Master Data Analysis on Excel in Just 10 Minutes

Aug 13, 2023

Master Data Analysis on Excel in Just 10 Minutes

AD: Sign up to enroll for a 7-day free trial with Coursera now! https://imp.i384100.net/c/4021125/134…
Learn all the fundamentals of Data Analysis on Excel in 10 minutes!

🆓 DOWNLOAD Free Excel file for this video: https://view.flodesk.com/pages/63f8ab…

#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 \u0026 Valuation Course: https://www.careerprinciples.com/cour…
👉 Excel for Business \u0026 Finance Course: https://www.careerprinciples.com/cour…
🚀 All our courses: https://www.careerprinciples.com/courses

SOCIALS:
🎬 My Company YouTube Channel: / @careerprinciples
📸 Instagram - https://www.instagram.com/careerprinc…
🤳 TikTok - https://www.tiktok.com/@career_princi…
🧑‍💻 LinkedIn - https://www.linkedin.com/company/care…

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

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.

Content

0 -> in this video you're going to learn all the fundamentals of data analysis and we'll break

5.28 -> this down into four main areas first we're going to be looking at transforming the data

10.68 -> so cleaning it up secondly we're going to be looking at creating descriptive statistics to

15.84 -> understanding better third we're going to be looking at data analysis and finally we'll get

21.54 -> into creating a report to visualize our findings so let's get into it and thank you to Coursera

27.84 -> for sponsoring this video more on them later the first step here is to transform the data and for

34.44 -> this here's the Excel file that we'll be working with which you can download for free in the video

38.58 -> description so as you can see this is going to be for a fast food chain you can see the types

43.26 -> of products here and the first step is simply to clean this up but for that we're going to

47.88 -> go to control T So to change this into a table so change the data set into a table then we'll

54.48 -> head on OK from here for the cleanup part you can see that under the manager column we seem to have

60.6 -> some very odd spacing here so let's go ahead and clean that up here we're going to go to equals

66.18 -> trim hit the top key and this formula is going to remove these old spaces I'm going to hit enter

72.24 -> there you can see that's going to Auto populate for all of these we'll change this name to manager

79.8 -> and then for all of these that are currently linked to this column over

83.58 -> here we're going to paste them as values so we're going to go Ctrl shift down

88.44 -> 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

96.54 -> means that we can go ahead and remove this column by hitting Ctrl minus there you go then under

102.36 -> quantity you might notice that we've got these decimals which don't make too much sense you can't

107.22 -> really have half a burger and so for this we're basically gonna have to round up to a whole number

112.62 -> for that we're going to use the Roundup formula so equals round up hit the top key there the number

119.46 -> is this one comma and the number of digits for us is going to be zero because we want the full

124.74 -> number close those brackets and hit enter so this is going to be our quantity now hit enter there

130.98 -> and we basically wanna Ctrl shift down Ctrl C and then paste as values so alt h v v v again

140.22 -> there you go so now we can remove this column here so we're going to select it and control minus

146.04 -> then you'll notice that under City here we have the city names but we don't necessarily have

150.6 -> the countries that would be a nice thing to add so for this we can actually head over to the data tab

156.72 -> and go under data types you'll find geography here so just click on that

162.72 -> once you start to see that icon it means that it's loaded up what you want to do here is

167.52 -> hit over to this icon to the side under add column and we basically want to add a country

173.22 -> or a region associated with that City and you can see that we have the corresponding country

177.84 -> for each City if you want to move this city column next to the country you can actually

183 -> go ahead and select it up top and we start to see these arrows when you hover over it just

188.46 -> press the shift key and then just drag it across like so that should allow you to move everything

193.08 -> one final thing we haven't done here is test if there's any duplicates so for this we can

198.66 -> simply go under data again and we're just going to remove duplicates by going to this icon over here

204.36 -> so we'll click on that and it's going to be for that whole table and we'll simply hit on OK

210.48 -> and you can see here that we get a notification saying that five duplicate values were found and

215.58 -> removed so we'll hit on OK there and speaking of data analysis if this is something that

221.28 -> you want to learn more about you can check out Google's data analytics professional certificate

227.52 -> it's designed to teach you job ready skills for any data analytics role so you can learn

232.98 -> all the skills required in less than six months the program currently has well over 1 million

239.76 -> students in a 4.8 star rating now if you're wondering if you need any prior experience no

246.42 -> there's no prior experience required and it's all fully self-paced in the program you learn

251.94 -> key analytical skills including data cleaning analysis and visualization using some of the

259.02 -> most common industry tools such as spreadsheets SQL or R programming upon completion you'll

266.82 -> receive a Google professional certification which you'll be able to share on your CV and Linkedin

273.36 -> so if you're interested sign up for a seven day free trial Now using the link in the description

279.06 -> below alright back to the video great now that we've done step one and step two it's

285.06 -> all about the descriptive statistics so really seeing things like what's the average price what

290.16 -> about the minimum the maximum and so on so for this let's hop over to the side and you could

296.1 -> try to find for the price and for the quantity say the average going one by one so type the average

301.38 -> formula then doing the same thing with the minimum the maximum so on but there is a much faster way

307.26 -> that's actually using a tool called Data analysis for that we're gonna have to activate it it should

313.8 -> be over here to the side under the data ribbon if you don't have it like me we can go ahead

318.3 -> and activate it so we'll head over to file go over to the bottom where it says options

326.28 -> from here we're gonna go into add-ins and we want to click on the analysis toolpack here and hit on

332.76 -> go from here we want to select analysis toolpack like I said and we'll hit OK now you can see that

339.72 -> we have this data analysis option under the data ribbon so that's the one that we want to select

346.8 -> from here we want the descriptive statistics and we'll hit on OK there

352.26 -> and the input range is going to be all of our prices so we'll go Ctrl shift down there

357.48 -> and then we want it to be a summary statistics and where do we want this let's say we want an output

363.78 -> range which is going to be up over here so right next to the table let's say we put it here to the

369 -> side there we're just going to hit on OK and now you can see that for the price which is what we've

375.18 -> selected we've gotten everything from the mean the median mode minimum maximum sum and a bunch

381.48 -> of other useful information let me repeat that same process for the quantity awesome now we've

388.02 -> got the breakdown by price and by quantity as well but one thing that this doesn't quite account for

393.18 -> is the fact that there could be outliers in our price so for that we can go ahead down over here

398.88 -> we're gonna basically create a new chart which is going to be a box and whisker so let's first

404.82 -> select all of the prices so Ctrl shift down and down over here you're gonna find this icon

411.72 -> click on that and we want a box and whisker basically this one over here and don't worry

417.24 -> if you can't interpret it we'll look at it just in a second let me move that all the way up

422.34 -> so once we have it in here this is basically telling us a few different things so first

426.96 -> these top and bottom lines are telling us the maximums so the Max and the Min if you

431.58 -> will and then in here this box represents the first and the third quartile and then you can

437.34 -> see that we've got that X which is the average then we've got a line which is going to be the

442.08 -> median and finally we've got these dots over here which represent all of our outliers in our price

448.62 -> now if we want to dig a bit further into this it would be nice to see the x-axis maybe we can

454.2 -> put something like the manager's name to see where exactly this is coming from maybe it's one manager

459.24 -> that might be reporting the data wrong so we'll go to right click for that go under select data

466.44 -> edit the horizontal category access label here and what we're going to do is put the

471.6 -> managers so we're going to select all of these Ctrl shift down and hit on OK

477.66 -> and hit on OK again now if we go all the way back up you can see what that breakdown looks like

483.42 -> by manager and it seems to be that all of these outliers are accumulated with Joel maybe we should

488.94 -> have a word with him now moving on to step 3 which is data analysis so over here you can see that

495.72 -> we've got three different questions that we want to solve which is what is our best selling product

500.16 -> what is our total revenue and that's what's our Revenue breakdown by payment method so if we go

506.52 -> back to the table here you notice that we don't actually have a column for Revenue so that's one

511.26 -> that we're going to have to calculate simply by multiplying the price and the quantity so

515.82 -> we'll do that over here let me add an extra column there and let's call this something like Revenue

524.34 -> and the formula for it is going to be equals the quantity multiplied by the price and we'll just

530.46 -> hit enter there that should be calculated for us and then we'll go up over here again and for all

536.46 -> of this analysis we can actually do most of it with a pivot table so we'll head over to insert

542.52 -> pivot table the table or range we're interested in is all of this range so once we select one of the

549.72 -> cells we can just go to control a that's going to select all of them and we want this in a location

555.96 -> in an existing worksheet which we already selected so we'll hit OK great from here we can start doing

562.38 -> all the breakdown so what's our best selling product so let's do this by quantity we're going

567.78 -> to select the products as the rows just hover that over and then we're gonna check the quantity over

574.14 -> here as the values great now we have all of that breakdown if we want to sort it from highest to

580.38 -> lowest we can go to right click and go to sort and we'll sort largest to smallest and now you

587.04 -> can see that beverages is our largest at 35 000. then secondly we've got what is our total revenue

594.66 -> for this one we're simply going to have to select the revenue so first we'll remove these let's take

600.18 -> this out and we'll take this out as well and we just have to select the revenue and put it

605.16 -> under values that should give us the sum of total revenue which seems to be 812 000. and finally our

613.26 -> Revenue breakdown by payment method so for that we're gonna get all of the payment methods and

619.38 -> put those under the rosie like so but to see the breakdown better it probably makes more sense as

625.56 -> a percentage so what we can actually do is go to right click and from here go to summarize show

632.16 -> values ask sorry and we're going to show them as a percentage of the grand total now you can see that

638.1 -> full breakdown and it seems like credit cards are the most dominant here now that you've seen how to

644.52 -> do some of the data analysis let me show you what a final report might look like this one here is

650.22 -> fairly simplified so you can see that we've got this drop down which is using data validation

654.84 -> to select the manager and everything dynamically changes so if I select dwell I'm able to see which

661.44 -> country and city is from using the X lookup and then just below that using the sum ifs I'm able

668.1 -> to derive his particular revenue for fries and the other products as well as his total revenue

675 -> and these data bars over here are done using the conditional formatting if you're unsure

680.64 -> how to do this type of formulas you can check out this video over here or this link over here

686.16 -> to take our Excel codes hit the like and that subscribe and I'll catch you in the next one

Source: https://www.youtube.com/watch?v=_g5roKHj95o