Data Analysis Essentials in Excel

0:00suppose you've received a large data set

0:02it's unformatted and messy and your

0:04manager is asking you some questions

0:06about it hey I have some doubts here do

0:09you have a second so to answer his

0:11questions we'll go over three essential

0:13data analysis skills which are data

0:16cleaning analysis and data visualization

0:19so let's get into it here's the Excel

0:22file which you can download for free in

0:24the video description we need to make a

0:26few changes to the data set like suppose

0:28we want to merge the first and the last

0:30name into one column we want to go ahead

0:33and change the formatting of the

0:34salespersons here so it's a bit more

0:36consistent and a bunch of other things

0:38now the fastest way to do that is using

0:41power query for that we're gonna head

0:44over to data and under the data tab

0:47you'll see this get and transform data

0:50area and just click on from table slash

0:53range

0:54it should select the whole data set for

0:56us and we'll just hit on OK there

0:59and from here it's going to load up the

1:01power query editor and this is really

1:03where we're gonna make all of the data

1:05cleaning changes it's a lot faster than

1:07doing it using just Excel so the first

1:10step when cleaning data is to remove any

1:12duplicates so under the order ID if

1:15there's more than one order ID with the

1:17same number then it's probably a

1:19duplicate so we can just right click on

1:22it and hit on remove duplicates

1:25whenever we make a change it's going to

1:27be recorded under applied steps here so

1:29you can always go back or you can even

1:31remove it if you wanted to

1:33then for the dates they're currently

1:35formatted as numbers you can see over

1:38here that it says whole number under

1:40date type so let's go ahead and click on

1:42it and change that to a regular date

1:46and you'll see that that changes

1:47automatically to the normal date format

1:50then we said we wanted to merge the

1:53first name and the last name so select

1:55both columns by hitting the control key

1:57we have both selected there and then we

2:00just need to go to right click

2:02and we'll go to merge columns

2:05from here we want a separator which is

2:07just gonna be the space sign for us

2:10and we want to go ahead and call this

2:12something like names and hit on OK

2:15and you'll see how we've been able to

2:17merge the first name and the last in

2:19just a few clicks if we keep scrolling

2:21through the data another thing that

2:23stands out here is that the units have

2:25decimals so if we're selling an item we

2:28should sell it in whole not a portion of

2:30it so let's go ahead and switch this up

2:32by going over to transform

2:35and from here you'll find the rounding

2:37on their number column and we want to go

2:40ahead and change that to just rounding

2:42it up and you can see there that we have

2:44the whole number one final thing that we

2:46want to change is all of these sales

2:48person column where they're not very

2:50well formatted so we can head over to

2:53format under the transform ribbon as

2:55well

2:56and first we want to go ahead and

2:58capitalize just each letter so the first

3:00letter of a word like so and we also

3:03want to trim these spaces so we can go

3:06back under format and just click on trim

3:09now that's looking a lot better now to

3:12go back to excel from here we just need

3:14to go over to the Home tab and then hit

3:17on close and load and this is going to

3:19apply all the changes that we've made so

3:22you can see here that it's created a new

3:23tab for us and this time it has

3:25everything cleaned up we have the

3:27queries and Connections in case we ever

3:29want to go back to it we can just right

3:31click and hit on edit but we don't right

3:34now so we'll just close out of that

3:36later in the video we'll look at

3:38creating some awesome visuals but before

3:40that let's get into the analysis section

3:42where our manager is asking us some

3:45questions about the data

3:47hey thanks for cleaning up the data if

3:49you don't mind can you summarize the

3:51revenue column

3:52so here he's probably trying to

3:54understand what the average revenue is

3:56or the minimum or maximum is and so on

3:59so it's basically this whole Revenue

4:00column here now we can do this with

4:03individual formulas like the sum the

4:05average and so on but there's a better

4:08way to do that that's by going to the

4:10data ribbon and then you'll find this

4:12data analysis option if you don't find

4:15that just go over it to right click and

4:17click on customize the ribbon

4:19then head under add-ins and you're going

4:23to want to select the analysis toolpack

4:25hit on go and select it from here

4:28obviously I already have it for now so

4:30I'm just gonna click inside of it

4:32once we get this pop-up we want to head

4:34to an area called descriptive statistics

4:37which is this one here and hit on OK

4:40this is going to summarize all of our

4:42Revenue data so for the input range we

4:45want to select all our Revenue numbers

4:47so just Ctrl shift down there and then

4:50for the output let's say we just want it

4:52on top to the to the right here so let's

4:55say I just put it on M2

4:57and then I'm just going to put summary

5:00statistics that's going to give us all

5:02the information we want and hit on OK

5:04there and you can see that it's created

5:06this whole set of Statistics from hey

5:09what's our mean revenue what's our

5:11minimum our maximum the total revenue

5:14the count and a ton of other useful data

5:18awesome thanks for putting this together

5:20can you also show me the breakdown of

5:22Revenue by salesperson

5:24we can do this by creating a pivot table

5:26so just go ahead and select inside of

5:29data go over to insert and just click on

5:33pivot tables hit on OK there and it's

5:36going to create a new tab where we can

5:38analyze it then under pivot table Fields

5:41let me just drag this along so we can

5:43see it better

5:44we're gonna want to add the revenue so

5:46that's going to be over under values

5:49and we're also going to add the sales

5:51person that's going to be under the rows

5:53and now we have that full breakdown but

5:55as you can see it's not really an order

5:57here so we can right click on it go to

6:00sort and sort largest to smallest and it

6:03seems like Anna is being the best

6:05performer for us

6:07thanks for that now for our next

6:09marketing campaign we're trying to see

6:11if we should Target more of a male or

6:13female audience do you have a breakdown

6:15for that

6:16for this we can still use pivot tables

6:19so let's go ahead and select this first

6:20one Ctrl C to copy and just paste it

6:24down below with Ctrl V and now we're

6:26gonna make all the changes so this time

6:29instead of the sales person on the rows

6:31we're gonna remove it and let's add the

6:34gender in here

6:35so we have the sum of Revenue maybe we

6:38also want to see the units so let's go

6:40ahead and drag that in as well based on

6:42this you can see that we should probably

6:44Target females more as they're the

6:46bigger part of our audience we can also

6:48get the proportion so the percentage

6:50here simply by right clicking on it and

6:53then show values as a percentage of

6:56grand total so with that you can see

6:59that we have 53 percent almost that are

7:02female relative to around 47 that are

7:05male this is great thanks a lot for your

7:07help

7:08awesome we can pretty much get any

7:11information on our data using pivot

7:13tables but there's another AI tool that

7:16Excel introduced so let's take a look at

7:18it we'll head over to the table here

7:20under table one and this time instead of

7:23going to Pivot tables we're gonna go

7:25under the home ribbon all the way to the

7:28right where it says analyze data so

7:31let's click on that it's basically going

7:33to create a q a box and within it we can

7:35ask anything so before we did a pivot

7:38table to find the revenue by salesperson

7:40you can also ask it here so Revenue

7:43by a salesperson

7:46hit enter there and you can see that we

7:48get the exact same breakdown so that's

7:50an awesome AI tool that you might find

7:52Handy and if you want to learn even more

7:55about data analysis you can check out

7:57our Excel for business finance course

7:59using the link in the description below

8:02we won't just go over data analysis

8:05though we'll also cover formatting best

8:07practices shortcuts building awesome

8:10visuals dashboards creating large

8:13Dynamic Financial models and much more

8:16and if you get stuck along the way you

8:19can always ask us the course instructors

8:21any questions in the discussions forum

8:24and if you want more than just Excel we

8:26also have several other courses on power

8:29bi Financial Accounting corporate

8:32finance and more if that sounds

8:34interesting check out the link in the

8:36description below alright back to the

8:39video now that we've cleaned up and

8:41analyzed the data let's look at how to

8:44visualize it and one of the easier ways

8:46to do that is using conditional

8:48formatting so you can see over here that

8:51we have the the breakdown that we did

8:52earlier with the revenue by salesperson

8:55and suppose we want to visualize these

8:58numbers over here a bit better we can go

9:00ahead and select them and then just go

9:03over to conditional formatting under the

9:05home ribbon

9:06and then we'll select the data bars

9:09let's say we go for a red one over here

9:11and you can see how easily we can tell

9:13the proportions now and just how much

9:15Anna brought in in Revenue another great

9:18visual for data analysis is a scatter

9:21plot so over here we have the cleaned

9:23data set and suppose we want to see if

9:26there is a relationship between age and

9:28revenue maybe older people tend to spend

9:31more money so let's go ahead and select

9:33both just by clicking on the edge here

9:36and hitting Ctrl shift down arrow then

9:39we'll go back up and for the revenue we

9:42want to keep hitting that control key so

9:45we also have this selected and again

9:47Ctrl shift down

9:49from here we'll go over under insert and

9:52right in the center here under charts

9:54we're going to want to select the

9:56Scudder so this one right over here to

9:59drag this back up just hit Ctrl X and

10:02then scroll up to the top and let's say

10:04I put it over here to the side with Ctrl

10:06V

10:07so it does seem like there's a bit of a

10:09trend where the older they are the more

10:11money they're spending we can look at

10:13that closer with a trend line just by

10:16right clicking and going to add

10:18trendline

10:19it does look upward sloping we can even

10:22go ahead and add the display equation so

10:25that we can see the numbers more

10:26precisely so here's the equation and

10:29basically what this is telling us is

10:31that for every increase of one year in

10:33age Revenue goes up by around 340

10:36dollars so overall it's fair to say that

10:39the older they are the more they're

10:41going to spend one final visual we look

10:43at is a box plot which is great for

10:46finding outliers in the data so over

10:49here let's suppose that we just take the

10:50revenue by hitting Ctrl shift down then

10:54we're gonna head over under insert again

10:57and this time under the very center here

10:59we're gonna click on that and go to the

11:01box and whisker I realize this is a

11:05funny looking chart so let's go over and

11:07explain what it's saying so the line

11:09right here which is quite small is the

11:11median the x is going to be the average

11:13for us and then these top and bottom

11:16parts are the extremes and these two

11:18here are the quartiles the first and the

11:21third quartile and if we had any

11:23outliers they would be showing us dots

11:26up over here or down over here just to

11:29show you quickly let's say I put another

11:30zero in here I'm just going to put a

11:32huge number you can see that it starts

11:34to show as an outlier there let me go

11:37back so you can see this again for more

11:39on data analysis check out this video

11:42over here going over five awesome data

11:45analysis tools or take our Excel chords

11:48over here and I'll catch you in the next

11:50one