Data Analysis in Excel & VBA

• Welcome to The Course.

• Download Corresponding Files from Resources.

• Excel Interface Design Intro.

• Review Data Files for Common Mistakes.

• Use Count Function to Count Blank Cells in Data Sets.

• Set Data Ready for Analysis.

In this compact course, we will be learning all the essentials for data analysis in Excel.

This course is available for all levels starting with beginners and until advanced.

If you are new to data analysis, I would like to welcome you on board.

This is Mo and I will be your friend first, instructor second.

I hope you will have the best time here and don't worry about course length.

We will be taking multiple breaks along this course, so please don't get impatient.

Just try to open up the corresponding files and apply the steps to your data and everything will be

alright.

If you get bored, try to set playback speed at 1.25, hopefully not more.

Or you can just bookmark the lecture you are currently on and come back to it later.

Remember this is no competition, so learn to consume content from this course.

Absorb the information you will find here slowly.

The fastest way to do that is by applying steps you see as you go along.

But don't worry about the results for a few days here and you'll feel at home.

With that said, let's go ahead and start our course.

So what to expect from this tutorial?

First, we will convert lists into a table and take a look at the table tools that are available.

We will use aggregate functions.

Conditionally apply calculations to our data.

We will use pivot tables to summarize our data set.

And then we will take it a step further.

We will create pivot charts.

Later we will be introduced to our precious friend VBA.

So stay tuned.

For now, please go ahead and open the practice files.

You will find them in the resources from this particular video.

Download that.

Then we will get started quickly.

We're going to dive right into our Creating Tables worksheet tab.

We have some basic employee data.

So notice that we have well well-defined headers.

We have ID, last name, first name, department, email, phone extension, location, hire date and

pay rate.

So that's a good first step.

We want to make sure that we have well-defined headers right.

So remember our headers.

They are the key when we are analyzing our data.

So we want to make sure that they are all named well.

And they describe the data below in an accurate way.

The next thing we need to make sure that we have unique labels.

This is not always the case, but it's best to have unique labels when you are working with data.

So in this case, we can see that we have unique ID numbers, and this will give us some better opportunities

to kind of filter our data down to what we are looking for as well.

The other thing we want to make sure that we have of course, complete records.

So as long as our list has well-defined defined headers, labels and complete records, meaning no blanks,

then we are good to go.

We can see in that data set we don't have any blank cells in here.

We have complete records on row five.

We have a complete record for Joe Gonzalez.

All of the information is filled out.

If we do have blank cells in our data set, we will run into some problems.

Now, one of the problems that you will run into is that our data analysis will be incorrect or incomplete,

because we are actually missing data.

So it's usually a good idea to just kind of scroll through your data set, make sure that you don't

have any blank cells.

You can always use the count blank function.

This will help you to count the amount of blanks that you have in your data.

So I'll just enter the count blank function here.

I'm going to go ahead and highlight all of my data.

So Excel is going to count all the blank cells.

And it finds them in range from A3 Three down to I-40 five.

Press enter.

I have zero blank cells.

Now, if I were to delete a cell, it tells me right away that I have one cell.

So those are some good first steps, some other tricky things to kind of look for, especially when

you are working with dates and numerical values.

You want to make sure that they are formatted properly.

So we can see if we take a look at our higher dates, we can see that they are properly formatted as

a date.

This is not always the case.

Sometimes you have data that's extracted from one program to the next.

You may notice sometimes, even though these look like dates, they say something else up here.

It may say custom or general.

So you want to make sure that you made those adjustments right there.

We're all good here for the pay rates as well.

We can see that's using the currency format.

So we are pretty much ready to go ahead and analyze this data in a table.

• Convert a Data Set into a Table in Excel.

• Methods of Converting a Data into A Table.

• Choosing a Table Style.

• Introduction to Sorting Data in Excel.

• Sorting by Last Name and/or Department.

• Complex Sorting (More than only one sorting at a time).

Inserting tables for analysis.

Let's convert this list into a table, and then we will have some tools that we can use to analyze the

data.

Now there are two ways that we can convert this list into a table.

Here's the first way we can actually use a shortcut.

So if we press control plus T that will convert this into a table for us as long as we have selected

inside of the list.

The other way is from our ribbon.

So from the home ribbon, if we head on over to the styles command group, we have format as table command.

So those are two ways that we can convert this.

So I'll go ahead and click anywhere inside of my list here.

And I will use my first method.

And that's control plus T.

If I press control T it just basically asks me if I want to create a table.

Where is the data for a table, and does your table have headers?

My table does have headers, so I'm good to go.

If I scroll down it's highlighting all of my data.

We'll go ahead and press okay.

And it converts it into a table for me.

So here is the table form.

We know that this is a table because if we look at the ribbon now we have the table design tab.

And this is where we will find all the tools that we need to be able to analyze the data.

We can filter our data.

We can turn on the total row.

We can insert a slicer.

We also can go ahead and take a look at the different table styles that are available as well.

I'm going to go ahead and undo that.

I'll press control Z and let's try method number two.

I'll go ahead and click on format as table from the ribbon here.

And the only difference is that we can choose our table style ahead of time.

So there you go.

Here's my list that's now converted into a table.

We'll now that we converted this into a table we can do things like sort by columns.

We can also do some filtering as well.

We also have another tool that's called the total row.

So we'll take a look at these three tools here as well.

Notice for all of my headers.

Now I have a filter.

So if I click on the last name filter here.

Several things I can do at the top of that filter I can go ahead and sort.

Then down here I can use a logical filter.

I can filter by a search form.

Or I can go ahead and select a unique value from down here.

So those are four opportunities for us to use the filter drop down in order to analyze our data.

I'll sort by last name here, and I'll sort from A to Z, and notice how all of my records now are being

sorted by last name.

The entire record moves.

We're not just sorting this column explicitly, the entire record is moving as well.

So if I were to go ahead and maybe sort by ID number, we can see that the entire record travels here

as well.

So we can do some simple sorting here as well.

But what if I want to do some more complex sorting.

We would need to click on the data tab.

Click on the sort icon here in order to do some complex sorting.

Because at this point we can only sort on one column at a time.

You saw that when I sorted by ID number, it removed the sword from the last name here.

Well, what I would like to do is maybe go ahead and sort by last name and department.

So how we can sort by last name and department.

Well, we need to click on this sort icon here.

This will allow me to select the different columns in which I want to sort.

By now I can select the column I want to sort by, I want to sort by last name.

Or maybe I will do department.

First department is a higher level.

So let's sort by department and then by last name.

Well for me to do that I need to click on add level then add another level.

Here I can go ahead and sort by last name.

I'll click on Add Level and last name.

I'll make sure that they both say from A to Z and I'm good to go.

Once I press okay, we can see it has been sorted by department and it's also sorted by last name.

So that was some basic sorting that we can perform on our table.

Now let's take a look at the different filters we can use inside Excel.

• Filtering Data in Tables in Excel.

• Filtering by Pay Rate & Hire Date.

• Filtering using a Logical Operator.

• Clearing Filters.

• Filtering between a Certain Timeline.

In this video, We will learn how to Filter Data in tables.

I'll go ahead and click on a filter for the hire date.

Let's see what filters are available for the hire date.

We'll also take a look

and see what filters are available for the pay rate, so we will work with dates

and numbers.

Now for the date filters we have a lot of date filters.

I can look for a hire date based on an exact date before a certain time, after a certain time, and

between a certain time.

Then I can be more specific, right?

Last year, last month, last quarter.

So those are different things that I can do.

What if I want to know how many hires we had between two dates.

So if I click on between here, here is my little logical operator.

I want to find out.

How many hires did we have?

Let's say after 2000.

So I'll say January 1st, 2000.

But before December 31st, 2006.

I could also click on Date Picker here and choose my date from here as well.

Let's see, how many hires did we have between 2000 and 2006?

After I press okay.

Here are my five records.

If you take a look at your status bar,

it tells you that five of 37 records were found.

We can see that these all fit the description.

They all fall between those two particular dates.

So that's one way we can filter using logical operator.

I'm going to go ahead and clear the filter from the hire date.

So we can see the filters active here.

I'll go ahead and click on it and I will click on the clear filter.

This will reset my table & display my 37 records.

Well let's go back to the hire date.

And this time let's go and enter a value in the search box.

What if I just want to find out how many hires did I have in 2010.

So if I type 2010 here, notice how it collapses for me for that period.

So it looks like I had a couple of hires in here.

Here are my three hires for that year

so as we can see,  filters are Really nice tools.

Excellent tools for doing simple data analysis.

I'll go ahead and clear this filter as well.

And then we will run one more.

So this is the other level day or year down here as well.

So what I'll do is I will unselect all and I just want to find out how many hires we had in 06 and

06 seven.

So if I press okay.

Here are my four records.

So either in 06 or 07.

I'll go ahead and clear the filter from here.

Let's take a look at the pay rate.

So the pay rate offers us the same opportunities.

Not as many filters as the date filter.

We have some basic ones here.

So if I wanted to take a look at the pay rates that are greater than or equal to a certain amount,

let's show all the numbers that are greater or equal to 30 dollars.

Here's my simple dialog.

I'll type 30.

And here I will go ahead and press okay.

And there we go.

So here are my 13 records.

So 13 employees are earning 30 dollars or more per hour.

So those are some basic filters.

The good thing about filters as well we can run multiple filters on our data set.

So in this case the pay rate is $30 or greater per hour.

And what if I want to filter by department as well.

I'll go ahead and click on the department filter here

I want to see from my H.R. department.

I'll go ahead and press okay.

So Filtering is a very nice tool.

So we were able to narrow it down to the HR department where the pay rate is $30 or more per hour.

Here are two records.

It's a really nice way to isolate the data so that you can work on it.

And once you are done, we can just go ahead and move to the next part.

you can Move on to MF here and there we go.

So it's a really nice way to filter.

We can run multiple filters together as long as we understand logically

what we are trying to accomplish.

Sometimes we need to kind of just stop and pause and think about what we are trying to accomplish here as well.

I'll go ahead and clear my filters.

I do have two filters running.

So what I can do from the data tab, I can click on the clear filter icon here.

And this will clear all the filters that are running on my table.

So it's going to clear my department filter AND my pay rate filter.

I'll go ahead and click here.

My table is reset to display my 37 records.

See you next time with the Total Row Tool.

• The Tool of Total Row.

• Function of The Total Row.

•   Using The Total Row to get The Sum of more than one cell, column or row (Pay Rates, Hours Worked, Gross Pays & Average Pay Rates).

• Inserting New Columns.

• Calculating The Total Gross Pay & Hours Worked.

• Gross Pay Formula Entry.

So far, We've discussed Sorting, Filtering

Now we will know how to use the third tool we mentioned earlier

which is the total row.

So if we click on the table design here in the table style options,

the filter is already turned on.

Let's go ahead and turn on the total row.

The total row is going to place a row at the end of our table.

And each cell will provide a drop down.

And on that drop down will be a list of basic functions that we can use to summarize the different columns

on our table.

I'll go ahead and turn on the total row.

Here we can see the total row has been added on row 41.

And it's already has a number here.

The cell in the bottom right.

Well let's go ahead and click on that cell.

If I click on the drop down it's giving me the sum of the pay rates.

If I want to find out what is the average pay rate here, I'll click on the average and it will automatically

calculate that column.

So the average pay rate is $27.31.

Now one of the nice things is that we don't really have a lot of columns.

So we can run formulas and functions in here.

But if I were to add in, you know, maybe some hours in here and the gross pay, then we will see more

options that are available to us.

As a matter of fact, I'll go ahead and do that.

I'm just going to insert a column here.

I'll go ahead and put in the hours and put in the gross pay.

So I'll just go ahead and put in some random numbers here.

I'll start with um, 1516.

I'll continue this pattern down till I get to say 40.

Right.

And let's see, I'll just come back here and continue this pattern down as well, because why not?

Then I'll simply just add a calculation here.

Now this is a major benefit of analyzing data in a table.

Look what's going to happen here once I write a formula here on this top cell.

As soon as I write, this formula is automatically going to fill down the rest of the formulas for me.

So that's the benefit of working with the table.

So for the gross pay I'll say equal pay rate times the hours worked.

Once I press enter we can see it's automatically calculated all of our gross pays, which is a nice

feature of a table.

Now we have three other columns that we can run numbers on.

So what are the total hours worked?

I'll use the sum function for that.

1155 hours.

What is the total gross pay?

There is the sum of the gross pay.

So this allows us to quickly answer questions on our data set.

Total hours 1155.

The other thing to mention about this if I filter my data.

These numbers will update as well based on what I am filtering by.

I'm going to go ahead and filter by department.

I just want the AC department I press okay.

Notice how the numbers have changed.

The pay rate has changed.

The hours change and the gross pay changed based on the AC.

• Be aware of Different Conditional Formatting Options.

  • Cell Rules, Top & Bottom Rules, Data Bars, Color Scales.

  • Select a Range Group.

  • Clearing a Recent Conditional Formatting.

    
    

Conditional formatting is another type of filters.

So here we just have some houses.

We have the listing price, the town, the square footage, the bedrooms and the bathrooms as well.

Well, sometimes when you are filtering instead of hiding some of the data that does not meet the filter

criteria, you may want to still be able to see all of the options.

Sometimes we just kind of feel like we are missing out on something, so what we can do is use conditional

formatting, and conditional formatting can be found on the home tab.

And here are some of the different conditional formatting options that are available.

So here is conditional formatting.

So we have cell rules.

We have top and bottom rules.

We have data bars and we have color scales as well.

The great thing about conditional formatting, there is a lot of rules that are built in, and we simply

just need to go ahead and use them.

The first step of using conditional formatting is you first need to go ahead and select the range group.

So step one you need to go ahead and select your range.

Then step two you want to go ahead and choose your formatting.

We will take a look at different types.

Here.

We will have a look at data bars first.

Then we will take a look at color scales.

Then we will kind of double back and take a look at some seller rules and some top and bottom rules

as well.

So let's see how much data we are working with here.

I'll go ahead and press control.

And so go down to row 133.

So quite a bit of data here.

I'm going to press control home to go back to cell A1.

Well there is a lot of numbers here for the listing price.

I could sort this but I just want to go ahead and see which ones stand out.

I'll go ahead and highlight all of my listing prices here.

Now that they are all highlighted, I'm going to go ahead and click on Conditional Formatting.

And from here I'll use one of the data bars here.

So here's a data bar that I can use.

I'll click on that I'll go over and solid fill.

So notice what this does.

If I kind of make this column wider.

So this gives us a clear indication in terms of which values are higher than the other.

I can clearly see on row 22 this house represents one of the higher prices.

And if you scroll through this, it will give you a really good visual in terms of which ones are cheaper

or which ones are more expensive.

Now I'm going to go ahead and apply another one here.

Let's take a look at color scales.

So here are different color scales.

Here is the green yellow red color scale.

When I go ahead and remove the other conditional formatting from there.

So I'll click on Manage Rules.

And I want to remove this one here from the data bar I'll click on delete I'll click apply.

And then I will click okay.

So here is the color scale.

So the color scale we can see the green represents the higher values and the red represents the lower

values.

So you kind of scrolling through here and keep your eye on all the greens.

That gives you a really good visual in terms of which ones are standing out as well.

So very good.

Let's take a look at some other conditional formatting.

Hey, what about square footage?

I'll go ahead and highlight all of my square footage range here.

And for this one I'll click on Conditional Formatting.

Well this time I want to let's say I want to find a home where the square footage is greater than,

less than, or between a certain value.

Well, I'll say greater than, so as long as it is set to greater than.

In this case, it's giving me 2373.

I'll change this to 2500 so we can see it's already applying that light red fill of dark text.

Maybe I want green fill with dark text.

And I can go ahead and press okay.

So I can already start to eliminate some of the houses that I don't want to see because they are not

meeting my square footage requirement here.

Well, what about bedrooms?

We'll go ahead and run another one for the bedrooms and maybe I won't.

Exactly right.

So maybe I want equal two.

I want exactly three rooms.

Three bedrooms.

I'll go ahead and press.

Okay.

So now I'm getting a match with a couple of different places here.

I'm good to go.

All right.

I'll go ahead and do one more for the bathrooms.

So maybe this one, let's say I'll go for greater than, say greater than two, greater than two bathrooms.

I'll go ahead and press okay.

And it looks like we have a nice match here on row 121.

All of my requirements are being fulfilled.

So that's what conditional formatting can do for us.

We do have another type of conditional formatting in here which are top and bottom rules.

So these really give some really nice analysis.

We can highlight the top ten items on a list based on the value the top 10%, bottom ten items, bottom

10% or whatever is above the average.

So in order to do that, I'm going to go ahead and clear the conditional formatting for my listing price

here.

I'll go back to Manage Rules, and for this entire sheet, I'll go ahead and clear that graded color

scale.

I'll delete that.

I'll press apply and I'll press okay.

Let's take a look at one of the other type of rules which is the top bottom rules.

So the top 10% which one of these listing prices are in the top 10%.

I'll go ahead and click here.

And we can see it's already highlighting them for me.

I can change the number here.

Maybe the top 20% right.

I'll go back to the top and bottom rules.

What about the bottom 10%.

So some quick analysis that we can use there as well.

So conditional formatting is another type of filter.

Let's take a look at the if function.

• IF as a Logical Function.

• Tracking Sales of Sales Representatives.

• Comparing Values in Two Different Cells.

• Inserting IF Function.

• Meeting The Function Arguments Box.

• Using The Fill Handle.

• Troubleshooting inside Cells.

• Knowing How to Absolute Reference a Certain Cell.

  
  

Now, the IF function is  a

really nice tool

it's kind of the base

or the standard for all of our logical

functions

so in this case we'll ask SOME

particular questions here

we have

few salespeople S Sam

E Brown

W Danny

M Meyer

s Sandberg and A John

we're

tracking their sales for

February

for example in week one s Sam

had sales of

9550 in week ONE

Here's his total for the month

THESE ARE HIS NUMBERS

so the question that we want to

ask is

well was the goal

met so that's the number one question we

need to ask

the next question we need to

answer here is well is that

number 36245 is that greater than or equal to

the monthly goal which is

34,000

so we need to compare these two

cells

and see if that is true or if that is

false

so that's the second question

we need to

ask

the third question we need to ask is

well if it's true what value are we

going to place in this cell right so

we're either going to say yes or

no so that's it what's the goal met in

order to answer that

we use the this cell

reference here, and ask ourselves is that greater than

or equal to

34,000?

yes it's true so s Sam did meet

the goal

we'll put a yes in here as

well

that's exactly what the IF

function will do for us right we already

know the cells that we want to compare

here we're comparing cell F7 to cell

B4

we already know what we want to say

if that logical test is true we  say

yes the goal was met

if it's not true

we'll simply say

no

let's go ahead and insert our IF

function

I'm going to click on the

formulas tab here so I'll start off by

clicking on the cell where I want to

place my answer

I'll click on the

formulas Tab and

I want to go ahead and

insert my IF

function so

I'll click here on my

insert function dialogue

I'll type the

IF function up top

I will press go and

on the bottom

I'll press okay

welcome to the function arguments box

which is a really

nice tool, it gives you

the name of the function tells you what

it does and it also shows you what it's

asking for

so the first thing we need to fill

out is our logical test

remember we're comparing the

value in cell

F7 so I'll go ahead and

click on

F7 and I'll use my operator

IS

that greater than or equal to the value

that is in

B4

Excel has

already evaluated that statement

the

value in F7 is indeed greater than or

equal to the value that's in B4

because

36245 is greater than or equal to

34000

telling us that it's true so if

it's true I'll simply say yes right I'll

say yes uh GOAL IS

met

and if it's

no or if it's false in this case rather

I'll say no GOAL IS NOT

met

we can see our answer is going to be

yes GOAL met because that condition

is true

if I press okay we get yes the

GOAL met as

well now I don't feel like writing this

formula another five times here

so

remember our little friend the fill

handle we can grab the fill handle here

and pull it down and that will copy the

formula for us

but we're going to run

into an issue

and let's do a little bit

of troubleshooting to see what happens

here

I'll go and grab the fill handle

and I'll fill

down and it's copying the formula for me

but we can see we're running into an

issue here

we can see that E Brown did

not meet the goal right

31475 is not greater than or equal to 34000

neither does s Sandberg ,

DIDN'T

MEET

the goal so let's figure out what's

going on

we can double click on the cell

to gain some insight to see what's

happening with the formula

if I double

click here on the

cell so we can see what's happening it's

moving away from the monthly goal which

is 34,000

so it went down one

row because  using relative

referencing so if I go down to row

9

we can see it's locking on to the

correct total in row nine

but it's

already moved away two spots from our

monthly goal

so what we need to do is we

need to Absolute reference the monthly

goal

so

to Absolute reference

instead of writing B4

we're going to say dollar sign B dollar

sign 4 and that will tell Excel to lock

onto that cell

don't keep moving down

down  one row at a time here

I'll go

back to my original

formula and right here in the cell

I'll

place the dollar sign right in front of

the

B

I'll place the dollar sign right right

in front of the

four

I'll go ahead and press enter and

now if I autofill

down

we can see that this is working

properly if we do a quick test if I

double click

I can see it's still locking on to cell

B4

if I go to the last calculation here

it's still locking on B4 it did not

move down several rows as well

so that

is the IF function it evaluates a

statement to see if it's true IT

RETURNS

one value if it's true

another value if

it is

false

let's take a look at a few useful

database functions

we'll take a look at SUMIFs we'll

take a look at average if a

& we'll also

take a look at some ifs

See you guys in the next video.

• How to use SumIF.

• How to use SumIFs.

• How to use AverageIF.

We will take a look at SumIF we will take a look at AverageIF.

I'll also take a look at SumIFs.

Now here is our company Q1 expenses.

We have several divisions East.

North.

South.

West.

We are also tracking different categories such as tech support, telephone coaching, overhead, software.

And we are tracking the expenses.

So basically in the East in January, we had $800 worth of expenses for tech support.

And March we had $700 worth of tech support.

In the East, total for quarter one for tech support.

And the West was 2150.

So we already have our data set here.

It goes down to row 61.

So let's go ahead and answer some questions.

Well, what are the total expenses by category?

So if I wanted to go ahead and create a small worksheet over here to summarize, what were the total

expenses where the category was software.

So I need to go down to category range here.

And every time I see software I need to go ahead and add that to the running list.

So here is what I'm on row eight.

Now the one down here on row 23.

And let's see another one on row 37.

Pretty sure may may be missing one here but the one on row 53.

And we need to go ahead and add the totals from our last column here.

So we will add the 37 and 85 to the running total.

So forth and so on.

We add all of those numbers up.

And we are going to go ahead and get our total for software.

Well, we don't need to do that.

There is a function that will do that for us.

And that function is called the SumIF function.

So let's go ahead and take a look at SumIF.

So basically what we are saying is we are going to summarize the total expenses if the category is equal

to software.

So we are going to go ahead and summarize the total expense column if the category is equal to software.

So there is a function a sort of program that is going to ask us for these things.

It's going to ask us in particular.

Well what is the range.

What is the criteria.

What is the sum range.

Now we already have all this information.

We just need to go ahead and point to this.

And the sum f will take care of the rest for us.

Let's go ahead and insert our sum if statement I'll click on cell J7.

I'll head on over to the formulas tab.

I'll insert a function and I will look for some f.

So here is some f and as I mentioned it asks us for the range, the criteria and the sum range.

Well our keys are right here.

We want to search the category range.

I will highlight all of my categories from C4 down to C61.

The next one is well what is the criteria.

Well I could type software in here, but I'd rather refer to the cell reference because then I can use

this as a little input field.

So I will refer to that cell software.

I'll go ahead and click here.

So i7 whatever is an i7.

We will perform our calculation.

The last one is the sum range.

So which column do I want to summarize.

Is Is it the January expenses?

February.

March?

No.

I want the total expenses.

I'll highlight g2 g 61.

We already have our answer.

It's already found all those instances where the category is software.

The total is 17,215.

We'll go ahead and press.

Okay.

And there is my answer.

I'll just go ahead and give that the currency format.

And I'm good to go.

Now the average, if it works the same way is going to ask us the same things.

Right.

Range criteria and the average range.

So for this one we will use the average if function.

So the question we want to ask is well what is the average per rent expense.

Right.

So we will count the amount of rent expenses we had will summarize the total expenses and divide that

by the amount of entries that we had for rent.

We have all the information that we need.

The range is going to be the category criteria is going to be the rent.

We want to go ahead and run the math on our total expenses column.

So I'll go ahead and insert the function here.

I'll look for average f.

I'll press okay.

Same thing.

Go ahead and highlight that range from C4 to C61.

The criteria needs to be rent.

In this case it's cell i13.

The average range needs to be the total expenses.

Highlight all my total expenses and I'm good to go.

So the range is our category.

And the criteria is expressed right here.

And then finally we want to run the count the numbers on the total expenses column.

So here we go.

Our total is 17,790.

90.

I'll also apply the same format to there.

I'll use my format painter.

Now, the benefit of using the cell reference as opposed to typing it in here.

Now what I can do is I can use this as a dashboard.

What are the total expenses for supplies?

If I type supplies in here and press enter, it gives me the updated answer right away.

I'll change this from rent to supplies as well and we get our instant answer as well.

So that is the sum if function and the average if function.

Let's write a function that will give us the expenses by division and category.

So what were the total expenses?

Where the division is east and the category is software.

We can see there is a match right here on row number Eight.

Here the division is east.

The category is software.

The total expenses here as well.

There may be some other matches.

Let's just go ahead and check.

So to do this we need a function that will allow to logic tests.

So two ranges and two criterias.

SumIF only allows for one.

So we need to use SumIFs which will allow for multiple.

I'll go ahead and click on my formulas tab here.

Let's insert the SumIFs function.

Here is my SumIFs.

So again it's going to ask for the same thing.

What is the sum range and what is the criteria.

It wants to get the sum range right away.

We already know we want to summarize the total expense column.

So I'll go ahead and highlight all these from G 4 to 61.

Here is a preview of the array.

Now criteria range one.

So I will highlight all my divisions.

Criteria one which division we are looking for.

Well we are looking for the East Division represented in cell I 19.

Now that we have finished criteria range one and criteria one we now have room for criteria range two.

We'll go ahead and click here.

The second one the category needs to be software.

So we will highlight all of our categories.

Criteria two needs to be software and we are done.

The total is 5875.

So apparently there was only one entry or one instance where we had a software Were expense in the East

region.

So that is the Sumifs function.

Now it did not matter.

I could have used the category range first, then division second.

It does not matter.

It would still make the calculation for us here as well.

I'll go ahead and press.

Okay.

And I'm good to go now because we are using the cell reference I can go ahead and change this.

So what about instead of software in the East?

What about rain in the east?

I get an instant answer.

24,000.

What about rent in the north?

So now we have a really nice dashboard that we can use to summarize all of our data.

So this one is the sum function.

Really nice tool.

It Takes away the manual work of highlighting several columns in several cells and adding it all up manually.

• Inserting a Charts.

• Analysis of Sales Figures.

• Using Recommended Charts.

• Using Charts Styles.

• Introduction to Chart Elements.

• Adding Axis to Charts.

• Adding Titles to Charts.

• Using Gridlines & Legend inside Charts.

Let's take a look at recommended charts.

Here we just have some sales figures.

Here is our sales rep.

We have Smith, James, O'Brian,  Carrie and Dunn

So week one R Smith 4520.

Week two 3620.

Week three 2560.

And then.

Week four 2750.

Well, we can create a chart from this.

I just need to go ahead and click on the insert tab here.

Now when we are inserting a short I don't have to highlight the entire data.

I can just click anywhere inside of my list here because it's a well defined list.

No blank cells, rows or columns.

So I can go ahead and insert any type chart that I want.

But what I want to do is insert a recommended chart, because Microsoft Excel is going to give me a

customized set of charts that it thinks it will fit best with my data.

So I can get an automated chart here, which is exactly what I want.

If you like shortcuts, we press Alt Plus F1 on our keyboard.

We can get the default chart for this data set here.

I'll go ahead and press alt F1 on my keyboard.

And just like that, here's our chart for our data.

Now I'm going to go ahead and undo that.

So the traditional way is to click on the insert tab.

Here are some Recommended charts.

If I click here here is that clustered column chart.

There are different charts that we can choose from.

Here is a stacked column.

Here is another clustered column.

It just kind of changed the data around.

We have the weeks in here instead of each of the sales reps.

If we scroll down we have different charts clustered bar line chart.

So lots of charts that we can use.

I'll stick to the default one here which is the clustered column.

I'll go ahead and press okay.

And here's my chart.

So pretty cool.

Have a few buttons over here in the top right of my chart.

These are chart elements.

So what I can do is I can turn some of these features off or on.

So for example, if I wanted to add an axis I could go ahead and do that.

I'll click on axis title, add a little text box on the horizontal and vertical plane, but I just want

one on the horizontal.

So I'll remove it from the vertical here.

And I can just go ahead and type some data in here.

Maybe I'll just say sales reps.

I'll go ahead and add a title for my chart.

I'll just call this February Sales.

Now some other things I can show the data labels at the top of the columns if I want to.

I can show the data table on the bottom of the charts, which is pretty cool.

And then I can turn off the grid lines.

I can also turn off the legend or I can move the legend.

So if I click on the small arrow here, I can show the legend at the top, or I can show it over at

the right.

I'll leave it over here on the right for now.

Still with our Clustered Column Chart here...

I can also turn off the legend or I can move the legend.

So if I click on the small arrow here, I can show the legend at the top, or I can show it over at

the right.

I'll leave it over here on the right for now.

Another option is to click on the paintbrush here.

So maybe we would just want to go ahead and use one of the predefined chart styles.

So here is  style number two.

So style number two is in my case here

it's showing the data labels at the top of my columns here.

It basically removed my vertical axis over here.

So if I click on style number three

it's incorporating the data labels as well.

If I keep scrolling down there are different types that I can use right now.

I can resize the chart just kind of make it a little bigger.

So see, that looks better.

Still having some issues.

Still kind of overlapping.

So I'll turn off the data labels.

And I will go ahead and choose another style.

So, Style number eight is a dark theme.

So that looks pretty good.

I'll go ahead and stick with that one.

I can also change the colors.

So if I click on the color tab here, I'll have different colorful palletes to choose from.

Here is the colorful palette two, three, and four.

On the bottom.

I have some monochromatic color palettes to choose from, different shades of blue, and so forth and

so on.

I'll go ahead and stick to the original one here, the colorful ballot, and I'm good to go

if I want

to change the chart type.

I can always do that.

Notice that we have two contextual tabs here for our chart design.

If I click on chart design, I can change the chart type.

I can also move the chart to another worksheet.

If I want to, I can switch the rows and columns, and I can also go ahead and select which data I want to be displayed on here as well.

I'm going to go ahead and switch the row and column.

But if I click on switch row and column,...

it basically switches it.

So now instead of my sales reps being in these different clusters,

it's showing week one.

So week one

here are the totals of  James. O'Brien. Carrie. and Don.

So just as subtle switching the row and column can just give you another look for your chart

here as well.

So that's how we insert a recommended chart.

Let's go ahead and add some data to our chart.

Now if I were to add another record here on row number ten, my chart will not show the data right away.

What I would need to do is I would need to come back here and click on Select Data and go ahead and

reselect all of my data that I want to be displayed on my chart.

But what I prefer to do is just to convert this list here into a table.

Remember you could do that using Ctrl + T

And now the benefit of doing this is

if I add another record, my chart will update automatically.

So charts are dynamically linked to a table.

So look what happens when I start adding some data in here.

Notice it's already reserved a space for my new data entry.

So pretty cool right?

I'll go ahead and put some values in here.

And there we go.

So that's a nice simple way to be able to have your chart dynamically linked to your data set as well.

Now we can change the chart type

I'll click on Chart Design here

I'll change the chart type.

Maybe I want something else like a 3D clustered column.

We'll go ahead and press okay I'll go ahead and change it to something else.

Maybe I want a line chart.

Okay.

So whatever chart you change it to, it's going to still incorporate your data into the chart.

So we are good to go.

I'll move this chart.

I'll go ahead and move it to its own sheet.

I will use the new sheet command here.

I'll press okay.

Here is the chart on a special sheet.

We can notice that the gridlines have been removed,

and we are able to just go ahead and display our chart here.

Even though we move the chart to another sheet,

it's still dynamically linked to our original data set.

So if I go back and make any changes, it will update automatically for my chart here as well.

• Inserting a Recommended Chart.

• Chart Design Tab.

• Choosing Different Chart Color Palettes.

• Switching between Rows & Columns inside Charts.

• Using Horizontal & Vertical Axis.

• Convert List into a Table.

• Linking Charts into Tables (Dynamic).

• Select Data Source.

• Moving The Data (Charts) into Another Sheet.

• Using Gridlines.

• Using a 3D Clustered Column Chart.

Another type of chart that we can take a look at is a small chart.

What if we don't want to have a large chart?

We just want to have a chart in a cell, maybe for some trend analysis.

Well, let's take a look at wherever a few sales reps here down to row 31.

Our first sales person is Kathy.

So we were tracking Kathy's sales for May, June, July, August, September and October as well.

So if I were to ask you if you can just kind of give us an analysis to see how Kathy's sales are trending.

Well, we can see that from May to June, we went down in June, we went up in July.

We went back down in August, went up significantly in September and also went up in October.

So this is my attempt at just kind of showing how Kathy is trending here as well.

Let's insert what's called a sparkline.

Right.

So I'll go ahead and put trend analysis right here.

I'll click on the insert tab.

You'll notice that we have a group that's called Sparklines.

There are three different types of sparklines that we can use.

We have one loss.

We have column.

We have the line I'll insert a line sparkline.

We can always change it to column when loss later.

Let's go ahead and insert a line here.

So I'll click on the line and it tells us right here.

Sparklines are mini charts placed in single cells each representing a row of data in your selection.

Well, that's exactly what we want here.

It's going to ask me two things.

Where do I want to place the sparkline and where is the dataset that I want to analyze.

So here we go.

The location I want the Sparkline to be right here in cell I12.

It's already selected while my data range.

I want to go ahead and analyze Kathy Sales from May through October.

I will simply need to go ahead and highlight this range and we are good to go.

Here are the numbers that I want to analyze.

Here is where the sparkline will be placed.

A Sparkline is going to do the work for me.

There is a lot happening in the background that we are not aware of.

I'll go ahead and press okay.

And here is our trend analysis so we can see went down, went up, went down, went up in September

and kind of stayed the same in October as well.

I'm going to go ahead and make the row a little higher.

I'll make it 28.

There we go.

So that allows us to kind of see a little better here.

We can also do things like change the background of the cell to make sure it's kind of standing out.

But we're good to go.

Now once we insert the sparkline I can click on the sparkline right here.

And I can do pretty nice things here.

So I can kind of highlight the high point and the low point here as well.

I can also go ahead and give it a particular style.

Just changing the color of the sparkline here.

I'll go ahead and rotate the high point.

Notate the low point as well.

We can see that the low point is here for June.

High point is for October.

I'll also go ahead and change the style.

So lots of different styles are here.

Again, changing the fill color may help for this to show a little better.

I'll look for orange and I will change the fill color of that cell to dark blue.

And there we go.

Now for the high point.

I'll change the color to if I go with white here.

So we can do some pretty things here with the spark line here as well.

I'm going to go and remove the fill color from there.

I'll say no fill and I'll put the marker color for my high point back here as well.

So there we go.

That's really nice about the trend analysis.

If I change the values here this is going to update automatically.

Right.

So notice how it updates automatically here as well.

So this is pretty cool.

So another object that's dynamically linked to our data set here as well.

Now I want to go ahead and create Sparklines for my remaining sales reps here.

I don't want to come in here and create individual sparklines.

What I can do is just grab our little friend here, the fill handle, go ahead and pull it down, and

this will create the individual sparklines for the rest of my sales reps as well.

For I do that, I'm going to change this to the column.

So here is the small column chart.

You can see high low high low.

We can also see that the high and low points are being indicated here as well.

We also have a win loss win loss only looks for positive and negative values.

So positive values appear at the top of the cell.

Negative values appear at the bottom.

So it doesn't really apply here because we only have positive values to work with.

I'll go ahead and put this back to line.

I'll go ahead and auto fill down.

Pull it down to row 31.

And here are individual sparklines I'll go ahead and adjust the row height for all of them so we can

see them a little better.

I'll change the row height to 28 and there we go.

So that was how to insert and manage Sparklines.

• Charts inside Cells.

• Trend Analysis.

• Inserting a Sparkline (Mini-Charts in Single Cells)

• Inserting a Line from Sparklines.

• Sales Analysis using Sparklines.

• Changing Cells' Background.

• Styling Color of Sparklines, Using High Point & Low Point for Sparklines.

• Changing Fill Color of Cells.

• Linking Objects to Data Dynamically.

• Line, Column & Win-Loss Sparklines (Positive & Negative Values).

• Adjusting a Row Height.