Data Analysis Project in Excel (3-Step Framework)

0:00in this video we'll go over a full data

0:02analysis project and this will cover the

0:05three essential Parts which are data

0:07cleaning data analysis and data

0:10visualization this has taken me years to

0:13learn and you'd be surprised how many

0:15Advanced Excel users don't know some of

0:17these key things we're about to learn to

0:20make this as realistic as possible we'll

0:22be using a real data set on the richest

0:25people in the world which you can

0:26download for free in the description

0:28below the first step for us is data

0:31cleaning and as you can see over here we

0:33have the list of the people so this rich

0:35list alongside where they're from their

0:37City how they made their money their

0:40total worth and some information about

0:42when they're born and the country that

0:44they're in before we do any of the data

0:47cleaning it's very important that we

0:49save a copy so for this we can just go

0:51to the tab and while pressing the

0:53control key just drag to the side so now

0:55we have to let's call this one say the

0:58backup and we'll just sa that over to

1:00the side this way if we end up deleting

1:03any rows by mistake for example we can

1:05always go back to them so we'll be

1:07working on this data tab before we work

1:09on things like formatting we first need

1:11to see if we have any duplicates and for

1:14this we would just go over to the data

1:17Tab and then click on this button right

1:19here which is the remove duplicates

1:21button so we'll click on that and just

1:24click on okay so it's saying we had six

1:27duplicates that have been found and

1:28removed we'll click click on okay there

1:31so that's the first part done and then

1:33we can work on some of the text here for

1:35example for the gender maybe for data

1:37visualization later would be nice to

1:39have male instead of just an m and

1:42female instead of just an F so for this

1:45we'll just select this whole area by

1:47pressing control shift down arrow and

1:50then we'll go to contrl H this is the

1:53find and replace tool you can also find

1:55it by going to home and then clicking on

1:58find or replace over here so what do we

2:00want well first we want to replace the M

2:04with a maale we'll click on replace all

2:07there click on okay and we want to do

2:10the same thing with the F for

2:12female replace all again and click on

2:16okay and close out of that let's go all

2:18the way back up with control app arrow

2:20and then we have all of the birth

2:22information over here so we have their

2:24birth year birth month and birthday but

2:26there's actually nothing to do with

2:27their actual age so it would be ni to

2:30create a new column for their age let's

2:32go ahead and add it over here for the

2:34time being for this we'll first need to

2:36aggregate the birth date so all of this

2:39together we'll do that with the date

2:41function so equals date we want their

2:44year comma then the month comma and the

2:47day and hit enter great so that gives us

2:50their full birthday and now we can just

2:52take this all the way down to the bottom

2:55alongside this we'll need the current

2:57date so the date today and we can do the

2:59today day function for that so we'll

3:01just type today in there close the

3:03parenthesis and hit enter and again I'm

3:05just going to track that down with these

3:08two we can then find the difference but

3:10we don't just want the day difference

3:11right we want it in years and for this

3:13we need to use the year Frack formula

3:17and this is going to give us both so the

3:19start date is when they were born comma

3:21and the end date is the date today and

3:24comma and for this basis we want the

3:26actual over actual we'll put a one in

3:29there close the parenthesis and hit

3:31enter and now double click over here to

3:34drag this down so now we have their

3:36actual age let's change the titles over

3:39here so this one's the age this is their

3:41birth date and then this one over here

3:44is date current

3:47date and hit enter I'm just going to

3:50move this age column all the way to the

3:51middle here as it's a bit more relevant

3:53and I'm going to do that by pressing the

3:54shift key and then just dragging that to

3:57the side like so and we have the age

3:59over here here finally let's take a look

4:01at number formats so over here you might

4:04have noticed we have the country GDP but

4:07as soon as we actually look inside of

4:09the number you'll realize that this is

4:11in text it's not actually in number

4:13format and we can test this just by

4:15going to let's say equals selecting this

4:18one plus this one we should obviously

4:20get a value there but instead we just

4:22get a value error I think the reason for

4:25this is because we have these commas and

4:27also because we have this dollar sign of

4:29up front so we could get rid of those by

4:32selecting them so contrl shift down

4:34arrow and again crl H and we're looking

4:37for first a comma and we want to replace

4:40it with nothing we'll click on replace

4:43all there click on okay and same thing

4:46goes with the dollar sign so we have a

4:48dollar sign that we don't want so we'll

4:50leave empty and click on replace all

4:54okay and close out of that now if we

4:56stretch this out it's looking a lot

4:58better but we need to change the

4:59currency here let's just go over to more

5:02number formats and I'm just going to

5:05make it a regular number this one right

5:08here with separators and no need for

5:11decimals and click on okay now you can

5:14see we actually get an answer if we sum

5:16one with the other that's looking better

5:19awesome that's a data cleaning phase

5:21done and next up we have data analysis

5:24but first if you're finding this

5:26slightly too fast or slightly too

5:28challenging for you recommend you check

5:30out our Excel for business and finance

5:32course in this course will go over all

5:35of the essentials you need to know

5:37ranging from formatting best practices

5:39and shortcuts to building awesome visual

5:42dashboards creating large Dynamic

5:44Financial models and much more this is

5:47basically the course I wish I had before

5:50I started working in Excel heavy

5:52corporate jobs if all of that sounds

5:54interesting check out the link in the

5:56description below and if you want more

5:58than just Excel we also have a ton of

6:01other courses including powerbi Finance

6:04evaluation and much more in this next

6:08data analysis phase I always find it

6:10most useful to start with the basic

6:12statistics so finding out things like

6:14the average the count minim Max Etc and

6:17for this we can go over to the side and

6:19create a large list of formulas like

6:22equals average equals Max and so forth

6:25but actually there's a better way which

6:27is by going over to data and clicking on

6:30data analysis here you should get a

6:33popup like this one and we want to go

6:35for a descriptive statistics we'll click

6:38okay on that and now as our input range

6:41we want to select all of the values we

6:43want analyzed so it's going to be all

6:45the way from the final Worth right here

6:48to let's see shift right arrow to go to

6:50the side all the way to the age and

6:53control shift down all the way to the

6:55bottom hit enter there and we want the

6:58labels in first row tick and the output

7:01we can just have it in a new worksheet

7:04we want the summary statistics so we'll

7:06click on that and click on okay this

7:09generates a sheet two as you can see and

7:11within it we have all of the information

7:13so for example for the final worth

7:16column which is basically their net

7:17worth you can see that we have the mean

7:20which I think is 14.8 billion because

7:22the numbers are in millions here we also

7:25have a maximum of 211 billion and a

7:28minimum of of 5.3 billion there's also a

7:32total count of 475 people on this rich

7:35list and we get all of that information

7:38for each of the columns like for example

7:40here we can see the average age is 68

7:44that's a basic analysis done which

7:45should give you a better idea of the

7:47data you're working with and next up we

7:49would have the slightly more advanced

7:51analysis which we can do with pivot

7:53tables for these let's head over to the

7:56data tab again and this time we're going

7:58to go just select anywhere inside of it

8:01and insert pivot table we're happy to

8:05have that in a new worksheet and just

8:07click on okay so over here in the middle

8:09I have all of the pivot table fields and

8:12within it suppose we want to find out

8:14who are the 10 richest so the top 10 in

8:17this case it's going to be the person's

8:18name right so whoever is the richest and

8:21we also want that to be by their final

8:23worth and just stick on that so we

8:25should have one under values and the

8:27other under rows you see right now it's

8:30just an alphabetical order so to change

8:32this we can go over to this drop down

8:35and click on value filters top 10 so

8:39you'll see this area right here and we

8:41want the top 10 items by their sum of

8:44net worth right so who are the richest

8:46basically we'll click on okay and we get

8:49this full list but upon looking at it

8:51closely it's actually not in any

8:54particular order so let's switch this by

8:56right clicking and going to sort largest

9:00to smallest now we have this Bernard

9:03Arno family as number one followed by

9:06Elon Musk and Jeff basos another

9:09interesting analysis would be finding

9:11out the count of billionaires by age so

9:14maybe the older they are the more likely

9:16to become a billionaire so let's take a

9:18look by selecting this whole pivot table

9:21by pressing CR a there contrl C to copy

9:25and we'll just contrl V so paste it down

9:27below here as a duplicate to get rid of

9:30these two areas just go ahead and drag

9:32them out like so and now what we want is

9:35the billionaire count so we can just

9:38take their age which should be over here

9:40you can see on the bottom and just put

9:42it under rows and we'll also put the

9:45same age under values but not as a sum

9:48we'll click on this dropdown go to Value

9:51field settings and we want it as a count

9:54the sum of age doesn't make too much

9:56sense so you can see here that at this

9:58age we have one person at 33 we have

10:01another one but it makes more sense if

10:04these are grouped right maybe it's

10:06people 30 to 40 40 to 50 Etc we can also

10:10do this by right clicking and clicking

10:12on group within it we're going to go

10:15start at 30 years old let's say and end

10:18at 100 years old the increments let's

10:21say we're happy at 10 so every 10 years

10:24and click on okay now we can see grouped

10:27much better and it looks like those that

10:29are are 60 to 80 years old are the ones

10:31that are most likely to be billionaires

10:34that said there are eight that are in

10:36their 30 to 40 range and we can find out

10:39by just double clicking there and you'll

10:40see we get the full breakdown in a new

10:43sheet of exactly who these people are

10:45and at 34th we have this person called

10:48Mark M shits in fact when we look at

10:50this closer this person is associated

10:53with Red Bull these are just a few

10:56interesting examples but by using

10:58descriptive stat stics and pivot tables

11:01you should be able to analyze pretty

11:02much any data set finally we have the

11:05data visualization phase and over here

11:08we have the same pivot tables as before

11:10let's close this for now the thing is

11:13they're currently not Dynamic for

11:15example it would be nice to see what

11:16about the net worth of the top 10 in a

11:19specific industry well for this we can

11:22head over to pivot table analyze and

11:25click on insert slicer these slicers are

11:28basically f filters so we can filter by

11:31the category which is actually the same

11:33thing as the industry we can also filter

11:35by whether they're selfmade or not and

11:37let's suppose their gender as well we'll

11:40take on those three and just go to okay

11:43let me quickly rearrange

11:45these and now we can just click on for

11:48example those in the automotive industry

11:51and you'll see that we now get Elon Musk

11:53as number one probably to do with Tesla

11:55and all these other people same thing if

11:58we filter this to to true so only those

12:00that are self-made now the list gets

12:02updated and it seems like there's

12:04actually no female here if I change this

12:06to false then there seems to be a few

12:08females in this case this suzan Clen

12:11person if you want to filter out of

12:13these just press on this x sign and X

12:16again but you'll notice that this table

12:18down below isn't changing even though

12:20we're changing all of the values here

12:22and the reason is because it's not

12:24linked so if we want to link this filter

12:26to both tables to avoid confusion just

12:28go to to right click and go to report

12:31connections here we just want to make

12:33sure both pivot tables are ticked and

12:36click on okay same thing with these two

12:38other ones so let me fast forward

12:40that awesome let me filter out of this

12:43one and now we can create a few

12:45different charts so we have this data

12:47right here let's go to contr a to select

12:49it all and insert under recommended

12:52charts I can choose let's say the

12:53cluster column and click on okay let me

12:56rearrange this a bit and so we get the

13:00top 10 if you don't want these tags you

13:02can just click on field buttons there to

13:05undo them and it looks like Bernard AR

13:07no and elom musk are well above everyone

13:10else in this billionaires list let's do

13:12the same thing with this other table

13:14just going to go over to insert

13:16recommended charts and I'll select a

13:18cluster it again let me fast forward

13:21this here you can see that it follows a

13:24normal bell curve where from 50 to 90 is

13:27the large majority of billiona

13:30once we make all of the visuals we want

13:32the next step is to put them into a

13:34separate dashboard tab or we can make it

13:36all Dynamic I won't cover that in this

13:38video because I've already made similar

13:40ones in this video over here where you

13:42can make your own interactive dashboard

13:45or you can learn that even better in our

13:47Excel course over here hit the like and

13:49the Subscribe and I'll catch you in the

13:51next one