Home Business Analyst BA Agile Coach Master Data Analysis on Excel in Just 10 Minutes

Master Data Analysis on Excel in Just 10 Minutes

81
21

In this video you’re going to learn all the  fundamentals of data analysis and we’ll break   this down into four main areas first we’re  going to be looking at transforming the data   so cleaning it up secondly we’re going to be  looking at creating descriptive statistics to  

Understanding better third we’re going to be  looking at data analysis and finally we’ll get   into creating a report to visualize our findings  so let’s get into it and thank you to Coursera   for sponsoring this video more on them later the  first step here is to transform the data and for  

This here’s the Excel file that we’ll be working  with which you can download for free in the video   description so as you can see this is going to  be for a fast food chain you can see the types  

Of products here and the first step is simply  to clean this up but for that we’re going to   go to control T So to change this into a table  so change the data set into a table then we’ll  

Head on OK from here for the cleanup part you can  see that under the manager column we seem to have   some very odd spacing here so let’s go ahead and  clean that up here we’re going to go to equals  

Trim hit the top key and this formula is going  to remove these old spaces I’m going to hit enter   there you can see that’s going to Auto populate  for all of these we’ll change this name to manager and then for all of these that are  currently linked to this column over  

Here we’re going to paste them as values  so we’re going to go Ctrl shift down   Ctrl C and then we’re gonna paste this as a value  so alt h v v is a shortcut for that and now that  

Means that we can go ahead and remove this column  by hitting Ctrl minus there you go then under   quantity you might notice that we’ve got these  decimals which don’t make too much sense you can’t  

Really have half a burger and so for this we’re  basically gonna have to round up to a whole number   for that we’re going to use the Roundup formula so  equals round up hit the top key there the number  

Is this one comma and the number of digits for  us is going to be zero because we want the full   number close those brackets and hit enter so this  is going to be our quantity now hit enter there  

And we basically wanna Ctrl shift down Ctrl C  and then paste as values so alt h v v v again   there you go so now we can remove this column  here so we’re going to select it and control minus  

Then you’ll notice that under City here we have  the city names but we don’t necessarily have   the countries that would be a nice thing to add so  for this we can actually head over to the data tab   and go under data types you’ll find  geography here so just click on that  

Once you start to see that icon it means that  it’s loaded up what you want to do here is   hit over to this icon to the side under add  column and we basically want to add a country  

Or a region associated with that City and you  can see that we have the corresponding country   for each City if you want to move this city  column next to the country you can actually   go ahead and select it up top and we start to  see these arrows when you hover over it just  

Press the shift key and then just drag it across  like so that should allow you to move everything   one final thing we haven’t done here is test  if there’s any duplicates so for this we can  

Simply go under data again and we’re just going to  remove duplicates by going to this icon over here   so we’ll click on that and it’s going to be  for that whole table and we’ll simply hit on OK  

And you can see here that we get a notification  saying that five duplicate values were found and   removed so we’ll hit on OK there and speaking  of data analysis if this is something that   you want to learn more about you can check out  Google’s data Analytics professional certificate  

It’s designed to teach you job ready skills  for any data analytics role so you can learn   all the skills required in less than six months  the program currently has well over 1 million   students in a 4.8 star rating now if you’re  wondering if you need any prior experience no  

There’s no prior experience required and it’s  all fully self-paced in the program you learn   key analytical skills including data cleaning  analysis and visualization using some of the   most common industry tools such as spreadsheets  SQL or R Programming upon completion you’ll  

Receive a Google professional certification which  you’ll be able to share on your CV and Linkedin   so if you’re interested sign up for a seven day  free trial Now using the link in the description   below alright back to the video great now  that we’ve done step one and step two it’s  

All about the descriptive statistics so really  seeing things like what’s the average price what   about the minimum the maximum and so on so for  this let’s hop over to the side and you could  

Try to find for the price and for the quantity say  the average going one by one so type the average   formula then doing the same thing with the minimum  the maximum so on but there is a much faster way  

That’s actually using a tool called Data analysis  for that we’re gonna have to activate it it should   be over here to the side under the data ribbon  if you don’t have it like me we can go ahead  

And activate it so we’ll head over to file  go over to the bottom where it says options   from here we’re gonna go into add-ins and we want  to click on the analysis toolpack here and hit on  

Go from here we want to select analysis toolpack  like I said and we’ll hit OK now you can see that   we have this data analysis option under the data  ribbon so that’s the one that we want to select from here we want the descriptive  statistics and we’ll hit on OK there  

And the input range is going to be all of  our prices so we’ll go Ctrl shift down there   and then we want it to be a summary statistics and  where do we want this let’s say we want an output  

Range which is going to be up over here so right  next to the table let’s say we put it here to the   side there we’re just going to hit on OK and now  you can see that for the price which is what we’ve  

Selected we’ve gotten everything from the mean  the median mode minimum maximum sum and a bunch   of other useful information let me repeat that  same process for the quantity awesome now we’ve   got the breakdown by price and by quantity as well  but one thing that this doesn’t quite account for  

Is the fact that there could be outliers in our  price so for that we can go ahead down over here   we’re gonna basically create a new chart which  is going to be a box and whisker so let’s first  

Select all of the prices so Ctrl shift down  and down over here you’re gonna find this icon   click on that and we want a box and whisker  basically this one over here and don’t worry  

If you can’t interpret it we’ll look at it just  in a second let me move that all the way up   so once we have it in here this is basically  telling us a few different things so first  

These top and bottom lines are telling us  the maximums so the Max and the Min if you   will and then in here this box represents the  first and the third quartile and then you can  

See that we’ve got that X which is the average  then we’ve got a line which is going to be the   median and finally we’ve got these dots over here  which represent all of our outliers in our price  

Now if we want to dig a bit further into this  it would be nice to see the x-axis maybe we can   put something like the manager’s name to see where  exactly this is coming from maybe it’s one manager  

That might be reporting the data wrong so we’ll  go to right click for that go under select data   edit the horizontal category access label  here and what we’re going to do is put the   managers so we’re going to select all  of these Ctrl shift down and hit on OK  

And hit on OK again now if we go all the way back  up you can see what that breakdown looks like   by manager and it seems to be that all of these  outliers are accumulated with Joel maybe we should  

Have a word with him now moving on to step 3 which  is data analysis so over here you can see that   we’ve got three different questions that we want  to solve which is what is our best selling product  

What is our total revenue and that’s what’s our  Revenue breakdown by payment method so if we go   back to the table here you notice that we don’t  actually have a column for Revenue so that’s one   that we’re going to have to calculate simply  by multiplying the price and the quantity so  

We’ll do that over here let me add an extra column  there and let’s call this something like Revenue and the formula for it is going to be equals the  quantity multiplied by the price and we’ll just  

Hit enter there that should be calculated for us  and then we’ll go up over here again and for all   of this analysis we can actually do most of it  with a pivot table so we’ll head over to insert  

Pivot table the table or range we’re interested in  is all of this range so once we select one of the   cells we can just go to control a that’s going to  select all of them and we want this in a location  

In an existing worksheet which we already selected  so we’ll hit OK great from here we can start doing   all the breakdown so what’s our best selling  product so let’s do this by quantity we’re going   to select the products as the rows just hover that  over and then we’re gonna check the quantity over  

Here as the values great now we have all of that  breakdown if we want to sort it from highest to   lowest we can go to right click and go to sort  and we’ll sort largest to smallest and now you  

Can see that beverages is our largest at 35 000.  then secondly we’ve got what is our total revenue   for this one we’re simply going to have to select  the revenue so first we’ll remove these let’s take  

This out and we’ll take this out as well and  we just have to select the revenue and put it   under values that should give us the sum of total  revenue which seems to be 812 000. and finally our  

Revenue breakdown by payment method so for that  we’re gonna get all of the payment methods and   put those under the rosie like so but to see the  breakdown better it probably makes more sense as  

A percentage so what we can actually do is go to  right click and from here go to summarize show   values ask sorry and we’re going to show them as a  percentage of the grand total now you can see that  

Full breakdown and it seems like credit cards are  the most dominant here now that you’ve seen how to   do some of the data analysis let me show you what  a final report might look like this one here is  

Fairly simplified so you can see that we’ve got  this drop down which is using data validation   to select the manager and everything dynamically  changes so if I select dwell I’m able to see which   country and city is from using the X lookup and  then just below that using the sum ifs I’m able  

To derive his particular revenue for fries and  the other products as well as his total revenue   and these data bars over here are done using  the conditional formatting if you’re unsure   how to do this type of formulas you can check  out this video over here or this link over here  

To take our Excel codes hit the like and that  subscribe and I’ll catch you in the next one
AD: Sign up to enroll for a 7-day free trial with Coursera now!
Learn all the fundamentals of Data Analysis on Excel in 10 minutes!

DOWNLOAD Free Excel file for this video:

#coursera #courserapartner @coursera

This video will teach you all the fundamentals of data analysis in just 10 minutes. First, we’ll go over how to transform or clean up a raw set of data on Excel. This will include using features like the trim formula, data types, removing duplicates, and more. Then, we’ll look at descriptive statistics using the data analysis feature on Excel. This will give us important numbers like the average, min, max, quartiles etc. We’ll also create a box and whisker chart for analysing outliers. Third, we’ll analyze the data using pivot tables. Finally, we’ll create a small report using conditional formatting, xlookups, sumifs and more.

LEARN:
The Complete Finance & Valuation Course:
Excel for Business & Finance Course:
All our courses:

SOCIALS:
My Company YouTube Channel:
Instagram –
TikTok –
LinkedIn –

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ Intro
0:30 – Transforming Data
4:41​ – Descriptive Statistics
8:10​ – Data Analysis
10:43​ – Dashboard for showing your findings

Disclaimer: I may receive a small commission on some of the links provided at no extra cost to you.
00:00 in this video you’re going to learn all the  fundamentals of data analysis and we’ll break  
00:05 this down into four main areas first we’re  going to be looking at transforming the data  
00:10 so cleaning it up secondly we’re going to be  looking at creating descriptive statistics to  
00:15 understanding better third we’re going to be  looking at data analysis and finally we’ll get  
00:21 into creating a report to visualize our findings  so let’s get into it and thank you to Coursera  
00:27 for sponsoring this video more on them later the  first step here is to transform the data and for  
00:34 this here’s the Excel file that we’ll be working  with which you can download for free in the video  
00:38 description so as you can see this is going to  be for a fast food chain you can see the types  
00:43 of products here and the first step is simply  to clean this up but for that we’re going to  
00:47 go to control T So to change this into a table  so change the data set into a table then we’ll  
00:54 head on OK from here for the cleanup part you can  see that under the manager column we seem to have  
01:00 some very odd spacing here so let’s go ahead and  clean that up here we’re going to go to equals  
01:06 trim hit the top key and this formula is going  to remove these old spaces I’m going to hit enter  
01:12 there you can see that’s going to Auto populate  for all of these we’ll change this name to manager
01:19 and then for all of these that are  currently linked to this column over  
01:23 here we’re going to paste them as values  so we’re going to go Ctrl shift down  
01:28 Ctrl C and then we’re gonna paste this as a value  so alt h v v is a shortcut for that and now that  
01:36 means that we can go ahead and remove this column  by hitting Ctrl minus there you go then under  
01:42 quantity you might notice that we’ve got these  decimals which don’t make too much sense you can’t  
01:47 really have half a burger and so for this we’re  basically gonna have to round up to a whole number  
01:52 for that we’re going to use the Roundup formula so  equals round up hit the top key there the number  
01:59 is this one comma and the number of digits for  us is going to be zero because we want the full  
02:04 number close those brackets and hit enter so this  is going to be our quantity now hit enter there  
02:10 and we basically wanna Ctrl shift down Ctrl C  and then paste as values so alt h v v v again  
02:20 there you go so now we can remove this column  here so we’re going to select it and control minus  
02:26 then you’ll notice that under City here we have  the city names but we don’t necessarily have  
02:30 the countries that would be a nice thing to add so  for this we can actually head over to the data tab  
02:36 and go under data types you’ll find  geography here so just click on that  
02:42 once you start to see that icon it means that  it’s loaded up what you want to do here is  
02:47 hit over to this icon to the side under add  column and we basically want to add a country  
02:53 or a region associated with that City and you  can see that we have the corresponding country  
02:57 for each City if you want to move this city  column next to the country you can actually  
03:03 go ahead and select it up top and we start to  see these arrows when you hover over it just  
03:08 press the shift key and then just drag it across  like so that should allow you to move everything  
03:13 one final thing we haven’t done here is test  if there’s any duplicates so for this we can  
03:18 simply go under data again and we’re just going to  remove duplicates by going to this icon over here  
03:24 so we’ll click on that and it’s going to be  for that whole table and we’ll simply hit on OK  
03:30 and you can see here that we get a notification  saying that five duplicate values were found and  
03:35 removed so we’ll hit on OK there and speaking  of data analysis if this is something that  
03:41 you want to learn more about you can check out  Google’s data analytics professional certificate  
03:47 it’s designed to teach you job ready skills  for any data analytics role so you can learn  
03:52 all the skills required in less than six months  the program currently has well over 1 million  
03:59 students in a 4.8 star rating now if you’re  wondering if you need any prior experience no  
04:06 there’s no prior experience required and it’s  all fully self-paced in the program you learn  
04:11 key analytical skills including data cleaning  analysis and visualization using some of the  
04:19 most common industry tools such as spreadsheets  SQL or R programming upon completion you’ll  
04:26 receive a Google professional certification which  you’ll be able to share on your CV and Linkedin  
04:33 so if you’re interested sign up for a seven day  free trial Now using the link in the description  
04:39 below alright back to the video great now  that we’ve done step one and step two it’s  
04:45 all about the descriptive statistics so really  seeing things like what’s the average price what  
04:50 about the minimum the maximum and so on so for  this let’s hop over to the side and you could  
04:56 try to find for the price and for the quantity say  the average going one by one so type the average  
05:01 formula then doing the same thing with the minimum  the maximum so on but there is a much faster way  
05:07 that’s actually using a tool called Data analysis  for that we’re gonna have to activate it it should  
05:13 be over here to the side under the data ribbon  if you don’t have it like me we can go ahead  
05:18 and activate it so we’ll head over to file  go over to the bottom where it says options  
05:26 from here we’re gonna go into add-ins and we want  to click on the analysis toolpack here and hit on  
05:32 go from here we want to select analysis toolpack  like I said and we’ll hit OK now you can see that  
05:39 we have this data analysis option under the data  ribbon so that’s the one that we want to select
05:46 from here we want the descriptive  statistics and we’ll hit on OK there  
05:52 and the input range is going to be all of  our prices so we’ll go Ctrl shift down there  
05:57 and then we want it to be a summary statistics and  where do we want this let’s say we want an output  
06:03 range which is going to be up over here so right  next to the table let’s say we put it here to the  
06:09 side there we’re just going to hit on OK and now  you can see that for the price which is what we’ve  
06:15 selected we’ve gotten everything from the mean  the median mode minimum maximum sum and a bunch  
06:21 of other useful information let me repeat that  same process for the quantity awesome now we’ve  
06:28 got the breakdown by price and by quantity as well  but one thing that this doesn’t quite account for  
06:33 is the fact that there could be outliers in our  price so for that we can go ahead down over here  
06:38 we’re gonna basically create a new chart which  is going to be a box and whisker so let’s first  
06:44 select all of the prices so Ctrl shift down  and down over here you’re gonna find this icon  
06:51 click on that and we want a box and whisker  basically this one over here and don’t worry  
06:57 if you can’t interpret it we’ll look at it just  in a second let me move that all the way up  
07:02 so once we have it in here this is basically  telling us a few different things so first  
07:06 these top and bottom lines are telling us  the maximums so the Max and the Min if you  
07:11 will and then in here this box represents the  first and the third quartile and then you can  
07:17 see that we’ve got that X which is the average  then we’ve got a line which is going to be the  
07:22 median and finally we’ve got these dots over here  which represent all of our outliers in our price  
07:28 now if we want to dig a bit further into this  it would be nice to see the x-axis maybe we can  
07:34 put something like the manager’s name to see where  exactly this is coming from maybe it’s one manager  
07:39 that might be reporting the data wrong so we’ll  go to right click for that go under select data  
07:46 edit the horizontal category access label  here and what we’re going to do is put the  
07:51 managers so we’re going to select all  of these Ctrl shift down and hit on OK  
07:57 and hit on OK again now if we go all the way back  up you can see what that breakdown looks like  
08:03 by manager and it seems to be that all of these  outliers are accumulated with Joel maybe we should  
08:08 have a word with him now moving on to step 3 which  is data analysis so over here you can see that  
08:15 we’ve got three different questions that we want  to solve which is what is our best selling product  
08:20 what is our total revenue and that’s what’s our  Revenue breakdown by payment method so if we go  
08:26 back to the table here you notice that we don’t  actually have a column for Revenue so that’s one  
08:31 that we’re going to have to calculate simply  by multiplying the price and the quantity so  
08:35 we’ll do that over here let me add an extra column  there and let’s call this something like Revenue
08:44 and the formula for it is going to be equals the  quantity multiplied by the price and we’ll just  
08:50 hit enter there that should be calculated for us  and then we’ll go up over here again and for all  
08:56 of this analysis we can actually do most of it  with a pivot table so we’ll head over to insert  
09:02 pivot table the table or range we’re interested in  is all of this range so once we select one of the  
09:09 cells we can just go to control a that’s going to  select all of them and we want this in a location  
09:15 in an existing worksheet which we already selected  so we’ll hit OK great from here we can start doing  
09:22 all the breakdown so what’s our best selling  product so let’s do this by quantity we’re going  
09:27 to select the products as the rows just hover that  over and then we’re gonna check the quantity over  
09:34 here as the values great now we have all of that  breakdown if we want to sort it from highest to  
09:40 lowest we can go to right click and go to sort  and we’ll sort largest to smallest and now you  
09:47 can see that beverages is our largest at 35 000.  then secondly we’ve got what is our total revenue  
09:54 for this one we’re simply going to have to select  the revenue so first we’ll remove these let’s take  
10:00 this out and we’ll take this out as well and  we just have to select the revenue and put it  
10:05 under values that should give us the sum of total  revenue which seems to be 812 000. and finally our  
10:13 Revenue breakdown by payment method so for that  we’re gonna get all of the payment methods and  
10:19 put those under the rosie like so but to see the  breakdown better it probably makes more sense as  
10:25 a percentage so what we can actually do is go to  right click and from here go to summarize show  
10:32 values ask sorry and we’re going to show them as a  percentage of the grand total now you can see that  
10:38 full breakdown and it seems like credit cards are  the most dominant here now that you’ve seen how to  
10:44 do some of the data analysis let me show you what  a final report might look like this one here is  
10:50 fairly simplified so you can see that we’ve got  this drop down which is using data validation  
10:54 to select the manager and everything dynamically  changes so if I select dwell I’m able to see which  
11:01 country and city is from using the X lookup and  then just below that using the sum ifs I’m able  
11:08 to derive his particular revenue for fries and  the other products as well as his total revenue  
11:15 and these data bars over here are done using  the conditional formatting if you’re unsure  
11:20 how to do this type of formulas you can check  out this video over here or this link over here  
11:26 to take our Excel codes hit the like and that  subscribe and I’ll catch you in the next one

DataAnalysisTechniques

21 COMMENTS

  1. What do you mean "paste as values"? you did this after you made the new manager and quantity columns. Is there a reason for this ? The value after trimming and rounding isn't good enough?

  2. i wish i took A course and already have learned all this when i was 16 smh. Soo much time wasted i regret.
    But anyway here we are now!
    For me atleast, this is very irrelevant to anatomy and physiology which i will be taking in medicine. But If Iwill take anything from the video it will be the data analytics add-in in 5:23.
    ))))))))))))))))))Thank you soo much good sir, may God bless you. I am looking forward to taking that google course.

LEAVE A REPLY

Please enter your comment!
Please enter your name here