Excel Data Analysis Full Course Tutorial (7+ Hours)

[Music]

welcome to learn it training the

exercise files for today's course are

located in the video description below

don't forget to like And

subscribe welcome to Microsoft Excel

introduction to data analysis you're

learning with Mo Jones IT professional

and educator and this compact course

we'll be learning all the essentials for

data analysis here's what we'll explore

today we'll convert lists into a table

and take a look at the table tools that

are

available we'll use excel's aggregate

functions to conditionally apply

calculations to our data we'll use pivot

tables to summarize our data set and

then we'll take it a step further we'll

create pivot charts to create visuals

from our data set go ahead and open up

the practice file for this course and we

are going to Dive Right

In looking to support our Channel and

get a great deal become a member today

to unlock AdFree videos that's right

your favorite courses without a single

ad interested in a specific video

purchase one of our adree courses

individually looking for even more gain

access to exams certificates and

exclusive content at learnit anytime.com

more information can be found in the

video description

below on our creating tables worksheet

tab

we have some basic employee data so

notice that we have well- defined

headers we have employee 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 because the headers

represents the name of the field in

which we want to analyze or in this case

the column that we want to analyze so we

want to make sure that they are named

well that they're meaningful and they

accurately describe the data below the

next thing that we want we want to make

sure that we have unique labels now this

is not always the case but it's best to

have unique labels when you're working

with data so in this case our unique

labels we can see that we have unique

employee IDE

numbers and this will give us

some better opportunities to kind of

just filter our data down to what we're

looking for as well the other thing that

we want to make sure that we have

complete

records so as long as our list has

well-defined headers unique labels and

complete records meaning no blanks then

we are good to go we can see that this

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'll run into some

problems one of the problems that we'll

run into is that our data analysis will

be incorrect or incomplete because we're

actually missing data so it's usually a

good idea just to kind of scroll through

your data set make sure that you don't

have any blanks and there is well you

can always use the count blank function

and this will kind of help you to count

the amount of blanks that you have in

your data so I'll just enter the count

blank function here and I'm going to go

ahead and highlight all of my data so

Excel is going to count all the blank

cells that it finds in the range A3 down

to I40 if I press enter I have zero

blank cells and I'm good to go now if I

were to delete a cell tells me right

away that have one blank cell right so

those are some good first steps some

other tricky things to kind of look for

especially when you're working with

dates and 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 here 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 something like

custom or

general so you want to make sure that

you make those adjustments here we're

all good here for the pay rates as well

we can see that that's using the

currency format and so we are pretty

much ready to go ahead and analyze this

data in a table

let's convert this list into a table and

then we'll have some tools that we can

use to analyze the data now there's two

ways that we can convert this list into

a table here's the first way um we can

actually use a shortcut so if we press

control plus

T that will convert this to a table for

us as long as we're 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 the 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'll use the first method which is

control T So if I press control T it

just basically asks me you want to

create a table where is the data for

your table and does your table have

headers my table does have headers so

I'm good to go and if I scroll down I

can see that it's is highlighting all of

my data I'll go ahead and press okay and

it converts it to a table for me so

here's the table form we know that this

is a table because if we take a look at

the ribbon we now have the table design

Tab and this is where we'll 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 can also go ahead and take a

look at the different table styles that

are avail available as

well going to go ahead and undo that so

I'll press contrl 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 we can

choose our table style ahead of

time so there you go so here is my list

that's now converted to a table well now

that we've converted this to a table we

can do things like sort

by columns we can also do some filtering

as well we also have another tool it'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 the filter I can go ahead and

sort and then down here I can use a

logical

filter I can filter by a search term or

I can go ahead and select a

unique value from down here so there's

actually one two so four different

opportunities for us to use this filter

dropdown to analyze our data well I want

to go ahead and sort so I'll click on

the I'll sort by last name here and I

will sort 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 the column explicitly the

entire record is moving as well so if I

were to go ahead and maybe sort by

employee ID

number we can see that the entire record

travels here as well so we can do some

simple sorting in here as well but what

if I want to do some more complex

sorting we would need to click on the

data Tab and 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 employee ID it removed

the sort from my last name here well

what I would like to do is maybe go

ahead and sort by let's see I'll go

ahead and sort by last name and

department so how can we sort by last

and

Department well we need to click on this

sort icon here and this will allow me to

select the different columns in which I

want to to sort by I'll go ahead and

click on sort now here we can see that

my data has headers is already checked

off now I can select the column that I

want to sort by so I want to sort by

last

name well maybe I'll do Department first

department is a higher level so let's

sort by a department and then by last

name well for me to do that I need to

click on ADD level so I can add another

level here so that I can go ahead and

sort by last

name so I'll click on ADD level and I'll

choose last name I'll make sure that

they both say A to Z and I'm good to go

once I press okay we can see it's sorted

by a department so here's AC and inside

of that department is also sorted by

last name here's the ad department and

that's also sorted by last name so

that's some basic sorting that we can

perform on our

table let's take a look at the filters

that we can use I'll go ahead and click

on a filter for the higher date let's

see what filters are available for the

higher date we'll also take a look and

see what filters are available for the

pay rate so we're working with dates and

numbers now for the date filters we have

a lot of date filters I can look for a

higher 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 l last year last month

last quarter so those are different

things that I can do so if I click on

between what if I want to know how many

hires we had between two dates so if I

click on between here here's 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 and but before

December

31st 2006 I could also click on the date

picker here and choose my date from here

as well let's see how many hires did we

have between 2000 and 2006 if I press

okay here are my five records if you

take a look at your status bar tells you

that five of 37 records were found we

can see that these all fit that

description they all fall between those

two particular dates so that's one way

that we can filter using that logical

operator going to go ahead and clear the

filter from the higher date so we can

see the filter is active here I'll go

ahead and click on it and I'll click on

the clear filter from higher date this

will reset my table display my 37

records well let's go back to the higher

date and this time let's go ahead 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 20 2010

here notice how it collapses for me for

2010 so it looks like I had a couple of

hirers in here if I press

okay here are my three hires for

2010 so we can see the filters really

nice tool excellent tool for doing

Simple data analysis I'll go ahead and

clear this filter as

well and then we'll run one more so this

is the other level so so we use the date

filter we use the search box now I can

just choose an individual month day or

year down here as well so what I'll do

is I'll unselect all and I just want to

find out how many highers we had in 2006

and

2007 if I press

okay here are

my four records so either or in 2006 or

2007

I'll go ahead and clear the filter from

there 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 in

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 say we

can see we go from 20 to

3676 let's show all of the numbers that

are greater than or equal to

30 here's my simp simple dialogue I'll

type 30 in here I'll go ahead and press

okay and there we go so here are my 13

records right so 13 employees are

earning 30 or greater 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 we want to filter

by the Department as well I'll go ahead

and click on the department filter here

I only want to see from let's say my HR

department I'll go ahead and press okay

and this is a very nice tool so we're

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're

done we can just go ahead and move on to

the next department so move on to

MF here and there we go a really nice

way to filter we can run multiple

filters together as long as we

understand logically what we're trying

to accomplish sometimes we need to kind

of just stop and pause and think about

what we're 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 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 now there's another tool that we

can use which is called the total row so

if we click on the table design here and

the table style options the filters

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 will provide a

dropdown and on that dropdown 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 on the bottom right so it says

1,030 well let's go ahead and click on

that cell if I click on that dropdown

it's giving me the sum the pay rates

well that doesn't make sense if I want

to find out what is the average pay rate

here I'll click on average and it

automatically calculates that column so

the average pay rate is

$27

31 now one of the nice things is so we

don't really have a lot of columns that

we can run formulas and functions in

here but if I were to add in you know

maybe um some hours in here and the

gross pay then then we will see more

options that are available to us as a

matter of fact I'll go ahead and do that

just going to insert a column here so

I'll go ahead and put in the

hours and then I'll go ahead and put in

um the gross

pay so I'll just go ahead and put in

some random numbers here I'll start with

20 I'll start with

15 16 I'll continue this pattern

down till I get

to say

40 right and let's see then I'll just

come back

here and continue this pattern down as

well why

not then I'll simply just add

a add a calculation here now this is a

major benefit of analyzing data in a

table look what's going to happen once I

write a formula here on the on this top

cell as soon as I write the formula it's

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

1,155 hours what is the total gross pay

here's the sum of the gross pay

31,8 4351 so this allows us to quickly

answer questions on our data set average

pay rate is

2731 total hours 1155 resulting in gross

pay of 3184

351 the other thing to mention about

this

is if I filter my data these numbers

will update as well based on what I'm

filtering by I'm going to go ahead and

filter by

Department I just want the AC Department

if I press okay notice how the numbers

have

changed the pay rate has changed the

hours have changed and the gross pay has

changed based on the AC

Department conditional formatting is

another typee of filter 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're filtering instead

of hiding some of the data that does not

meet that filter criteria you may want

to still be able to see all of the

options sometimes we just kind of feel

like we're missing out on something so

what we can do is use conditional

formatting and conditional formatting

can be found on the home Home tab and

here's some of the different conditional

formatting options that are available so

here's 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's a lot of

rules that are built in and we simply

just need to go ahead and use them the

first step in using conditional

formatting is you first need to go ahead

and select your range right so step one

one you need to go ahead and select your

range and then step two you want to go

ahead and

choose your

formatting so we'll take a look at a

different a couple of different types

here we'll take a look at data bars

first then we'll take a look at color

scales then we'll kind of double back

and take a look at some cell rules and

some top and bottom rules as

well so let's see how much data we

working with here I'll go ahead and

press control end so we go down to row

133 so quite a bit of data here going to

press control home to go back to sell A1

well there's 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're all highlighted I'm

going to go ahead and click on

conditional

formatting and I'll use one of the data

bars here

so here's a data bar that I can

use I'll click on the I'll go over the

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 gives you a

really good visual in terms of which

ones are cheap cheaper or which ones are

more

expensive now we going to go ahead and

apply another one here let's take a look

at color scales so here are different

color scales here's the green yellow red

color scale I'm going to 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 for

the data bar I'll click on delete I'll

click apply and I'll click okay so

here's the color scale so the color

scale we can see the green represents

the higher values and the red represents

the lower values so as you kind of

scrolling through here keep your eye on

all the greens and 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 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

unconditional 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's greater than in

this case it's giving me

2373 I'll just change this to

2500 right so we can see it's already

applying that light red fill of dark

text maybe I want green fill with dark

text and now I can go ahead and press

okay so I can already start to eliminate

some of the houses that I don't want

because they're not meeting my square

footage requirement here well what about

bedrooms we'll go ahead and run another

one for

bedrooms and maybe I want

exactly right so maybe I

want equal to I want exactly three

bedrooms I'll go ahead and press okay

so now I'm getting a match 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 m

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 10

items on a list based on value top 10%

bottom 10 items bottom 10% whatever is

above the average so to do that I'm

going to go ahead and clear the

conditional formatting for my listing

price here so 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 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% I'll go back to 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 now the IF function is 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 a

particular question here is we have a

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

48965 for a total of

36245 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 the

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 FSE 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 we use the cell

reference here is 36,2 245 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 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 or 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 here is 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

really nice tool because 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 well our logical

test is remember we're comparing the

value and cell F7 so I'll go ahead and

click on

F7 and I'll use my operator I'll say 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

34 it's telling us that it's true so if

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

say yes uh gold

met and and if it's

no or if it's false in this case rather

I'll say no go not

met we can see our answer is going to be

yes gold is met because that condition

is true if I press okay we get yes the

gold is 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 34

so that does not compute neither

does let's

see neither does s Sandberg did not 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 is 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 we're

going to write 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 uh 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 to 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 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

a few we'll take a look at some if we'll

take a look at average if and we'll also

take a look at some ifs now here is our

peer company q1 expenses we have several

divisions East North South and

West we're also tracking different

categories such as tech support

telephone copying overhead

software and we're tracking the expenses

so basic basically in the east in

January we had $800 worth of expenses

for tech support in March we had

$700 worth of tech support in the East

total for quarter 1 for tech support in

the East was

2,150 so we already have our data set

here it goes down to row

61 so we can see it's row 61 1 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 what I would

need to do I need to go down the

category range here and every time I see

software I need to go ahead and add that

to the running list so here's one on row

eight

another one down here on row

23 and let's see another one on row 37

pretty sure I may be missing one here

another one on row

53 and we need to go ahead and add the

totals from our last column here so

we'll add this 3785 to the running total

this

3750 this

3805 and so forth and so on so we add

all those numbers up and we'll 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 sum IF

function let's go ahead and take a look

at sum if so basically what we're saying

is we are going to

summarize the total expenses

if the category is equal to Software

so we're going to go ahead and summarize

the total expense column if the category

is equal to

software so there's a function that's

already programmed that's going to ask

us for these things it's going to ask us

in particular well what is the

range what is the

criteria and what is the sum

range now we already have all the

information we just need to go ahead and

point to these and the sum of 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 formulus tab I'll insert a function

and I'll look for Su if I'll press go

I'll press okay here is some if and as I

mentioned ask us for the range the

criteria and the sum range well our keys

are right here we want to search the

category range so I'll highlight all of

my categories from C4 down to

c61 the next one is well what is my

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'll refer to that cell instead

of software I'll go ahead and click here

so so i7 whatever is an i7 will perform

our calculation two down one to go the

last one is the sum range so which

column do I want to summarize is it the

January expenses February March no I

want the total expenses I'll highlight

G4 to

g61 we already have our answer it's

already found all those instances where

the category is software the to is

1,215 I'll go ahead and press okay and

there's 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 it's going to ask us for the same

things right range criteria and the

average range so for this one to answer

this question we'll use the average IF

function so the question we want to ask

is well what is the average per rent

expense so

we'll count the amount of rent expenses

we had we'll summarize the total

expenses and divide that by the amount

of entries that we had for rent we

already have all the information that we

need the range is going to be the

category the 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

if I'll press go I'll press

okay what is our range well category so

same thing go ahead and highlight that

range C4 to

c61 the criteria needs to be rent in

this case it's sell

I13 the average range needs to be the

total expenses highlight all of my total

expenses and I'm good to

go so the

range is our category

range right the

criteria is expressed right here in this

cell and then finally we want to run the

C the numbers on the total expenses

column

so there we go our total is

17790 I'll go ahead and press okay and

we are good to go I'll also apply

the same format to there I'll use my

format painter

17790 now the benefit of using these

cell reference as opposed to typing it

in here now what I can do is I can use

this as a dashboard what were 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

category so what were the total expenses

where the division is east and the

category is software so for example we

need to go ahead and find we can see

that there's a match right here on row

number eight here's the division is East

the category

software here's 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

two logical tests so two ranges and two

criterias Su if only allows for one so

we need to use sum ifs which will allow

for multiple I'll go and click on my

formulas tab here let's insert the sum

ifs

function here is my sum ifs so again

it's going to ask us for the same thing

what is the sum range what is the range

and what is the criteria it wants to get

the sum range out of the way right away

we already know we want to summarize the

total expense column so I'll go ahead

and highlight all of these here G4 to

g61 here's a preview of the array now

criteria range one well it's going to be

division so I'll highlight all of my

divisions criteria one which division

are we looking for well we're looking

for the East Division represented in

cell

i19 now that we finished criteria range

one and criteria 1 we now have room for

criteria criteria range to I'll go ahead

and click here the second one the

category needs to be software so we'll

highlight all of our

categories criteria two needs to be

software and we're

done the total 5875 so apparently there

was only one entry or one instance where

we had a software expense in the east

region

5875 so that is the sum ifs

function now it did not matter I could

have used the category range first and

then division second does not

matter it would still make the

calculation for us here as well I'll go

ahead and press okay and I am good to go

now because we're using the cell

reference I can go ahead and change this

so what about um instead of software in

the East What about rent 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

ifs function really nice tool takes away

the manual work of

highlighting several columns and several

cells and adding it all up

manually let's take a look at

recommended charts here we just have

some sales figures here is our sales rep

we have R Smith H James S O'Brien l Cary

and K dun here are their weekly totals

so week one R Smith 4520 week 2

3620 week 3 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're

inserting a chart 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 of 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 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 is our

chart for our

data now I'm going to go ahead and undo

that and go ahead and delete my chart

here so the traditional way is to click

on the insert tab here is recommended

charts if I click here here's that

clustered column chart there's different

charts that we can choose from here's a

stack column here's 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 is my chart so

pretty cool I 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 for example if I wanted to add an

access title I can go ahead and do that

I'll click on access title adds a little

text box of the horizontal and vertical

plane but I just want one on the

horizontal so I'll remove it from the

vertical here and now I can just go

ahead and type some data in here maybe

I'll just say sales

[Music]

reps I'll go ahead and add a title for

my chart I'll just call

this February sales

I'm good to go 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 chart 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

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's style

number two so style number two in my

case here it's showing the data labels

at the top of my columns here it's

basically

removed

my vertical axis over

here if I click on style number

three it's incorporating the the data

labels as well and if I keep scrolling

down there's just different types that I

can use right now the other styles my

data labels are just kind of overlapping

each other I can resize the chart just

going to make it a little bigger see if

that looks any better still having some

issues they're still kind of overlapping

so I'll turn off the data

labels and I'll go ahead and choose

another Style 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

paletts to choose from here's colorl

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 that colorful palette

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 do a couple of things

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 and just a

really subtle effect 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's the totals for R

Smith James O'Brien Carrie and dun so

just a subtle switching the row and

column can just kind of 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 10 my

chart will not show that 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 the

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 so

remember we can press contrl t convert

this to a table 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

entry so pretty cool 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 also go ahead and change it to

something else maybe I want a

line chart go ahead and press okay so

whatever chart you change it to it's

still going to incorporate your data

into that

chart so we're good to go I'll go ahead

and move this

chart I'll go ahead and move it to its

own sheet I'll use the new sheet command

here I'll press

okay here's the chart on a special sheet

we can notice that the grid lines have

been

removed and we have the capability just

to 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 another type of chart that we can

take a look at is a small chart so what

if we do not 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 here we have a few

sales reps here down to row 31 our first

salesperson is Kathy Albertson so we're

we're tracking Cathy's sales for May

June July August and 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 went back down

in August went up significantly in

September and also went up in October as

well so this is my attempt at just kind

of showing how Kathy is trending here as

well well we'll appreciate the trend

line better than my failed attempt here

at trying to manually create a trend

analysis let's insert what's called a

spark line right so I'll go ahead and

put

Trend analysis right

here so here's my Trend

analysis so click on the insert tab now

when you click on the insert tab you'll

notice that we have a group that's

called spark lines there's three

different types of spark lines that we

can use we have win loss we have column

and we have the line I'm going to go

ahead and insert a line spark line we

can always change it to column and wind

lost later let's go ahead and insert a

line here so I'll click on the line and

it tells us right there spark lines are

many charts placed in single cells each

representing a row of data in your

selection well that's exactly what I

want it's going to ask me two things

where do I want to place the spark line

and where is the data set that I want to

analyze so pretty nice so here we go the

location I want the spark line to be

right here in this cell i12 it's already

selected well my data range well I want

to go ahead and analyze the

data Kathy sales from May through

October so I simply need to go ahead and

highlight this

range so we're good to

go here are the numbers that I want to

analyze here's where the spark line will

be placed I'm good to go spark line is

going to do the work for me there's a

lot happening in the background that

we're not privy to really appreciate

this tool I'll go ahead and press

okay and here's our Trend

analysis so we can see went down went up

went down went up

significantly in September and kind of

stayed the same for October as well now

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

little

higher so I'll make it

28 there we go so that allows us to to

kind of see it a little better here we

can also do things like change the

background color of the cell to make

sure it's kind of standing out but we're

good to go now once we insert the spark

line I can click on the spark line right

here and I can do some pretty nice

things 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 really just changing

the color of the spark line here so I'll

go ahead and notate the high point

notate the low Point as well we can see

the low point is here for June high

point is for um

October I'll also go ahead and change

the

style right so lots of different styles

here again changing the fill color may

help for these to show a little better

I'll look

for go for orange and maybe I'll change

the fill color if that cell

to that's probably too dark May I'll go

for

a dark blue

here and so 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 nice things with the spark

line here as well right going to go

ahead and remove the fill color from

there I'll say no fill

and need to go ahead and put the marker

color for my high

point back here as well so there we go

now what'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

pretty pretty cool so another object

that's dynamically linked to our data

set here as well now I want to go ahead

and create lines for my remaining sales

reps here I don't have to come in here

and and create individual spark lines

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 spark lines for the rest of

my sales reps as well before I do that

I'm just going to go ahead and change

this to the column so if I click back on

the spark line here I want to go ahead

and change this to the column let's see

what that looks

like so here is a small column chart you

can see high low high low high and then

High we can also see that the high and

low points are being indicated here as

well we also have wind loss wind loss

only looks for positive or negative

values so positive values appear at the

top of the cell negative values appear

at the bottom so it does not really

apply here because we only have positive

values to work with I'll go ahead and

put this back to line and then I'll go

ahead and autofill

down pull it down to row 31 and here are

individual spark

lines I'll go ahead and adjust the row

height for all of them so we can see

them a little

better so right click change the row

height to

28 and there we go right so that is how

to insert and manage spark lines

here we have some order details for

December of

2023 if we scroll down this goes down to

row 68 so quite a few entries here now

what if we were asked to be able to

answer some questions on this data set

so we need to go ahead and present this

data in a way that our audience will be

able to decipher the information

understand be able to ask questions as

well so we can see that we have a few

things here we have the order ID we have

the order date customer ID but what if

one of the questions was well what were

the total sales for our products based

on

city what about by

state what about by region sales

rep what about by product or by

category so we can see that we have

several options here we have the revenue

that's being calculated here and column

U we have quite a few columns as well so

in terms of categories here are the

distinct categories that we have so we

want to be able to run some numbers

based on

categories we have several products lots

of products as well how can we find out

what the total sales were for green tea

and New

York now in terms of let's say region we

have four regions east north south and

west what about sales by sales rep we

have quite a few sales reps here as well

so this is a daunting task because again

this is a lot of data and we would need

to be able to manipulate this data sort

filter run some calculations right use a

lot of the table tools

but it's going to be a lot of work just

to answer one question we're going to

have to make space and then move things

around that's where a pivot table comes

in so the pivot table already has a lot

of built-in functionality for us we

simply need to go ahead and convert or

extract this data into a pivot table and

then we'll have a lot of analysis tools

um available to us I do want to check

out the quick analysis tool though so

for the quick analysis tool

[Music]

or I'll just say quickly

analyze so this is something that I use

often here if you press uh control+

Q on your data set this will open up the

quick analysis dialogue so I'll press

control q and down here in the bottom is

giving me some some

quick analysis tools so remember I want

to insert a pivot table so I'll click on

table tables here and let's see what

pivot tables Excel can create for

me so look at this very first one this

is giving me the sum of Revenue by

region so I can see that the highest

revenue was in the North Region

26912 so pretty

cool sum

of let's this one sum of Revenue by

shipper name so we can see company C we

had the most Revenue through that

shipper

and then this one here is the sum of the

shipping fees that one doesn't really

help too much so we can see that we can

get some quick analysis and this is

basically what we want to do we want to

condense all of these rows and columns

into a a nice simple table where we can

quickly answer some questions now if I

click here this will actually insert

that pivot table for me so if I click

here here is my pivot

table right out of the box and I can see

all the data right here as well but

let's go ahead and create it from

scratch I'll go back to my data tab

here and to insert a pivot table let's

go ahead and let's first convert this to

a table so I'll press contrl

T convert this to a

table when you insert a pivot table from

a table you simply need to right click

on the pivot table and refresh if you

want to update the pivot table to show

any new values or records that were

added to your data set so it's much more

efficient than working with a list okay

now one of the table tools that's

available is actually to summarize our

data with a pivot table so I can click

on here and this will open up a new

worksheet for me and I'll have the a

blank canvas to work with I can also

click on the insert tab

and choose the pivot table command they

both do the same thing I'll do this the

traditional way I'll click on insert and

I'll click on pivot table just make sure

that you clicked on any cell inside of

your data set here I'll go ahead and

click on pivot

table here's my dialogue box as always

it's always asking us what we want to do

before we actually proceed so it's

asking me well where is the table or

range it's locking onto this table which

is now named Table Six I want to go

ahead and and place my pivot table on a

new sheet I'll go ahead and press

okay and so here we are here's our blank

canvas here's our pivot

table over here to the right here are

all the fields from our data set and

then we have these four areas

filters columns rows and values and

we'll see just by

dragging these fields to these boxes it

will start to generate

our pivot table over here as well but

the first thing I want to do I want to

go ahead and rename my pivot table right

so I'll go ahead and right click

here and I'll choose the pivot table

options

command now right at the very top it

says pivot table name right now it says

pivot table 5 I'll go ahead and change

that I'm going to call this

December

sales so one more thing I want to do if

you take a look at our our Fields here

we can see that they are not sorted in

alphabetical order we have quite a few

Fields so it'll make sense if we can

sort these a toz to do that I'll go

ahead and click on the display tab

here and on the bottom I'll be able to

sort A to Z so I'll sort the fields A to

Z I'll press

okay and there we go all right let's go

ahead and start building our table let's

just add a few I want to go ahead and

get

the total revenue so I'll look for

Revenue I'll drag that to the values box

and right there it gives me my answer my

total revenue $ 81,1

785 so pretty cool now if I want to go

ahead and change that I want to give the

accounting or currency format what I can

do is click on the drop down right here

in the values

box I want to get to the value field

settings here

and from here I can go ahead and change

the number

format to

accounting I'll go ahead and press okay

and then I'll go ahead and press okay

again there we go so that's how we

insert a pivot table and that's how we

add a feel to the box to get our first

answer now let's go ahead and try and

answer some questions on this data set

here now what we want to do we're

summarizing data we want to add

numerical values to our values box here

now we can build the table either by

dragging for example the revenue to the

values box here or we can ask the pivot

table to kind of construct itself for us

so if I were to just click on the check

box here for Revenue it places it into

the values box for me because it's a

numerical value it's also summarizing

the revenue as well right so but I can

change this so what if instead of

summarizing the revenue I want to count

how many transactions we had let's say

by

region so because we have a order ID

let's see here's my order ID I'll go

ahead and click

here so it's giving me the sum of the

order IDs well that's not what I want I

want to count how many orders I had so

to do that instead of the sum I want to

change this to the

count so to do that I'll click on the

drop down here I'll go back to Value

field settings and notice right here I

can change it from the

sum to the count function so I'll go

ahead and change that to the

count and up here for the custom name

that appears instead of saying you know

count of order ID I'll change this to

something else like I'll just say

orders I'll go ahead and press okay and

let's see what

happens so we can see it now it says

orders and so I have 65 total orders and

that's correct because we had 65 records

on our original data set so it's nice to

know that I had 65 orders but now let's

go ahead and figure out how many orders

we had let's say by region so I'll click

on region here

here and so now I'm getting some more

information so here's my grand total for

all of my orders is 65 but now I'm

getting some more specific information

so 16 orders in the East 20 in the north

12 in the South and 17 in the west so as

you're beginning to see the pivot table

there's a lot that it's doing behind the

scenes that we're not privy to a lot of

calculations a lot of logical functions

right that's happening here as well well

now that I have that I'll go ahead and

reset my table and let's go ahead and

focus on the actual Revenue right for

here I have the let's see I have

the by region let's go ahead and add

another detail in here let's put the

region into the columns

instead and let's put something else a

lower level into the rows maybe I'll put

the

ship state in the

rows so now just by doing that we can

get some really specific answers here as

well right so we can see that for

example in California and the west

region we had four orders submitted in

that particular

region so we'll start to see as we start

moving these fields into these different

boxes our table is going to get more

advanced and provide more information

for us as well but for now that's how we

change how our values are being

summarized let's go ahead and add the

revenue to the values let's go ahead and

apply the accounting format as we did

before now we have the sum of Revenue

let's go ahead and get the revenue by

region let's place the region into the

columns

and let's figure out what else we want

let's go ahead and grab the sales by

category I'll place the category into

the rows and let's just pause for a

moment and take a look at the data

that's being displayed here so this is

quite a bit of data to take a look at

right but think about this in tabular

form I'm actually going to go ahead and

click on the design tab here and I'll

change the report layout to more look

like an Excel table right we don't have

the grid lines in here I'll change the

layout here to tabular form there we go

so now we can see that we are getting

our totals so let's go ahead and

identify all of these numbers here so

here's our grand

total of all of our Revenue down here in

the bottom here's the total for the east

region north South and

West so we're summarizing that column

here as well and over on the right here

here is the totals for baked goods and

mixes so just by having the region and

the columns the categories and the rows

and the revenue and the values we can

see we can answer a lot of questions in

condensed form here right we can also

sort so if I wanted to sort this this

total column here here I can click on

the data tab I'll sort Z to A or largest

to smallest and we can see that

beverages we had the highest amount of

sales for beverages

19630 oil generated the the smallest

revenue

1,46 and

15 now we have a lot of fields in here

that we can use now when you're building

a pivot table you don't have to use all

the fields right you don't really have

to do that you can just keep it simple

go ahead and use the fields that you

want to answer a particular question I'm

going to go ahead and change my view

instead of having the region and the

columns I'm going to go ahead and move

the region into the rows and let's see

what happens when we have two fields in

one of these

boxes so now that I have two fields in

the rows I have two

levels so region is level one and my

category is a level two item so

therefore we're going to see that

indicated on on the pivot table so

here's the region that's a level one

item and then we can see that our

categories are level two as well so

they're kind of indented over so we can

see that detail here I if I switch them

I'll get the opposite so here's

beverages for the east north south and

west dried fruit for the east north

south and west so that's the nature of

the pivot table it just automatically

updates all the data for us changes all

the numbers B Bas on what we're trying

to answer now this is not the best

example you want to use higher level

items at the top of each of those boxes

so you can get a you know better view of

your data here as well I'll go a and put

the region back into the columns and I

am good to go I'll remove the category

here so I'll remove

that and I'll go ahead and click on City

I'll move the city into the rows and now

we have a breakdown for our

cities here as well let's go ahead and

filter our pivot

table we can filter a few ways we can

filter notice that we have a row and

column label

filter we also have a filter box and

finally we have on the pivot table

analyze tab we have what's called a

slicer so if I click here here is my

slicer let's take a look at the built-in

filters here here's our first filter so

we can filter by city so because we have

the city and the rows if we click on

this row label filter we can filter by

City And this is nice because if I want

to go ahead and filter let's say I just

want to filter

by I'll go for I'll unselect these here

I'll just choose Chicago and Denver if I

press okay notice how my table is only

showing the data for Chicago and

Denver well I can also go ahead and

filter by region I'll go ahead and reset

this

filter what if I want to just filter by

the east

region if I press okay it only shows the

states that make up the east region here

and my data as well we can use filters

together just like with a table to drill

down to find what we're looking

for let's take a look at another filter

while what if I want to go ahead and

filter by product name I'll go ahead and

drag the product name to the filters box

here now that I have the product name

here we can see that we have a filter at

the very top of our Excel document and

now I can go ahead and filter by product

have several products I'll go for just

the first three so I'll

select the first three here and if I

press okay we can see all the numbers

have been updated so for those

particular items total of

5916 so that is using the filter box

here I'll go ahead and clear that

filter we can see the filter is active

right

here and we can also clear it from here

I'll go ahead and make sure that

everything is selected I'll press okay

everything is back to

normal so those are just some different

ways that we can filter

our pivot table let's take a look at the

third filter which is a slicer let's go

ahead and use the slicer to filter our

data I'll go ahead and click on insert

slicer

here now notice we have a slicer for

each of our Fields right one for

City product name so forth and so on

let's go ahead and insert just a few we

have a lot of fields again we'd want to

keep it simple I'll go ahead and insert

a slicer for the

state I'll also insert one for the the

region and one for the salesperson I'll

go ahead and press okay here are my

three slicers I can resize

them so I can see all the

values so I'll just resize them so I

can see the bare minimum here now our

slicers they come with a contextual tab

so we can change the

colors we can also change the amount of

columns that's being

displayed I'll just just give them each

a different

color so there we go now if I wanted to

filter by a region if I just wanted to

answer a question based on the East if I

click on the East button here on my

slicer it's only

showing for the

East I'll go ahead and clear that if I

want to get sales revenue based on a

sales rep let's say Michael there

Michael had some sales in

Milwaukee

$917 what about by state I'll go for

California here's California here as

well if you want to select multiple you

can hold down the control key and select

multiple so I'll go ahead and select

[Music]

Colorado Florida New

York

Oregon and there we can see that that

works as

well so those are the slicers my

favorite way to filter data either on a

table or a pivot table as

well just remember to clear your slicers

before you move on click on that clear

filter icon in the top right of your

slicer that will clear the slicers for

you and put your pivot table back to

normal it's time to create a visual in

the form of a chart based on the data

that's being displayed on our pivot

table I'm going to go ahead and delete a

few of these slicers here so I'll just

click on it and press the delete key I

just want to see my region slicer I'll

move this over here and I'll go ahead

and click on pivot table analyze and I

want to go ahead and insert a pivot

chart now what's really cool about the

pivot chart is it's dynamically linked

to my pivot table so whatever is being

displayed on my pivot table is going to

be dynamically being displayed on my

pivot chart even if I were to remove

some of these fields move them around

they will be displayed here as well I'll

click on my Pivot

chart and here are the different types

of charts well I'll stick to this

clustered column chart here and I'll

press

okay now here is

my Pivot chart I'm going to go ahead and

close the fields over here so I can see

my chart going to make it a little

bigger I'll give it the

the dark style here make it a little

easier to see right so here's my pivot

table here's my Pivot chart and here is

my slicer now I can filter from the

table from the slicer or the pivot chart

so for the pivot chart if I want to go

ahead and filter let's say by product

name and I'll just choose a few

here and press okay

notice my Pivot chart has updated and

also my pivot table has updated as well

so any change you make to either of

these objects they will update if I

click on the east region button on my

slicer we can see that everything has

been updated here as well I'll go ahead

and clear the

filter from my slicer I'll also go ahead

and clear the filter from my Pivot chart

go ahead and select all items

and there we go now I'll go ahead and

turn back on the field list

here and I'm just going to make a few

changes so I do want the revenue um but

instead of the city and the rows I'll go

ahead and

remove the

filter I'll remove the region I'll move

the city as well so right now we're just

getting the grand total what about grand

total by let's see what have we not used

it well what about

bu let's

say shipper name I'll put the shipper

name into the rows right and here's a

basic pivot chart so we can see that

Christina Lee most of the revenue went

through Christina

$159 And1 cents if I want to go ahead

and add the region here in the columns

let's see what this will look like so it

does make the chart a little harder to

read but we do have the details there as

well so we're good to go I'll put the

product uh I'll put the category The

Columns there we go right so just like

with the recommended chart we can change

the chart

type to display another one maybe I want

a line or a pie chart I can go ahead and

do that if I want to here as well so

that is how we insert a pivot chart to

display

our pivot table

data I'll go ahead and simplify this

I'll put this back to my clustered

[Music]

column make it

3D and I'll just go ahead and do a

simple instead

of I'll move the categories

here I'll move the shipper name and I

just want the state in

here there we go so nice simple pivot

chart just as a recap we learned data

analysis some of the basics converting

list into tables for analysis a lot of

table tools are available to us that

helps us to efficiently analyze our data

we're able to summarize data with

logical functions the IF function

average if and SU

if we're able to convert a list into a

table and summarize the data with a

pivot table we're able to filter the

table move Fields display different

values using the sum function or the

count function as well and then we're

able to go ahead and display data with a

pivot chart so just giving a visual

representation of our data as well

congratulations on completing the course

and I look forward to seeing all of you

in a future learn it

course welcome to excel pivot tables

you're learning with Mo Jones IT

professional and educator in this course

we'll learn about the highly sought

after tool for data analysis which is

pivot table in this course we'll explore

kind of the first step just preparing

data for pivot table analysis what do we

need to do to our data before we convert

into a pivot

table also take a look at inserting and

managing a pivot table now managing

means a lot means constructing your

pivot table pivoting the table querying

the

table we'll go ahead and insert and

manage a pivot chart from our pivot

table as well so we'll take it one step

further and create a visual and then

we'll take a look at how we can connect

different data sources by using the

popular X lookup function so go ahead

and open up the practice file for this

course and come right back and we'll

Dive Right

In welcome back so I did open up my

practice file here and we're taking a

look at a data set so here's just some

company payroll here we have some

headers we have the employee number the

employee we have several divisions

California New Jersey Georgia and

Washington we have a few departments as

well sales

development staff and research we have

the date of hire for each employee the

amount of hours that they're logging per

pay period their hourly rate and a

formula over here to calculate the gross

pay we also have a status column here

indicating whether they're full-time or

part-time by using the IF function so if

I were to present this data to you and

ask you to present this data in about 30

minutes or so to our team well naturally

you would first kind of take a look at

the data set it's always a good idea to

do that we can see that we have 94

records

here and we need to anticipate some

questions that may be asked so some of

the questions that may be asked is well

what is the sum of the gross pay what's

the gross pay by Department maybe gross

pay by division and something like what

is the average hourly rate well as you

can see there's a lot of other questions

that we can ask here and so if we were

to try and answer these questions what

are some of the things that we can do

well we might use some

sorting we may do some

filtering we may need to use some

functions right like the sum function

the average function we may need to have

some basic calculations in here as well

that we can use so there's a lot of

different things that we would need to

do here we would need to go ahead and

just kind of move things

around and kind of reshape our data here

as well so for the sum of gross pay we

could you know enter the sum function

and then go ahead and grab all the gross

pay but think about this this is not

something that we want to be doing while

we're presenting this data we want this

data to be you know readily available

with just a simple click of a button we

don't want to have to go through the

process of writing formulas and that's

exactly what the pivot table will do for

us the pivot table is going to give us a

nice condensed table with all the

information that we need just a simple

table and it's going to condense this

large set of data into a nice compact

list with some

totals on the bottom and some totals

over here on the right as well well

let's take a look at a fully functional

pivot table and let's see what we can

gather from this

here so here's a fully functional pivot

table this is based on our data set and

as you can see the pivot table is in a

condensed form and if this is your first

time looking at a pivot table this may

be overwhelming so I'll go ahead and

annotate some of the things here we can

see that we have some totals down here

on the

bottom so this is the total for

California

New Jersey Georgia and

Washington over here's the grand total

so this is the sum of all of the gross

pay for all of our divisions all of our

departments there as well so if I were

to ask you well what's the sum of the

gross pay for Washington we can say the

sum of the gross pay for Washington is

18,154

52 well what about something else what

if we wanted to grab the gross pay for a

department such as the development

department well then we'll take a look

at the column over here to the right and

we can see that this is the

development sum of gross pay for the

development team so we can see it's kind

of three-dimensional here we're getting

a lot of nice information in here as

well so we can already answer some

questions sum of gross pay gross pay by

uh division gross pay by Department as

well now if I wanted to to answer some

deeper questions here we can see that

the total gross pay uh the highest gross

pay is attributed to the sales

department so now what I can do is I can

go ahead and expand the sales department

and now I can get a breakdown of

full-time staff inside of the sales team

15 46590 as opposed to part-time

10,962 81 so this is the pivot table

condenses all the information for us

notice we don't see any formulas in the

formula

bar so everything is happening in the

background for us we can see it has a

nice layout it's visually appealing it's

more efficient to work with and if I

wanted to just kind of move things

around I can simply just if I just want

to focus on maybe

the gross pay by Department I can do

that now I can have a nice condensed

view just to focus on what I want to use

here as well so think about it go ahead

and write it down what do you consider

to be a major benefit of working with a

pivot table take a few minutes write

down your answers come right back and

we'll actually insert our pivot

table welcome back so let's go ahead

and get this table or this data rather

ready for a pivot table so the question

we want to ask is is this data ready for

a pivot table so we are working with the

list so we do have our 94 records here

we go down to row 98 and we can see that

this is sorted by division so California

and then Georgia and then New Jersey and

then Washington here as well so the

first thing to ask is remember when

we're working with a list we want to

make sure that we have a couple of items

here we want to make sure that we have

meaningful and unique

headers we also want to make sure that

we have

unique labels so in this case our labels

are the employee ID number and they're

unique and they're unique to the entire

record and then we have complete records

now remember a record because this is a

column based table we have our record is

a full row of data here as well so we

already have a unique and meaningful

headers they describe the data below low

so for example the gross pay we expect

to see gross pays and no dates or text

for the employee we have the names in

here as well so we're good to go right

the next thing we do have unique labels

so we can check off these first two

items here as well the next one we want

to make sure that we have complete

records and we have no blanks now this

is a really big deal because when we

create a pivot table when you have

Blanks on your data set they will

actually show up as Blanks on your pivot

table it will say blank plus it will be

incomplete so for

example what I do is I use the count

blank function this is a good first step

this tells me how many blanks I have in

my data set I have the function right

here it's a simple function we tell

Excel to count the amount of blanks from

the range A4 to

j98 so if I were working with this data

set and I deleted a couple of cells here

it tells me right away that I have two

blank cells right so right here we can

see it's telling us that so that's a

good first step to make

sure so once I see that I can simply

just go ahead and rectify those blanks

and as I Rectify

them it goes away so we want to make

sure that we have zero blank cells in

our data set the other thing to do is

you want to check formatting especially

dates now if we take a look at the date

of higher field we can see that these

look like dates to us but if you take a

look at the numbers command group this

tells us that this is not using the date

format this is a custom format now this

is a big deal because a lot of our data

is time is you know it's based on time

or dependent on time quarterly reports

monthly reports so forth and so on if

you do not have a properly formatted

date field you will not get the benefits

of the date tools that are available in

your pivot table so I'm going to go

ahead and fix these dates I'll go ahead

and highlight all of

them and I'll just come back up to the

numbers command group here and I'll use

the short date

format and we are good to go right

notice one of the things that we'll kind

of discuss this later in the course but

one of the things that we can always ask

about our data is how we can how can we

improve the data so that we have more

reporting features notice in column J

that I've included a simple IF function

to determine whether or not the employee

is a full-time or part-time employee so

if they're working less than or in this

case greater than or equal to 40 than

they're full-time less than or equal to

then they are part-time so this is a

great way to just improve the reporting

because now we have a status field that

we can go ahead

and run data analysis on as well so

those are just some simple cleanup

techniques we want to make sure that

this checklist is

complete now that we have a nice list

here let's go ahead and insert our pivot

table there's several ways to insert a

pivot table we'll explore some of the

other other options as we go through the

course but for now let's go ahead and do

this the traditional way we need to

click on the insert Tab and when we're

on the insert tab we can see that to the

far left in the tables command group we

have pivot table we also have

recommended pivot tables we're going to

go ahead and click on pivot table here

now the first step is just to select the

data that you want to add to your pivot

table I don't have to select the entire

data set if I just click anywhere inside

in this case I'm just clicking on Cell

C6 Excel is going to automatically

select all the data until it gets to a

blank

column or a blank row so it's

automatically going to select all of

this data for us as a matter of fact it

uses the regional command so if I press

control a notice how it selects all of

my data until it gets to a blank row or

a

blank column as

well so I'll go ahead and click anywhere

inside of my data set I'll click back on

C6 here and on the insert command I'll

click on pivot

table when I click on pivot table and

presented with this dialogue box it's

just asking me where is the data that I

want to add I can already see it's

already selected all of my data for me

and I do want to place this on a new

worksheet sheet you can place your pivot

table on an existing sheet for example

one of these sheets down here the only

problem is when your pivot table touches

other data like text you get some errors

as well so most of the time we want to

go ahead and insert our pivot table on a

new worksheet I'll go ahead and do that

I'll go ahead and press okay and then

we'll take a look at the components of a

pivot

table so I just inserted my pivot table

here and I'm just going to reset this

here there we go so immediately we see

two things on the far left here is our

actual pivot table it's blank right now

we don't have anything to work with so

that's the first thing we're getting a

little Q here it says to build a report

choose fields from the pivot table field

list well that's over here so that's the

second component we have our pivot table

Fields right here and then finally down

here at the bottom we have four areas

for filtering our data for displaying a

field in the rows for displaying a field

in the

columns and for displaying an overall

value which is the the big ticket item

here as

well we also have two contextual tabs so

we have one that's called pivot table

analyze

and we have one that's called design so

right now I'm on the pivot table

analyze and from here we can do things

like insert a pivot

chart we can refresh the table we can

change it to connect it to another data

source we can insert a slicer a timeline

right here we can rename the pivot table

as well we have access to the pivot

table options so this pivot table

analyze is going to give us all the

tools that we need to manage this pivot

table we also have a design tab which is

right here and just like with tables and

charts we have styles that are available

and then there's other layouts that we

can choose from and then there's other

kind of sub features that we can turn

off or on we'll visit these later on in

the course but for now let's just go

ahead and rename our pivot table

so to do that I can either click on

pivot table analyze and I can go ahead

and type the name right here and that

box right clicking on the pivot table

gives you access to a lot of these

commands that are available under the

pivot table analyze as well let's go

ahead and rename the table I'll click

right in here and I'll just call this

payroll press enter and there we go so

it is called

payroll now over here on the far right

there's two things I like to do before I

start building my pivot table notice

that the fields are not an alphabetical

order they appear in the order of the

data set so what I want to do is I want

to go ahead and sort them A to Z so if I

click on the gear icon here there's

actually two things I want to do here I

want to go ahead and sort my Fields A to

Z I also want to change the view to side

by side right now it's using stacked I

don't want to do that so I'll go ahead

and sort A to Z

first there we go but as you can see I

can't really see all of my Fields I have

to scroll through so I'm going to change

the view to side by side it's going

going to give me a taller window where I

can see all of my feels and over here I

can see my four area boxes as well

so I'll change this to side by side and

then I'll be ready to start building my

pivot table so here's side by

side so this makes it more efficient if

I have a lot of feels I can see them

here and I still have access to my areas

here as well so it's really up to you go

ahead and toggle back and forth either

stacked or side by side I'll stick with

side by side

go ahead and play around with both and

then come right

back welcome back now that we have the

basics set up here let's go ahead and

start building our pivot table now to

build our pivot table it's a matter of

taking a particular field and dragging

them to one of these actual boxes here

so I like to start with the values box

first and so what's the big ticket item

here that we're focused on well it's the

gross pay so I'll go ahead and drag the

gross pay right into this box or I can

simply check it off so either one will

allow me to do that I'll do both I'll go

ahead and drag the gross pay to the

values

box and as you can see let me zoom in

just a little bit here we can see we

already have our answer so the sum of

GRA gross pay is

8436 right and some decimals there as

well so it automatically runs the

calculation for us it's already

summarize that column to get rid of that

I can either uncheck it or drag it back

this time I'll drag it

away and our pivot tables back to

normal so I'll go ahead and click on the

check box

here and because it's a numeric value it

automatically places it into the values

box as well so that gives us our answer

well we do want to play around with the

gross pay here but let's take a look at

something else What if I click on the

employee number well let's see what the

pivot table gives us

here so it shows all the employee

numbers and the rows doesn't really help

me I'm going to go ahead and drag that

to the

values and what it does is it inserts

the count function for

us because this is a alpha numeric

character it's giving us the count of

employee numbers well why would we want

to do that well remember the employee

numbers are unique so we have 94 unique

employee numbers because it's using the

count function here what I can do now is

figure out how many employees I have by

division so if I click on division

here look at what it does so it places

the division into the rows for me and

now I get some nice information so I

have 15 employees in California 27 in

Georgia

30 in New Jersey and 22 in Washington

right what about my department if I

click on Department

here it also places the department in

the rows well I'll put the department in

the columns

instead so look at how many questions we

can answer just by having the employee

number in the values Department in the

columns and Division in the rows I can

answer a very specific question such as

how many employees do we have on our

staff team in California three right

here so we can isolate the data as you

can see the pivot table is taking care

of all that for us we don't need to

enter any formulas or any logical

functions like that as

well so I'll go ahead and reset the

table I'll go ahead and clear this out

here right I'll go ahead and put the

gross pay back in here

again right so by default it gives me

the sum of the gross pay but let's take

a look and see how many other ways we

can express the gross pay I'll click on

the drop- down

here and here I have what's called value

field

settings I'll click on here here's my

value field settings dialogue and now

what I can do is I can change it I can

suiz the value by

count and also by average well let's try

the count and let's see what happens so

don't summarize that just count it for

me I'll press okay and look at that we

also get 94 because we have

94 records to work with so this is

another way to kind of give us

the number of employees by division

right so we can always change that value

the way that it's being

expressed let's try one more I'll go

ahead and grab the average

here right and so the average gross pay

is

89687 what about average gross pay by

division well the average gross pay in

California

853 average gross pay in Georgia

936 so those are just some of the basics

of how we can build a table and how we

can change the way that the value is

being displayed for us as well go ahead

and play around with the different

values here click on the value field

settings go ahead and use these

different summaries here and familiarize

yourself with them and come right

back welcome back so we can already see

how intuitive our pivot table is just by

simply clicking on a field we can help

the pivot table built for us as we

interact with our Fields here as well

well let's go ahead and answer some of

those specific questions that we asked

earlier what is the sum of gross pay

gross pay by Department also gross pay

by division so I'll go ahead and drag

the gross pay back to the values box and

we'll focus on the sum of the gross pay

here as well right so right now it's

giving us the answer but I don't really

like like the way this looks I don't

have the currency or accounting format

in here so let's go ahead and apply the

accounting format I'll click on the drop

down

here and I'll click on value field

settings we were here before this time

what I want to do is I want to go ahead

and click on the number format down here

on the bottom left it's going to give me

the format options that I simply just

want to choose the accounting format so

I'll click on number format here

and here is the accounting so we can

either choose accounting or currency I

like to use accounting because it kind

of spaces out the dollar sign and the

number we can always change it if we

want to so I'll choose accounting and I

will press

okay and I can also press okay as well

notice right here if we want to change

the name that's being displayed right

here we can type in our custom name

right here so instead of saying sum of

gross pay we can say something else here

as well right so maybe what I'll say

here

is maybe I'll say pay per

period then I'll go ahead and press

okay so we can see the name has changed

more meaningful for me at least and now

we can read the number more correctly so

as we continue to build the table all of

our cells with numbers will have the

accounting format

okay so we have the paper period here

well now we're concerned about the

division so let's go ahead and click on

the division let's put the division into

the

rows and there we get a

simple condensed list here we can see

the pay per period for each of our

divisions here we can see New Jersey has

the highest gross

pay well what if I put that into the

columns so I can see it's in the columns

here looks a little different

so placing it in the columns gives us a

different view I'll go ahead and leave

it there and I'll go ahead and add the

department to the

rows so now that I have the division in

the columns and the department in the

rows this is a nice view and we can

answer a lot of questions here so what

is our move over

here so what is our grand

total 85 4

30661 here's our total for Washington

New Jersey Georgia

California and then over here we have

our totals for the development

department research sales and staff so

look at all these different questions

that we can answer here and all we did

so far was we place the department in

the rows division in the columns and the

gross pay into the values as well now

now I can switch these if I want to so

for example what if I want the

department to be in the columns and the

division to be in the rows I can switch

them and the pivot table will just make

the adjustments for me so I'll go ahead

and move the division to the

rows and I'll drag the D Department to

the column here as well so this is just

another view we still get our totals but

now our subtotals down here on the

bottom

there are for our

departments and the totals on the right

column four our divisions so it's just

another way to display the data so it's

your pivot table you're going to go

ahead and

display the information in any form that

you want now one thing that we can do as

well is we can put the division and the

department in the rows so if I drag the

department here into the rows

now this is pretty cool now because we

have two fields in one of these areas we

have a hierarchy so because division is

on the top here division is going to be

a level one item and my department is

going to be a level two item and we see

that over here as well we can see that

we have our California is the level one

item and then we have the level two

items down here so development research

and sales so it's

indented so it kind of just organizes

the data for us so we did not have to do

that the pivot table took care of that

for

us last thing to mention here we have

different buttons so you'll notice that

we have a button here that allows us to

either expand or collapse this list so

if I click here I can go ahead and

collapse all of

them and this gives us a simple View and

now this is on demand so if someone says

well why is New Jersey so much we can

expand New Jersey and we can show the

breakdown by Department here as well we

can also right click on any of these

buttons and when you right click you'll

notice that we can towards the middle of

that menu we can either collapse the

entire field or we can expand the entire

field I'll go ahead and expand the

entire field and then I'll collapse it

so there we go

and then I'll go ahead and collapse

everything as

well and there we go so just different

ways that we can work with our pivot

table notice when you click away from

the pivot table the pivot table

feels goes

away

also our pivot table analyze and our

design goes away so it is contextual we

have to be interacting with the pivot

table to get both all three create these

components here as well so go ahead and

play around with your pivot table just

move things around and go ahead and set

it up the way that you like it and come

right

back now that we have the basics covered

for constructing a pivot table the next

thing that we want to do is we want to

go ahead and filter our pivot table so

let's talk about filtering there's

several ways that we can filter a pivot

table we'll notice that we have

two filters already built in we have a

row label filter and we also have a

column label filter now depending on

what we have in the columns because we

have the department in the columns I

should be able to click on that button

and filter what is being displayed by

department now because I have the

division here in the rows for my row

label filter I should be able to filter

by division so let's go ahead and answer

a question well maybe I want to just

figure out well what is the sum of the

gross pay

for California and

Washington so I'll simply just select

California and Washington from the list

here and I'll press

okay notice how all of the numbers have

updated my grand total represents only

the grand total is for California and

Washington so all of these numbers here

so all 15 of these

numbers have been updated so in a

fraction of a second the pivot

table wrote These formulas for us and

gave us the answer now there's a lot

that's happening in the background for

the pivot table we're not privy to the

professional that wrote this tool or the

professionals that created this tool

there's a lot that's happening in the

background now if you think about it

there's a lot of logical functions

that's happening here as well for

example if the division is California

and the department is development

summarize the data from the gross pay

column so a lot of logical functions Su

if

count and it will take us a very long

time to construct a table like this but

the pivot table is giving us instant

analysis for our data as well let's go

ahead and take a look at the column

label filter so the column label filter

we have our departments because we have

our departments in the column here so if

I just want to focus on the research and

the sales

team I can simply do that and I can go

ahead and press okay and now we're able

to drill down to find exactly what we're

looking for research and sales

department and California and Washington

and there's our data so that's one way

that that we can filter now remember

when we're running our filters it's very

important to clear our filters before we

move on otherwise this happens a lot the

information will be incorrect so I can

clear the filter right from here we can

see that the filter is active so once

I'm here I can click on it and I can

choose the clear

filter option

here so here's clear filter from

Department you'll also notice the filter

is available or active on your pivot

table Fields as well so for my Division

I can come over here and I can clear it

from here as well this resets my table

everything is back to normal let's go

ahead and take a look at another way

that we can filter and we have the

filters box well let's go ahead and

filter by status so I'll grab the status

here and I'll drop it in here I want to

be able to filter simply by part-time or

full-time staff so I'll go ahead and

drag the status into the filters box we

can see right away that it places that

filter box right here on the top left so

there is our

filter there's only two options if I

click on there it's a simple filter we

don't have all the bells and whistles

like the other ones but I can go ahead

and filter either for full-time or

part-time staff if I want to so I'll go

ahead and click on there and I'll go

ahead and filter for

part-time let's take a look at that

filter so here we have part-time so I'll

click on part-time here I can also

search up here as well I'll go ahead and

press okay and now all of our

information is being displayed based on

our part- timers which is pretty cool so

we can see up here it's part time and

notice how all the numbers have changed

well notice that we have some cells here

that does not have any data in here for

example we don't have any part-time

staff in

Georgia we also don't have any part-time

staff in Washington that's why we don't

see any numbers these are not

necessarily blank data we just don't

have any values there so it's pretty

cool we can see the pivot table is very

very intuitive so those are two quick

ways that we can filter we use the row

or column label filter we can also use

the built-in filter box here as well but

my favorite way to filter is by using a

slicer now we'll click on pivot table

analyze and once we click there we're

going to see over in the filter command

group we have a couple of options here

we're going to be using the slicer so

let's go ahead and insert our slicer and

then we'll go ahead and move on here as

well

so we took a look at some built-in

filters let's go ahead and insert our

slicer here so again on the pivot table

analyze tab let's go ahead and insert

our

slicer now the slicer is another way to

filter but we have a little more control

and we can filter

our data set more efficiently notice

that we have a slicer for each of our

fields

so we have one for status we have one

for the gross pay hours worked so forth

and so on let's go ahead and insert a

few slicers here we can insert all of

them if we wanted to but not all of them

are a good candidate I'll go ahead and

insert one for the Department one for

the division I'll also insert one for

the

status and let's insert one for the

gross pay as well right I'll go ahead

and press okay

so here are my four slicers now I can

move them around and I can resize them

so for my department slicer I'll just

make that a little smaller here here's

my status I only have two buttons on

there so I'll just make

this short or shorten

that and then we have one for the gross

pay we can already see the gross pay is

going to be problematic because we have

so many unique values

that it's going to be very difficult to

use this and then we have division so

I'll go ahead and move this over

here and I'll go ahead and shorten that

as well so what exactly do these slicers

do well I'm going to go ahead and click

on

the development button on my department

slicer and we can see what it does I

clicked on the development button on my

slicer and you can see that it is

basically filtering my pivot table to

only display the information from my

Development

Department pretty pretty cool now what

if I wanted to focus on the development

department in

California so as we can see the slicers

by clicking on the buttons we can

control what's being displayed on our

pivot table so we're basically filtering

our pivot table but we're just doing it

in a cooler way we have a little

dashboard over here that we useing to

control what's being displayed well

let's go ahead and clear all of our

slicers so on the top right you'll

notice that we have that clear filter

icon on all of our slicers here so let's

go ahead and make sure that those are

all cleared now we good to go quick tip

here if you want to clear all the

filters that are running on your pivot

table just click on your pivot table and

instead of kind of unchecking them

individually if you click on the data

tab you'll notice that this clear filter

icon is available this will basically

clear all the filters that are running

on your data here as well so I'll click

clear and notice how it resets my table

I'm back to the total of

84306

61 okay but this is about slicers so

let's go ahead now if I wanted to go

ahead and run a slicer for the gross pay

well

there's too many values here and so this

is not really a good candidate so not

all slicers will be a good candidate to

use it will work if I click here it

shows me my one record there for

Washington but doesn't really help me

too much so I'll go ahead and delete the

slicer I'll click on it and now I'll

just press the delete

key there we go so now I have three

slicers to work with now when it comes

to the slicers

the slicers have their

own contextual tab so if I click on my

department slicer here and I locate the

slicer tab well I can go ahead and give

it a slicer style which I will do and I

can also change how many

columns are being used for my slicer

here so I'll change this to two columns

and I'll go ahead and maybe choose this

uh orange style here as

well right so I'll change it to two

columns first

now I can see I have a two column

orientation so now it gives me a little

more room I can make it a little

wider and now I can make it

shorter now I can move up my division

here as well I'll also give that a quick

color that light

orange for my division I'll do the same

thing I'll make this two columns make it

a little wider as well make it the same

width there we go and now I can just

kind of stack them like this

now we good to

go I'll make my division another color

and my status another color so here are

my three slicers and I can use these to

basically control what's being displayed

on my pivot table as well now there's

another option here on the top right of

your slicer we'll see a like a little

checklist here that is the multi- select

tool and if you hover over it tells you

this is the multi select you can

press the alt plus s key to toggle that

well what this allows me to do is to

select more than one button at a time

right now I can only select one button

at a time so if I wanted to select

multiple I can either hold down the

control

key and select multiple like

that or if I just toggle the multi

select tool

here I'll put that on for both of these

now I can go ahead and select sales and

research and I can go ahead and select

California and Washington so that is the

multi- select tool so you can toggle

that on if you want to be able to filter

your data here as

well I'll go ahead and clear my filters

I'll turn off the multi- select tool and

I'll just answer a question that what is

the sum of gross pay for the part- timr

in New Jersey on the development

department and there we go so very very

specific so those are our slicers always

be ready to clear your filters from your

slicers I'll go ahead and remove a few

of these for now I just want my

division and my status so go ahead and

play around with your with your slicers

and come right

back welcome back now that we know how

to filter let's go ahead and take it a

step further let's go ahead and do a few

things what we want to do we're going to

add data to our original data source and

once we're done we're going to

insert a pivot

chart so let's go ahead and add some

data here let's go back to our data

cleanup worksheet tab now let's go ahead

and add a record on row 99 so if you

want to go ahead and add yourself there

there that's perfectly fine so I'll go

ahead and put in another employee number

here I'll just go with a

a

40 and I'll go ahead

and enter my name here so this is going

to be

Jones and I want to introduce another

Division and another department because

I want to be able to catch the change on

the pivot table so I'll go ahead and put

uh

let's see I'll put Texas in

here and I'll

put

tech for that as well for the higher

date I'll just put uh I'll go with

January first

2024

benefits not really running

any analysis on benefits but I'll put it

here on part-time

all right and there we go so here's my

record in row 99 but specifically I

introduced a another Division and

another department so I want to go ahead

and go back to my pivot table and let's

see what happens here so I'll go back to

my sheet one and I need to rename this

as well so I'll just rename this as

payroll just like my pivot table so as

we can see when we come back to our

pivot table we're not seeing the new

Division and or the new Department as

well so let's take a look and see the

behavior here I'll right click on the

pivot table and I'll click on pivot

table

options now we'll discuss some of these

options a little later but for right now

if I click on data notice that we have

the option to refresh the data when

opening the file right so we can do that

so it's not going to just update

automatically we need to do something

special here so to do this I'll click on

pivot table analyze and if I click on

refresh that doesn't do

anything right because right now this

pivot table is based on a specific range

so what I need to do is click on change

data source so if I click on change data

source it brings me back to the original

data source and we can see it's locked

onto the range A4 through

j98 well I just added another row for 99

so I can either highlight the entire

data set or I can just come here and

enter a nine here as well now this is

pretty cool as well because maybe I

don't want to display all this

information what if I just want to

display the data down to row 25 well my

pivot table will re

analyze and recalculate everything here

as well but in this case I just want to

include that new record that I added

here so I'll go ahead and press

okay and there you go so we can see that

we have our new department and we have

our new division here as well our number

is updated to reflect the total gross

pay based on the change take a look at

my slicer here we can see that my

division slicer has updated to show the

new division here as well well let's try

one more thing I'll go back to my data

clean up here this time what I'll do is

I'll go ahead and insert so I'll just

insert a row I'll insert a record as

opposed to adding a new record to the

end so I'll just insert a row

here and I'll put in another so maybe

I'll say 8 it 55 I'll add a friend here

and I'll give for Tech as well higher

date same

thing and I'll fill out the rest of this

information okay so let's go back to

our pivot table

here and let's see what happens so this

time if I right click and refresh let's

see if my number changes from 8496 to

something something

else and it does we can see that it has

been updated here here is the new

division for

Florida and here is the new update here

as well so the thing to learn here is

that when you're working from a list

which we

are so this is a list this is not a

table the pivot table is locking onto a

specific range however if I were to

insert

a row or a column the range will expand

for me it's automatically going to

expand right so I can go ahead and do

that even take a look at my count blank

function here it is now expanded to 99

as well so if you insert a record you

simply need to refresh the table but if

you're adding a new record to the end of

your data set you need to click on the

pivot table analyze Tab and change the

data source and just select all the data

that you want to add as well right so go

ahead and add a new

record and then go ahead and change the

data source to include that new row and

when you're done go ahead and insert a

record anywhere in the middle and

refresh your table just confirm that it

works for you and come right

back now that we were able to add some

data let's go ahead and insert a pivot

chart chart to insert a pivot chart we

can simply click on our pivot table

click on the pivot table analyze Tab and

then to the far right under Tools we'll

see our pivot chart now the great thing

about the pivot chart is is directly

tied to our pivot table so we simply

need to choose the type of chart that we

want to be displayed and we'll take a

look and see as we change the data

that's being displayed on our pivot

table our chart will d dynamically

recreate itself so I'll insert my Pivot

chart

here now here is our insert chart

dialogue so I have different charts that

I can use LINE charts Pi Bar area so

forth and so on some will not be

available I'll keep it simple I'll just

stick to the clustered column one here

and let's see maybe I'll go for a little

3D clustered column

go for this one I'll go ahead and press

okay I'll close the pivot chart fields

for now so I can see my chart here so

here is my Pivot chart so we can see

it's displaying whatever is currently on

our pivot table we get some really nice

filters here so what if I want to filter

by let me move my slicers down here I

want to show you what happens when we

filter either using the chart or the

pivot table or the slicers here so we

have three filters for our chart we have

status full-time part-time we have our

departments and we have our division

well I want to remove Texas and Florida

from the division so I'll just go ahead

and remove Texas and Florida I'll press

okay right so there we go so we can see

that now because we filtered our chart

take a look at our pivot table our pivot

table has also been filtered take a look

at our slicer so our slicer has also

been filtered so the one thing to note

is that these are all connected so our

pivot tables connected to our

chart is connected to our

slicers so they're all connected to each

other so running a filter on one will

filter the others as well if I click on

the fulltime button here on my

slicer we can see that the information

on my chart has also changed as well as

on my pivot

table so we're good to go now when it

comes to our pivot chart we can move it

and we'll move it in just a little bit

but let's take a look at just some basic

customizations that we can make up here

on the top right we have the chart

elements so we can turn some things off

or on so we can add a chart title if we

want to we can display the data labels

at the top we have the data

table so but what I like to do when it

comes to a pivot chart is keep it simple

just to choose one of the chart styles

that are available so here are the

different styles that we can use here's

style two if we scroll

through here is style

three this style has the data labels at

the top of the

chart now we have different types here

as well here's style number nine nice

Dark

theme and then we have 10 and then we

also have 11 I'll go with

11 here as well and we're good to

go so now what I can do is I can go

ahead and move this chart but before I

do that let's go ahead and just kind of

reconstruct our table here and let's see

what happens to our chart well first

I'll clear all of my filters

here and let's reconstruct this so what

if I don't want to show my let's

say go back to my pivot table what if I

remove the department from The

Columns so now as you can see I'm only

showing the payer period for my

divisions and we can see the chart is

reflecting that current cury here as

well if I were to change this to

department remove this

here and there we go so now here's the

sum of gross pay by department so as I

move things around here I'll get some

different answers as well for the pay

period what if I want the hours the

hours

worked per department and per division

well there we go so we can see we don't

need to do anything special to the Chart

it's dynamically linked to our pivot

table I'm going to move my chart here so

if you click on pivot chart analyze we

have a lot of things that we can use

here here's the move chart

command on the pivot chart analyze tab

let's go ahead and move our chart we can

also

rightclick and use the move chart

command here but let's move the chart to

a new

sheet and then we'll press

okay so here's the chart on that new

sheet all by itself now this is still

linked to my pivot table right so right

now I'll remove that department I don't

want to show the

tech I'll press okay so that looks a lot

better right so I am

filtering to remove the tech

Department I'll go back to payroll

here and you can

see that we're still linked to our pivot

table it's only

showing development research sales and

staff but not Tech so even though we

move our pivot chart it's still

connected to our data set which is good

because typically you want your pivot

chart to be on another sheet you don't

want it to be on the same sheet as as

your pivot table well that's it so go

ahead and play around with your go ahead

and move your pivot chart go ahead and

turn some of these features on or off go

ahead and choose a

style and come right

back welcome back so we've covered all

the basics with regards to a pivot table

so you now have all the tools the

essentials that you need to build a

pivot table well

let's take a look at the X lookup

function for our project for module one

here or part one what we want to do is

on the joining data sets worksheet here

we have three different data sets we

have a

products list we have a region

list so sales rep and region and then we

also have an order form over here as

well but we can see that the order form

is missing some information such as what

is the

region where the transaction took place

what is the price of the actual product

so rather than doing this manually we

want to write an xlookup statement that

will automatically populate the region

based on the sales rep

right so for example Dan

Peterson is in the Midwest Division so

we want to be able to ask

Excel to put Midwest right in here for

us the sunshine

product over here is

$19.95 we want to be able to put that

price over here manually and Excel will

fill out all the prices based on the

product here as well so in order to do

that we're going to use x lookup but

before we do that we need to understand

how X lookup works so here here's a

small product list over here we have the

product ID the product name the quantity

the amount that's in stock and then we

have the actual price of the product we

want to create a little kiosk here so

that if we put in a product ID number

let's say

105 what it's going to do it's going to

go ahead and take this

ID and it's going to go ahead and go

down this column the product ID column

once it finds is a match that

1050 it's going to head on over to the

right find the product

name and place

that over here for

us it's also going to find the price

from the price column and drop in the

price over here as well so basically

we'll be able to enter a product ID and

have Excel automatically populate column

H and I with the corresponding product

name

and the corresponding product price so

let's go ahead and do this together so

I'll click on Cell H5 and I will insert

my X lookup we'll use the function

arguments box for this so I'll click on

the formulas tab

here and on the formalist tab we're

going to click on where it says insert

function we'll get the dialog box and we

will search for the X lookup function so

we'll click here

I'll type X

lookup I'll press

go here's X lookup I'll go ahead and

press okay so here's my function

arguments box for X lookup and it tells

me the name of the function tells me

what it does so it searches a

range for a match and Returns the

corresponding items from a second range

or array well that's exactly what we

want to do we have arguments here the

lookup value is the value to search for

the lookup array is the array arranged

to search the return array is the array

arranged to return I already have this

kind of annotated for us here as well so

basically in my words the lookup value

is the unique ID so the unique ID is

basically whatever value we enter in

this cell that is the lookup

value the unique ID column so the lookup

array is going to be the unque ID column

so this is where basically column A

where we can find our

IDs the return array is going to be the

product name

column and then we're good to go these

other ones down here if not found these

are optional we don't even need to enter

anything here as well so let's go ahead

and plug this

in so my lookup value I'll point to cell

G5 so whatever value is in G5 that is

the look up notice it's already

recognized that the value 1050 is in

G5 so the next part the lookup array is

where can we

find where can we find those product IDs

well right here column A I'm going to

highlight all of

those from A5 down to a21 now because

this is a table it's giving us the table

name and the table column so table one

one product ID column here's the preview

of the array here are the ID numbers

1050 1051 1052 so this lets us know

we're locked onto the correct

array two down one to

go which column do we want to return

well we want to return the product name

so I'll highlight

this and that's it we're good to go the

function in the background is doing a

lot of things that we're not privy to we

simply need to point to the lookup

value which again is

G5 we need to point to the lookup

array which is basically all of our

product

IDs and then we need to specify the

return array which in this case is the

product

name and so we are good to

go change the color here

right so pause the screen take a look if

this is your first time using X lookup

it does take a little pausing to kind of

think logically what we're trying to

accomplish here but again the function

does the work for us we can see it's

already giving us our answer it is the

Aspen which is correct so I'll go ahead

and press

okay and there we go there is our Aspen

product if I change the product ID to

1054 and press enter it gives me the

doublers which is

correct well let's go ahead and write

our X lookup statement for the price and

then we'll move on to our final project

for part one here so I'll go ahead and

insert my

function here's X lookup lookup value

same thing it's going to be

G5 the lookup

array the product ID column from table

one the return array the only thing

that's different I want to return the

price so I'll highlight my prices

here it's already giving us our answer

$411.33 for our doublers with ID

1054 I'll go ahead and press okay and

there we go so that is the X lookup

function not as intimidating as it looks

it does the work for us we simply need

to identify the lookup value the lookup

array and the return array I'll go ahead

and test it here I'll put

1066 now we can see this is correct it

gives me the correct record

here on row

21 very good go ahead and write your X

lookup statements go ahead and enter a

few product IDs just to make sure you're

getting the correct answer and come

right

back welcome back now now for our final

project what we want to do is we want to

go ahead and make sure that this order

form has all the information that we

need to create a pivot table to answer

questions such as the sales by region

sales by product sales by sales rep and

maybe even some sales by month or

quarter or year as well so we have this

is very common in the data world to have

your data sets in different places so we

have a product table here we have a

region table here as well and then we

have our order form so this is what we

want to focus on we want to make sure

that we add the region based on the

sales

rep this will allow us to run Regional

reports we need the price of the actual

product so we'll grab the price based on

the product and then we'll enter a

simple calculation in column M for the

sale we'll just multiply the units times

the price and then this will be good to

go now this is a really big data set if

I press control end this goes down to

row 10,000 and3 so this order form has

10,000 rows or 10,000

records I'll press control home to go

back to cell A1 let's go ahead and plug

this in so for the region we're going to

enter X lookup now remember we already

have the information that we need we

have our lookup value we have our lookup

array and we have the return array so

I'll highlight this first here so for

our region this is our Target we want to

return the region here to return the

correct region we're going to use the

sales rep so in this case Isabel cross

we're going to search the sales rep

colum for Isabel cross so that's the

lookup

array and then once we're done what do

we want to return we want to return the

region from the region

column so we already have all that we

need I'll make this a different

color so here is our lookup

value a bell

cross here is

our lookup array

and here is our return

array so we just need to point to those

different areas so let's go ahead and do

that here now I have converted these to

tables for you if you've never created

the table before we'll go through the

process of inserting a table but part

two or module two so right now one of

the benefits of working with a table is

once I enter a formula or a function in

the top row it's automatically going to

to fill

down that formula for me so let's write

our X lookup I'll insert X lookup

here now remember lookup value we're

going to take the sales

rep where can we find the sales reps

over

here I'll just highlight them

manually so two down one to go

the next is what do we want to return we

want to return the corresponding region

from

the table here so I'll go ahead and

highlight all those as

well and once I'm finished I can see

that because I'm working with the table

Excel gives the table name so region

table sales rep column region table

region column here's a preview Alan

Ramos Andrew Meer Midwest West East it's

already telling us that Isabel

cross is from the east region so as long

as you have East there you should be

good to go go ahead and press okay it

should automatically fill out all of the

corresponding regions for us so let's go

ahead and press

okay and there we

go well maybe we're skeptical let's

check Cecilia

Manning is Cecilia Manning from the

south well let's take a look and

see where's Cecilia Manning here's

Cecilia

Manning indeed Cecilia is from the south

so we're good to

go pretty cool now let's go ahead and

grab the price from our product again we

already have the data we need so let's

enter our X lookup

function lookup value is going to be the

product where can we find the products

right over

here A4

to a15 AKA

products table products column what do

we want to return well we want to grab

the

price so product table price

colum and that's it 1995 for our

sunshine and that is correct I'll go

ahead and press

okay there we go everything is all

correct here as well so now we have the

region we have the price we simply need

to enter a formula to calculate the sale

so in this case I'm just going to say

equal and it's going to be K looks like

this is K4 so

K4 times the value that is an M4

so that's what my formula is going to

look like because it's a table it's not

going to say K4 and M4 but we do have

the target cells we want k4s

here and

L4 okay so I'll click on Cell M4 here

and I'll say equal and I'll click on the

units here from cell

K4 and I'll multiply with the asteris

times the

price right so units times the price

I'll go ahead and press enter and there

you go so just like that we have a fully

functional order form now captured all

the data that we need to create a pivot

table so I'm just going to go ahead and

insert a quick pivot table I'll click on

the insert

Tab and maybe this time I'll use a

recommended pivot table

and this one says sum of sales by region

I'll go ahead and grab that one I'll

press

okay here's my pivot table showing

my sales by region as well again we'll

just give this the accounting

format and we're good to go so now we

have sum of sale by region if you want

to add the products in there we we can

go ahead and do

that well maybe not that's Overkill

maybe I'll put the region into

the columns and there we go so that is

how that

works go ahead and complete those tasks

and come back and uh we'll wrap

up that completes module one now we've

learned all the essentials that we need

to build and manage a pivot table module

two will build on our learning and this

module what we'll cover is we'll

summarize a table with a pivot table so

we'll convert a data list into a table

and we will take a look at the tools

available to convert that into a pivot

table as well we'll take a look at some

Advanced pivot table

tools we'll also be able to customize

our pivot table by choosing the design

colors a lot of the Cosmetic aspects of

the pivot table you can even brand your

pivot table if you so

desire we also are going to take a look

at Power pivot so power pivot is a part

of the Microsoft's Power Platform and

this will allow us to connect multiple

data sources and really work with big

data so that's what we'll be

covering once we upload our data into

Power pivot we'll be able to create

what's called measures and or calculated

columns and we'll be able to create a

cohesive pivot table from the data model

so go ahead and open up the module 2

practice file and come right back and

we'll Dive Right

In Here is our practice file for our

second portion here and we have a couple

of worksheet tabs here on the bottom

well we have advanced tools which we'll

be exploring we'll also take a look at

pivot table design and then we'll go

ahead and introduce power pivot and then

we also have some kind of a bonus we'll

take a look at how to perform some data

mining to improve our reporting

capabilities on our very first worksheet

tab here what we want to do we have a

list here so just our Pure creary sales

we have the order ID the sale date we

have the product sales

region we're tracking the cells and the

units as well so right now we have three

cells Representatives we have Pullen

Watson and Bishop we're selling in three

different regions so Central north and

west as

well several products popsicles ice

cream tasty treats frozen yogurt so we

want to keep it simple that's why we

using crey items here so let's go ahead

and convert this this list to a table

and then insert a pivot table so

remember we can convert this list into a

table by pressing control+ t on our

keyboard so that will convert our list

into a table so I'll go ahead and do

that you don't have to highlight the

entire list let's scroll down let's see

how big this list is I'll press control

end this goes down to row 112 so not

that bad I'll press control home to go

back to cell A1 and here we go so if I

just press contrl

T here's my create table dialogue box

and we can see it's already selected all

the data for me A4 to g112 and that's

because I don't have any blank rows or

columns in here my table does have

headers so I'll go ahead and press

okay so here is my table now I know that

this is a table because a lot of things

have happened here if you take a look at

the ribbon we have what's called table

design and the whole reason we're

converting this into a table is because

when you convert your list into a table

you have

more opportunities to manage your data

for example we have table Styles we have

filters that have been turned on we can

also add the total row we can go ahead

and insert a slicer as well we can

remove duplicates and then another big

thing that we can do is we can go ahead

and

summarize this table with a pivot table

so just a quick rundown on what a table

is let's go ahead and click on the table

design and let's go ahead and click on

the option that says summarize with a

pivot table I'll go ahead and click

here and just like before when we're

inserting a table we have the ability to

insert a pivot table here well I do want

to add this to a new worksheet so I'll

go ahead and press

okay and there you go so we have our

pivot table all of our fields are here

for us and we're good to go so we can go

ahead and start building this table now

remember we want to take a look at some

Advanced tools such as calculated Fields

using the timeline drill down reports

and Report pages so I'm just going to

copy this data over

here and I'll put it far to the right

maybe over

here and these are the different tools

that we'll be exploring here let's go

ahead and some of the basics just to

kind of recap I'll click on pivot table

options I want to rename my table and

I'll just call this

one creary sales

while I'm here I'll click on the display

Tab and I'll go ahead and sort my field

list a toz make sure they're sorted in

alphabetical

order so I'll go ahead and do that as

well and I'll go ahead and press

okay remember you can also change the

view from stacked versus side by

side I'll leave it on side by side here

well I'll go ahead and build out a pivot

table so I'll go ahead and grab these

sales I'll go ahead and apply the

accounting format as we've learned

before number format accounting okay and

then okay so here are some of sales

going to zoom in just a little bit here

and now what about sales by product put

the product in the rows now put the

region in the columns and just like that

we have a really nice table that we can

interact with well what if I add some

data

to the original data source going to go

back to my original data source here but

let me rename this worksheet

first so I'll call this crey

sales and let's go back to the advanced

tools now what I'm going to do is at the

very end I'll press control end here and

I'm going to add another transaction so

I'll say 1109

and I'll go with the same date why

not and what I want to do is introduce

another product so I'll introduce

another product let's say I'll go

for organic

yogurt I add my name in

here and I'll introduce another region

as well so I'll go for the

East and for the units I'll go with

25,000 we already have the formula in

column F so here is my new record on row

113 now what I want to do is go back to

the pivot table we can clearly see that

region has not been

added and we don't have our new item

here well here's the benefit of working

with a table I don't have to click on

pivot table analyze and click on change

data source and then select all my data

again well all I need to do is simply

right click on my pivot

table and we'll notice the refresh icon

right here so I'll click on

refresh and just like that my pivot

table updates now this only works

because I'm working from a table so

here's my new

region and here is my new product as

well so this works because the nature of

the table the table automatically

expands when you add data to

it it continues the formatting from

above we get some nice filters in here

as well and we can see our headers as

we're scrolling through our document no

need to freeze that particular row go

ahead and convert your list into a table

go ahead and insert a pivot table add

another record to the original data set

and refresh your pivot table and come

right

back welcome back let's go ahead and

take a look at one of the advanced tools

which is a calculated field so what I

want to do I'm just going to go ahead

and reset my table here and all I want

to focus on are my sales reps so I'll

add my sales reps to the

rows and the sum of s and the values as

well so maybe we want to go ahead and

calculate the commission based on the

sales here so let's say we're giving out

commission and the commission is at

25% not bad so what can I do to be able

to calculate the commission well I don't

have a field over here that's called

commission if I go back to my original

data set I don't have a field called

commission so I have two options I can

either come back here to my original

data set add a field call

commission go ahead and insert my

calculation fill down my

formula and then go back to my pivot

table and then refresh the data well

those are a lot of steps and a lot of

times you don't want to really

modify the original data one of the

benefits of working with a pivot table

is the changes that you make on the

pivot table they do not impact the

original data the original data stays

intact and so we want to avoid having to

come here and add another field so to

remediate that what we're going to do is

create what's called a calculated

field going to click on pivot table

analyze and on pivot table analyze over

in the calculations command group if I

click on the drop down I'll see that we

have what's called a calculated

field now once I click on the calculated

field I'll be presented with a dialogue

box I simply need to enter the name and

then the

formula and once I create that

field I can add it over here as a field

that I can use pretty much anywhere in

my pivot table it's also going to be

displayed right here and all of my

values are going to be displayed here as

well so let's take a look at how that

works I'll click on pivot table analyze

here is Fields items and sets I'll click

on calculated field and as expected

we're presented with this dialogue box I

need to go ahead and add a field name

well I want to call this field

Commission

because that's exactly what I'll be

calculating here now the formula we can

only enter simple formulas in here we

cannot refer to a cell reference like F1

in this

case if you click on the question mark

here this little help button this will

open up a Microsoft page for you

indicating the types of formulas and

functions that can be used here but I

just want to keep it simple so what I'm

going to do is my formula is going to

equal the sum of sales times the

commission at

25% so what I can do is I'll grab the

sales here I'm going to insert that

field into the

formula and I'll enter my asterisk so

equals sales times and I'll enter

25% just like that so the name of the

field is going to be commission the

formula is equal to the sales time

25% what I'll do is I'll click on ADD

add and I'll see that this will be added

to my field list here and then I'll go

ahead and press okay and then we'll see

it populated over here as well quickly

giving us the commission for each of our

sales reps so I'll click on

ADD and here is my commission field

now then I'll go ahead and click on

okay and there you go so we get those

two things we have the commission field

we can use over and over and here we

have answered our question we can see

that because Watson had the highest

sales Watson is getting the highest

commission at 100, 1412 as well so

that's the benefit of working with a

calculated field well now what's really

nice is well what if I want to calculate

the commission not just by sales rep

what about the commission that would

paid out based on a particular product

well look what I can do I'm going to go

ahead and move things around here so

I'll go ahead and click on the

product and I'll go ahead and remove the

sales

rep and look at that detail that we're

given so when you create a calculated

field we can use

it and multiple instances in our pivot

table as well finally I'll do one for

the

product let's see what we have to out

based on our region so I'll click on

region going to remove move my product

and here we go so now we can see the

commissions that were paid out 75,000

commission was paid out in the central

region so go ahead and insert a

calculated

field go ahead and add it to your pivot

table fields and go ahead and use it and

get the commission by product sales rep

and by region and come right

back in this mini lesson we'll learn

about the timeline I'll go and quickly

construct my table here I just want the

region and the columns and I want the

product and the rows so here's my simple

table I can see all of my products and

all of my regions here's my grand total

well a lot of the information that we

analyze it is based on time and a lot of

time we want something like a quarterly

report or a yearly report or even a

month monthly

report so we do have a properly

formatted date field in here which is

the sale date so I can go ahead and drag

the sale date to one of these boxes or I

can just click on

it but uh it's going

to just kind of place dates in random

places here so what I prefer to do is to

use the actual timeline to use a

timeline we'll click on pivot table

analyze and right next to our could

friend the slicer we can see that we

have the timeline now this is why it's

very important to make sure that you

have a properly formatted date field if

we do not have a properly formatted date

field we will not be able to insert a

timeline well let's get some reports

here I'll click on insert

timeline and as you can see it found

that we have a

matching date field here for the sale

date I'll go ahead and click on sale

dates

I'll go ahead and click on okay and here

is my timeline so just like the

slicer it comes with its own contextual

tab so we can change the color I'll go

ahead and just give it a little contrast

here I'll give it this orange

color there we go so here is my timeline

the way that the timeline works it's

basically a filter just like a slicer

but we're working with a date higher key

so the first thing to note on the top

right of our timeline we'll notice right

here if we click on this dropdown we can

either look at our data from a year

level quarterly level months or even

days as well well I'll click on

years and it looks like we had

transactions in 2022 and in

2023 so if I were to ask well what were

our sales for 20 2022 if I click here in

2022 I can see that we had sales of

519,000

378 notice how all of these numbers have

changed because we're filtering just for

2022 so when I click on

2023 all of these numbers should update

as

well I'll click on

2023 and we can see that we have a

different set of numbers here for 202

three so that's looking at it at a year

level let's go ahead and take a look at

it at a quarterly

level so because we have two years we

have eight quarters that are available

so now we can be very specific what were

our q1 sales in

2023 that is pretty cool so no need to

write formulas the pivot table is taking

care of all that for us here are our q1

numbers for 2023

now I can also expand this timeline so

what about the first half of

2023 notice when I move my cursor

towards the end of this little time

capsule I can drag it to the

right so now I'm looking at q1 to Q2 of

2023 here is my grand total so pretty

pretty nice as well what about Q4 for

2022 there we go

now let's change this to months so I'll

click on months here and we have a lot

of months to work with so we need to

kind of expand our timeline

here and let's say I just want to focus

on

2022 well what about January of 2022

look at that in January of 2022 we had

sales of 68 to

44 February went down march went down

April

ail we're kind of going down here June

pick back up it's the summer time

everybody

wants tasty treats

here and so we can see how that works it

even goes down to the days as well so

this is very granular so we can go down

to specific date June 23rd of 2022 look

at

that so pretty cool I'll go ahead and

clear the filter from my timeline and so

that is the timeline a really really

nice tool to help you to have more

control over time-based data

analysis sometimes when you're taking a

look at your data you may want to just

kind of get more detailed in terms of

these numbers for example we can see

that in terms of products we can see

that ice cream had the highest sales

445,000

$80.50 here as well well what if we

wanted to you know kind of use ice cream

the ice cream sales as kind of the

standard so in other words how can we

improve our sales for frozen yogurt

popsicles and tasty treats and so forth

and so on well what we can do is we can

create a separate report for ice cream

and all of the transactions that refer

to ice cream will be included in that

report well we don't have to do anything

special we don't have to build it I'm

just going to go ahead and double click

on this number and when I double click

on this number let's see what happens

well it's going to create a worksheet

for us with all of our details

concerning ice cream so I'll go ahead

and double click on this

number and as you can see it has all the

transactions here so these are all of my

transactions regarding ice cream

here's the dates in here so we can

see so this is a pretty pretty nice tool

because we have all the detail so now we

can break away from the pivot table and

we can focus on maybe this will be the

data for discussion for this portion as

well so we have everything in

here so now we can answer the question

how did we arrive at this

445.6 K here's the breakdown and the

pivot table gave this to us we did not

have to build this manually now if you

wanted to go ahead and run some filters

on here you know we can filter by date

because it inserted this data in table

form and so we're good to go so maybe we

can go ahead and do some

sorting and so forth and so

on so that is just being able to kind of

drill down or drill through our actual

report so a nice little tool that allows

us to do that we can double click on any

of these numbers and we'll get that

particular detail here as well for

frozen yogurt in the central I'll double

click

here and as you can see only frozen

yogurt in the central region so pretty

nice okay let's go back here to our

creary sales the next thing we'll talk

about is what's called report page

filters so basically what I want to do

is I want to go ahead and kind of create

another pivot table based on this table

and specifically what I would like is I

would like to have different pivot

tables one pivot table on a sheet that's

for the central region another pivot

table on another sheet for the

North and of course one for the East and

the West as well right so so they will

behave independently and this gives me

the capability this way I don't have to

try and copy and paste the pivot table

somewhere else I can just create

individual report filter Pages for these

here as well so how does that work well

the first thing we need to do is we need

to go ahead and

introduce a field into the filters so

the first question is well what do we

want to create the reports based on what

well we want reports based on the region

so I want Regional reports I'll go ahead

and drag the region into the filters box

here and now that I've dragged the

region into the filters box let's go

ahead and click on the pivot table

analyze tab let's go ahead and find out

where we can insert these reports all

the way to the far left we have the

pivot table command group if I click on

the drop down I can see that I have show

report filter pages are available here

right so step one we need to go ahead

and

introduce a field into the filter box

step two we need to go ahead and click

on the pivot table analyze Tab and step

three we need to go ahead and

actually show the report filter page so

I'll go ahead and do that I'll click on

the drop down here I'll click on show

report filter pages

and look what it's looking for it's

looking to see what field we have in the

filters area if we did not have a field

in here we would not be able to show a

report filter page so that's why the

first step is adding one of these fields

into that filter I'll go ahead and press

okay and let's see what happens we

should get four new

worksheets each one for east west north

and south let's go ahead and press

okay and here we go so here's the north

well we'll start central so here's the

report page for

Central

North

West and East as well so these are

behaving autonomously so if I make any

changes here it does not affect the

original pivot table so I can go ahead

and just focus on the Central here and I

can go ahead and add my timeline if I

want to analyze the data here just for

the central if I head on over to the

West I can do the same thing I can kind

of just you know move these around here

so maybe I just want to go ahead and uh

let's see maybe I'll put the sales

person here in the

columns so we can go ahead and

reconstruct these individual tables in

any manner that we want so those are the

report filter Pages we have one one for

each of the region just be careful when

you're creating these report Pages this

works well because I only had four

regions if I had 20 regions we would

have 20 worksheet tabs to work with

so just remember not every field is a

good candidate for a Slicer in the same

way not every field is a good candidate

for a report filter

page now when you're working with your

pivot table you do have the capability

to customize the design and in this

lesson what we'll do we'll take a look

at how we can change the report layout

we'll customize how the sub totals and

the grand totals are displayed and we'll

go ahead and just a quick demo on how we

can create our own pivot table design if

I click on my pivot table here these can

all be found on the design tab so I

click on my pivot table here's the pivot

table design we can always choose a

quick pivot table style let's see what

style are

available so we have light Styles we can

also hover over each one we can see how

it's impacting our pivot

table we have medium Styles where we can

see that we have some more contrasting

colors in here especially for the

headers and those subtotal

rows if you scroll down we also have

dark

tables so it really depends on what you

are looking for now if we take a look

right now now what if none of these

tables are working for us we want to go

ahead and create our own pivot table

once we do that we'll see another

section up here at the top and this will

display any custom pivot tables designs

that we have created we can also make

that the

default as well but for now let's go

ahead and take a look at the different

layouts so by default we're using the

compact form I'll go ahead and add some

more detail in here so I'll show my

field list and I want at least two

fields and one of these rows so maybe

I'll go

for let's see I'll put the product in

the

rows there we

go okay so here we can see this is the

compact form I'll go ahead and change

the report layout to outline form and

then we'll take a look at tabular form

and just go ahead and choose whichever

one works best for you so here's compact

we're just compacting as much data as we

can here's the outline

form and we can see the outline form

kind of just gives us a little more

space to work with if we take a look we

can catch the change basically what it

did is it pushed our products over into

its own column it's no longer sharing

the same column as our sales person here

so if I go back to

compact we can see it's going to push it

over to column

B and there we go so this looks pretty

good let's take a look at tabular

form so tabular form looks really really

nice and if you're showing this for the

first time you may want to use table to

form looks a

little more simple to kind of understand

what's going on here as well so those

are the three different layouts that we

can choose so again here's

compact here is

outline and then here is the tabular

form which more mostly represents an

Excel table as well now we have some

other options down here on the bottom

such as repeat all item labels so

basically what this does this fills out

the blank space here and this is helpful

if you have a really large you know a

lot of data being displayed here and you

may want to just add that little detail

here so everyone can kind of read the

data from left to right but I can say do

not repeat all item labels we're good to

go now when you're displaying your pivot

table there's a lot of numbers here a

lot of grand

totals we have a lot of subtotals in

here as well and this could be a little

overwhelming for your

audience so you can determine which one

of these you want to turn off what you

want want to leave on so we can turn off

these subtotals these Grand totals let's

just quickly take a look at some of the

options that are available so for

subtotals if I say do not show subtotals

let's see what

happens so we can see we don't have that

subtotal

row down here at the bottom for each of

our sales rep so we only have the grand

total column on the right and the grand

total row on the bottom here as well if

I go back to subtotals I can say show

all subtotals at the bottom of the

group so it shows down here here's

Bishop Bishop gets a

subtotal or I can say show it at the top

of the group now depending on the layout

that you're using this will not matter

if I change this to compact we can see

it showing at the top

here I'll go back to tab form

and so that's it so I'll go ahead and

show the subtotals at the bottom of the

group then we have grand totals so the

same thing we can turn off our grand

totals and so we can see we no longer

have our grand total column we no longer

have our grand total row so this just

gives you a little more flexibility in

terms of what you want to

display maybe you just want to focus on

the subtotals for now and not so much

the grand totals so we do have those

options here as well the other option is

Grand totals on for rows and columns

which is the default if I only wanted to

be on for rows then here is the grand

totals summarizing our rows over here as

well and as expected we also have one on

for columns

only which shows the grand total at the

bottom summarizing our Central north and

west regions here as well

so those are just some of the

different ways that we can kind of

improve the experience for our audience

go ahead and toggle some of these

options see if you can catch the change

let's come right back and we'll actually

go ahead and create our own pivot table

style let's go ahead and create our own

pivot table design so I'll go ahead and

click on the design tab here and I can

go ahead and change it maybe I'll go

ahead and change it to a medium style

table so I'll go for this one here so

light blue pivot style medium

6 so I can go ahead and choose one from

here if I want to or maybe I'm scrolling

and I just not finding the one that I

like here so what I can actually do is I

can go ahead and create a new pivot

table style maybe you want to go ahead

and brand your pivot table to your

company colors or so forth and so on

keep in mind

that when we're building the table the

colors that are available is based on

the current theme that we have selected

so for example if I click on the page

layout tab here and I head on over to

the left I can either choose an existing

theme the same themes that are available

in PowerPoint and word or I can go ahead

and choose my own colors I can go ahead

and choose my own fonts here as well

we'll keep it simple I'm just going to

go ahead and take a look at some of the

themes that are available here so by

default we're all using the office theme

unless you change it to facet or gallery

and we can see that our entire Excel

document is being updated the colors the

fonts and the effects are immediately

being updated or changed we have access

to different fonts and different colors

here as well so here are just some

different ones so here's

droplet so if you want access to

different colors to work with go ahead

and change change the theme or you can

select your own color palette you can

also go ahead and select your own fonts

as well well I'll keep it simple I'll go

with the basic office theme here I'll

click on the design tab I'll click on

this drop down and I'll go ahead and

create a new pivot table style now we'll

just cover the basics here this will

take a very long time to actually go

through this so I'll go ahead and call

this one pivot table Style

I'll call this pivot

table custom

one so the way that this works is you

give it a name and then over here on the

right we have a

preview in terms of what the table will

look like so we can see once we add the

formatting to the header Row the first

column the total row so forth and so on

when you're formatting the table you

need to choose what part of the table

you want to form format so there's a lot

of parts here the whole table report

filter labels First Column stripe header

row First Column so we can interact with

all of these different elements here

subtotal rows column subheadings grand

total column

so I'll go ahead and format the whole

table the First Column the header row

and then maybe the grand total row

right so let's see so I'll start off

with the whole table I'll click on the

format tab here notice before you start

this or before you finish you can also

click down here on the bottom to lock

this in so that every time you create a

pivot table this will be the default

pivot table

style I'll go ahead and click on format

for the whole table and just like when

you're creating a table design we have

this simple dialogue box where you can

choose the um

how you want this to interact here as

well so I'll go ahead and click on the

fill color right so the fill color for

my entire table let's say I'll go with a

light

gray and I'll go ahead and press okay

and I can see here it's already has that

gray color I'll click back on the format

tab I'll go to the font and for the

color maybe I'll go with a dark blue

I'll go ahead and

press oh maybe I want the borders as

well I'll go ahead and add a border I'll

say I want

an outline

border and I'll choose a solid line and

I want the color to also be that dark

blue so I'll go ahead and press

okay and there we go so I'll go ahead

and format the header row now I'll just

do the opposite here for the header Row

the font is going to be gray this

time I do want it to be bold I want it

to stand

out and for the fill color it's going to

be that dark

blue so I'll go ahead and press okay as

you can see up here here's the header

row has the gray text on a dark blue

background My First Column we go to to

My First Column here I'll also go ahead

and format that and I think for this one

I just want to make the text bold right

so I'll make the text

bold for the First

Column I'll head down here to

the grand total column I'll format that

I'll give that a double underline and

I'll also make that

[Music]

bold and maybe the fill color I'll give

it

a different shade of blue here maybe

that

shade there we go so we have that and

then finally the grand total row so as

you can see it's a lot of formatting in

here again I just want to keep it simple

I'll click on the format tab for the

grand total

row and for the fill color I'll give it

a simple fill here for the font I'll

also give that double and I'll make it

bold I'll go ahead and press okay so

this is kind of what my table is going

to look like here as well well I'll go

ahead and press okay and let's give it a

test and let's see what that looks like

so I'll press okay so now how do we find

that design if I click on the more drop

down here at the very top in the custom

area I can see that I have my custom

table well let's go ahead and apply that

to style

here and there we

go I could make a few Corrections maybe

I want the grand total row here to kind

of mimic the header row but doesn't look

that bad right if we need to make any

modifications to it we can just

rightclick on the style on the design

tab we can click on modify this brings

us right back to that box and this gives

us the capability to make any

modifications that we need as well again

just a quick reminder it's based on the

theme and the colors that you're

currently

using so if you want to go ahead and

choose change the theme and then go

ahead and design your own pivot

table let's head on over to the power

pivot setup worksheet Tab and power

pivot is not turned on by default we

need to go ahead and turn it on once we

have it turned on we'll have an

additional tab here on our ribbon that

we can use this is what it would look

like we can introduce data into the data

model we can create measures kpis we can

detect

relationships so we want to go ahead and

turn that on we need to get to the Excel

options here and we need to click on

customize the ribbon on the left Pane

and we simply just need to go ahead and

turn on power pivot the same way that we

will turn on the developer

tab going to go ahead and right click on

any one of my tabs here I'll right click

on the Home tab I'll click on the

customize the ribbon option here and

this brings me right to the area where I

need to be here's customize the ribbon

over here on the far right here are my

main tabs I'll scroll down until I find

power pivot so here is power pivot I'll

go ahead and select it then I'll go

ahead and press

okay once I do that here is my power

pivot on my ribbon and now I can go

ahead and add my Excel tables to the

data

model and go ahead and create the

relationships and do what I need to do

to make a pivot table as well now if

you're not seeing the power pivot over

here you can go back to the customize

the ribbon and under add-ins you want to

go ahead and search to see if the power

pivot adom is

available so what you want to do is

click on

the down here under addin it's a Comm

addin so go ahead and click on the drop

down here change it to a Comm addin and

then go ahead and press go so Comm addin

and then press go we'll be presented

with another dialogue box that will

allow us to add power pivot to Excel for

Windows so I'll go ahead and click on

go and here we can see we have Microsoft

power pivot for

Excel so just go ahead and press okay

and once you do that you should have

access to power pivot I already have it

I'll go ahead and press cancel and I'm

good to go go ahead and add power pivot

and come right back we'll start working

with our power pivot data

now here we can see we're working with a

data set now it's very common in the

business world or the data World rather

that we have our data in different

places so for example we can see that we

have a product list here with have

several products including the actual

product

prices we also have

another data set here so this is our

sales

rep list and we have all of our sales

reps and we have their corresponding

regions and then finally we have our

order form so this is the form that's

kind of tracking the orders for example

on August 31st Christy Olen sold two

units of the doublers and so we want to

go ahead and figure out well what were

the total

sale so right now we want to focus on

our order form we want to make sure that

this has all the data that we we need

and then we're going to go ahead and

create a pivot table from this or order

form here as well so it would be nice if

we can add the region based on the sales

rep so we can add the region here and

we're missing the product prices so if

we can add the product prices in here as

well then we'll have the capability to

basically Run Regional reports and all

of our prices in here as well finally we

need to go ahead and create another

column here so that we can grab the

total

sale right so this is going to be our

sale well how can we do this there's

several ways that we can do this but

let's go ahead and use power pivot which

in my opinion is the most efficient way

to accomplish these

tasks the first thing we need to do we

need to go ahead and convert each of

these lists to a table so I'll go ahead

and just press contrl

T then I'll press

okay and I want to go ahead and name

these tables accordingly because once I

upload these tables into Power pivot

when I'm writing my formulas I'm going

to be referring to the table by name so

I want to make sure I don't have any

spaces that it's just one word and that

it accurately describes the table so

right up here I'm want to go ahead and

call this table table products or I

could say TBL products I'll just call

this table

products I'll go ahead and press enter

one down two to go I'll go to my

salesperson table here I'll press

control

T they're the 73 records I'll press

okay and I'll call this one

table salesperson

[Music]

there we go and then finally for our

orders I'll go ahead and press contrl T

here as you can see we have 10,000 rows

of data I'll go ahead and press okay and

I'll call this one

table

orders so that's it so we have our three

tables let's go ahead and click on power

pivot so we have each table they're

named accordingly let's go ahead and add

each these to the data model so if we

hover over it it says add an Excel table

from the worksheet to the data model

let's go ahead and click on our first

table let's click on add to data model

it's going to open up power pivot for us

and add the details of that table into

Power

pivot so here is power pivot we can see

it's open here on power pivot we have

the Home tab design and we have advanced

I'll go back to the Home tab here notice

on the very bottom here our first tab is

our products

table so I'll just go ahead and minimize

this I don't want to close it I'll just

minimize it I'll go back to my Excel

I'll click on my second table here and

I'll click on add that to the data

model and so as you can see we now have

our products table and our salesperson

table included in the model

here I'll go ahead and add the third

table so I'll add this to the data

[Music]

model and there we go so we have

products

table salesperson

table and then we have our order table

here as well go ahead and add those to

the data model and come right back

now that we have our three tables into

the power pivot for Excel data model we

want to go ahead and establish

relationships between these three tables

just a quick trip here we're on the Home

tab in power pivot and we have a couple

of command groups as expected we have

our clipboard command group we have the

capability to get external data so this

is the benefit of one of the major

benefits of power pivot we can pull in

data from several different sources as

long as they're related to each other we

can do that so we can actually pull in

from a SQL server or an access

database different services that we can

add here as well we have other

sources so lots of different places that

we can get data from as well while we're

in here we can format columns just by

clicking on one of these tools here now

unlike Excel we just format a particular

cell you can format an entire column

with these different types of data here

as well we have sorting and filtering

then we have calculations but what we

want to do we want to create

relationships between these three tables

once we create relationships we can

pretty much return any field that we

want to from any of those three tables

so the first thing we want to do from

the Home tab we want to go ahead and

switch over to diagram view this will

show us how three tables and we'll be

able to create the relationships and

connect them together so that they can

all talk to each other so I'll click on

diagram view

here and for those of you who have used

Microsoft Access this looks pretty

familiar here are our three tables

here's the products table our two Fields

salesperson table our two fields and

then our orders table so I'll just go

ahead and move my order table this has

no bearing on the data this is just to

be able to focus on this table here so

here's my orders table so now the

question is well if we take a look at my

orders table and my products table what

do they have in common so what field do

they have in common well we can see that

it's the products they both have a field

that's called products so what I'm going

to do to create this relationship I'm

going to go ahead and drag the

products from my products table right to

the products field on my orders table

now I can go either way I can go this

way I'll go ahead and drag it and

connect it to the products

here when I do that I can see what's

called a join line so here's my join

line my join line has a on to many

relationship so this is telling me that

we only have one

let's

say the doublers product it only appears

once on this list but because this is

our order form this appears many times

on this list so you'll see this a lot

mostly you'll have a one to many

relationship and our second example here

so what does the

salesperson table have in connection

with our orders table well the

connection here is the sales rep

right so we can find many instances of

our sales reps on the order table but

just one unique value on our salesperson

table so I'll go ahead and drag the

sales rep right to the sales rep here

and there we go so here are our two

relationships we'll see why this is

important in just a little bit but for

now go ahead and create those

relationships when we come back we'll

see how we can use a Dax expression

called Rel ated to

basically borrow the region from the

salesperson table and also borrow the

product price from the products

table we have three tables and we have

established the relationship between our

tables and our order table I'm going to

click back on the data

view so I'll click back here now I can

see the data for my order table here's

my salesperson table and here is my

products table well I want to focus on

my order table so I'll click here now

what I want to do we're missing

the region so I want to add the region

based on our sales rep so I'll go ahead

and click on the first empty cell here

in that blank column and just like in

Excel we have a formula bar right here

well I'm going to enter a what's called

a Dax expression or a a data analysis

expression so that I can pull in the

corresponding region for each of our

sales rep so I'll type equal and I'll

use the

related now this is one of the nice

things about using the Dax expression it

uses common language and we get some

help along the way so I just started

typing the phrase related and as you can

see related here it returns a related

value from another table if I go down to

related table

this Returns the related tables filtered

so that it only includes the related

rows well I just want related so I'll go

ahead and double click

there and for the first argument is

asking me because I created

relationships between these tables I can

either pull the price from the products

table the product from the product table

well what I want is the region from the

salesperson so I'll go ahead and double

click here and then I'll go ahead and

close my

parentheses so basically I'm using the

related function what's available I can

return the region from the salesperson

table based on our current sales rep

once I press enter it's going to

populate all the regions here based on

each sales reps region so I'll go ahead

and press

enter and there you go so Christy Olen

is from the West Dan Peters is from the

Midwest mayel Lindsay is also from the

West Isabelle cross is from the East

I'll go ahead and rename the column so

I'll double click on it and I'll just

call this

region good to

go next I'm missing the product price so

I want to go ahead and return the price

based on the product here so I'll click

on

the first empty

cell on the new column I'll click equal

and again I'll use the related

function now I want the price from the

products

table I'll go ahead and close my

parenthesis and press

enter and it populates all of my prices

for me my doublers Are

$79.95 My Sunshine product is1 19.95 so

forth and so on I'll go ahead and double

click on this column and I'll go ahead

and call this I'll just call this price

and press

enter and I'm good to go now one of the

nice things with power pivot here and

the other power tools like power query

we can move these columns so if I want

to go ahead and move the region right

next to the sales

rep I can go ahead and do that can't do

that in Excel what have to cut it insert

it couple of different steps there as

well for the product price I'll move

that as well over to

the get right after the actual product

so there we go sales rep region product

price there's one thing left to do we're

going to go ahead and enter a formula

here we want to calculate the actual

sale now we'll keep it simple we

won't add the discount in here here we

just want to go ahead and multiply the

price times the units it's going to be

the

price times the unit sold and that's

going to go ahead and give us our answer

so for this one when you're writing

calculations and power pivot we are

summarizing columns not just cells so

when I write my formula here it's going

to go ahead and summarize all of that

for me so I'll click on the formula bar

I'll say equal

and just like in Excel I'll click on

that

price and notice how it puts in the

column name for me

here and I'll click on the asterisk and

I'll click on

units right so let's multiply the price

column times the unit column I'll go

ahead and press

enter and as expected I now have my sale

for each of my transactions here as well

I'll go ahead and double click on that

column I'll rename it to sale and I'll

go ahead and press enter and there we go

so we were able to write three Dax

Expressions to add three columns to our

data set and we can see it's summarizing

the columns for us we don't have to go

step by step there's no need to autofill

down and we're good to go here as well

if I wanted to I can go ahead and apply

the currency format

to the sale here also to the

price so just some really nice cleanup

opportunities that we have in here that

we can use if I click on design we have

some more options in here we can freeze

a

column right here's some more

relationships we can manage the

relationships if we want to break a

relationship we can do that a lot of

calculation options that we can use

there's a lot in here that we can use

but right now mission accomplished we

have pretty much rigged up our order

table so that we can go ahead and create

a pivot table from here so the final

step is to go ahead and click on the

pivot table Command right here this is

going to insert our pivot table into our

current Excel document and now we're

able to combine all three of those

tables into one and make a cohesive

pivot table where we have some nice

reporting as well so I'll go ahead and

click on pivot table here it's asking me

where I want to place it I'll place it

on a new worksheet I'll go ahead and

press

okay here's my pivot table I'll turn on

my pivot table

fields and here we go so notice how it

pulls in all those tables for us but I'm

only concerned about the orders table so

now I can go ahead and build my pivot

table here based on

region right based on

product and I am good to go so a really

nice way to work with our pivot

table so we've inserted our pivot table

using power pivot now a natural question

is well what if we were to add some data

to our original data set so what if I go

back to my power pivot data here and

what if I were to change the prices here

so let's say I'm just going to go ahead

and double the prices here when I go

back to my pivot

table what will I need to do do I need

to go back into Power pivot

again and make the changes all over

again or will all those steps that I

created and power pivot will it run

through those steps and update my pivot

table well let's go ahead and take a

look let's go ahead and record our grand

total here so right now our grand total

is

915 787

50 so I'll go back to my power pivot

data and let's just change some of the

prices here maybe the Aspen is double to

42 maybe the quad is double to 68 maybe

the sunbell it's now 50 and maybe the

vrang is now

38 right so I just made some updates to

the original data set I'm going to go

back to my pivot table here and let's

right click on it and let's refresh and

let's see what

happens look at

that everything has been updated because

I made those changes for the product

prices we did not see it but basically

what happened was it started from the

very very beginning it opened up power

pivot looked at the data model each

table was refreshed to check to see if

any data was

updated in that case it was and once it

caught the changes that were made to my

products

list it

recalculated the

sales in the sales column for me and

provided the new information here as

well and the best thing about all of

that the original data set no matter

what we do to our pivot table here or in

power pivot the original data stays

intact right but if we change it

manually here we simp simply just need

to refresh the table we can get some

realtime results here as well so that's

another major benefit of working with

the Power Platform so go ahead and go

back to the original data set go ahead

and change some of the prices if you

want to go ahead and even add another

transaction in here or maybe edit one of

these transactions or add a new

transaction or two or three and simply

go back to your pivot table refresh it

and go ahead and locate the change

for our data mining worksheet here well

this sheet is dedicated to just giving

us some tips and tools that we can use

to improve the reporting on a particular

data set so originally we had the first

name and the last name in two separate

columns so what we did was we realized

it would be better would be more

efficient if we had the rep's full name

in one field here or one header we also

had the city and the state in one column

well that doesn't help us much so if we

can extract the city and the state now

we have more reporting capabilities such

as being able to report based on city

also report based on state we don't have

to worry about the date as long as

they're properly formatted we can use

the timeline to get quarterly reports

and so forth and so on so to do this

we're going to be using a tool called

[Music]

flashfill and flashfill basically looks

to the

left so it looks

left and it tries to find a

pattern once it finds the pattern it's

going to offer

suggestions so for example I'll go ahead

on click on the rep full name here so

I'll go ahead and type maybe I'll do

this maybe I'll say

Olsen comma space

Cara I'll go ahead and press

enter now let's see what happens when I

start typing Anthony's last name so

TS look at that so as soon as I start

typing the last name it already looked

to the left it already sens the

pattern so in this case the pattern it's

saying it looks like you want the entire

string value that's in column C you want

to add a comma and a space and the full

string value that is in column B well

this looks pretty good I'll go ahead and

press

enter I'll go ahead and expand my column

here and maybe I'm skeptical I'll go go

to the bottom here and I can see it all

looks good and that's Silva Jerry rera

and I'm good to go so Flash Fill will

either run automatically for you once

senses a pattern if you want to run

Flash Fill manually enter a few examples

and then click on the data Tab and over

in the data tab in the in the data tools

command group we have Flash Fill little

lightning bolt going through a table my

favorite tool in all of excel I've Ed it

in so many instances to clean up data

let's go ahead and run Flash Fill here

let's go ahead and extract the state

from column G um well the city sorry so

this is

Boulder I'll go ahead and press

enter now on the next Blank cell I'll go

ahead and run Flash Fill notice you can

also run Flash Fill by pressing control+

e well I'll go ahead and run Flash Fill

here and I can see it's extracting

everything to the left of the comma Now

what's really nice about Flash Fill is

also intuitive sometimes maybe it only

puts bowling here instead of Bowling

Green

well if I corrected this and added

Bowling Green in here this will kind of

retrain Flash Fill and it will look for

any city that has two names for example

San Antonio and it will make the

correction for me so it is

intuitive last one I'll go ahead and

extract the state So Co for

Colorado this time I can either start

typing

Oregon or I can press contr e to run

flashfill or I can run Flash Fill

manually here as well now we're good to

go as soon as you run Flash Fill you'll

get the Flash Fill options here you can

undo Flash Fill if it did not find the

pattern correctly or you can accept

these suggestions you can go ahead and

select all 41 of those change cells as

well I'll just accept these suggestions

and I am good to go if Flash Fill does

not work right away just keep on

entering a few more examples until it

finds the pattern and offers you the

suggestions as well either way we were

able to improve this report and now we

can go ahead and get some reports based

on city state rep's full name and use

the timeline as well I'll go ahead and

insert a quick pivot table from here

I'll use the quick analysis tool I can

either right click and right in that

search menu here at the top it's one of

the newer features I'll go ahead and

type pivot

table if I can click on that box here we

go and here we have some recommended

pivot tables we also have summarize with

a pivot table but let's see if I can

find quick

analysis here we go here's the analyze

data if I click here opens up my analyze

data pane over here on the right and now

what I can do is ask questions about my

data I can also go ahead and insert one

of the these pivot

tables so this has sale by

order city state and

city uh what else do we have here sale

by state here's a pivot chart that we

can insert I'll go ahead and insert this

pivot chart

actually there we go so here's the pivot

table and we also have the pivot chart

we'll give that a dark

theme and and there we go so all set to

go congrats on completing the course as

a recap we're able to prepare data for

pivot table

analysis we're able to insert and manage

a pivot table we're able to insert and

manage a pivot

chart and then we're able to connect

data sources with the popular X lookup

function we're able to summarize a table

with a pivot table

we're able to customize our pivot table

design we're able to use power pivot to

connect different data

sources then we're able to use power

pivot to create measures with Dax

Expressions as well so congrats on

completing the course and as always I

look forward to seeing all of you in a

future learned

course hello everyone and welcome my

name is Joe and I'm going to be

facilitating this course for you today

today we're talking about co-pilot from

Microsoft Excel now co-pilot has been a

major topic lately and it's something

that's going to change the way that we

work with these applications in order

for us to understand co-pilot for

Microsoft Excel we have to understand

what co-pilot

is co-pilot is a powerful addin tool for

Microsoft Excel that allows users to

automate repetitive tasks improve

efficiency and also save time now it

utilizes machine learning and AI

technology to analyze your workflow and

then suggest code Snippets based on your

specific needs and with copilot you can

automate data manipulation formatting

calculations and so much more without

actually needing extensive programming

knowledge now a couple things with

co-pilot is the way that it comes out

with outputs an input is when you ask it

a question and then the output will be

the result or the answer to that

question and the way that it randomizes

this is known as stochastic process now

stochastic process in AI models can lead

to slight different results each time

the model is run so even though we put

in the same input right I can say hey

how much does this equal out to my total

sales it might not be word for word the

same exact output and this is often what

is desired right we often want r

Randomness so that none of us have the

same answers so just know that if you're

following along with this video that you

will never get the same answer as me

unless it's something really

straightforward like give me the total

sales that I've made it will give you

the total sales that's not random but if

you asked it to give you insights it

might give you something totally

different than the insights it gives

me another thing is a couple of the pros

and cons of using co-pilot so we're

going to start off with Pros

productivity boost right it aims to

enhance productivity by automating

various tasks such as writing emails or

creating presentations or even taking

meeting notes which is great we can also

integrate it with Microsoft Suite which

is what we're doing today we are going

to be looking at excel's version of

co-pilot we also have to understand that

is a natural language process which

means we're able to just put in inputs

naturally and then we get

results and the best part about co-pilot

is learning and adaptation it's always

designed to learn from our user

interactions so the more questions we

ask over time it starts to understand

how we think about our data now some of

the cons of using copilot are first off

potential bias and inaccuracies right of

course this is a new technology so

sometimes it might generate inaccurate

information based on the data it was

train TR on and you'll notice that

throughout the course that sometimes I

might not get the exact answer I want

and I need to teach it what I want you

also see here that there's an adoption

and a learning curve as with any new

technology you're going to have to get

used to using the interface or even

certain input commands that you're going

to askask it or certain prompts that you

want to look at so that's why this video

is going to be super helpful for you

because it's going to allow for you to

understand those learning curves and

last but not least

ethical concerns of course you know it's

an AI assistant so we want to make sure

that we're not just using it to generate

misinformation or plagiarized

content now the last quick note before

we actually dive into this is that

Microsoft co-pilot is still in

development and its final capabilities

the pricing availability is yet to be

determined so as with anything else that

is AI powered it's crucial to approach

it with a critical mindset right we want

to be sure that we're maintaining our

privacy and security we're going to

comply with any of our company's rules

right and this AI is definitely not a

replacement for human expertise or

decisionmaking so we are actually using

it as a tool not a

replacement so in order for us to

actually use cod pilot for Excel we need

to have it set up so I don't mean

actually having co-pilot on your Excel

that setup should automatically update

after you purchase co-pilot and then

you'll just click update and then you

should see the button but this button

will be useless unless we actually set

up our data for the use of co-pilot and

the way that we have to have co-pilot

set up for our data is that our data has

to be a table and also you have to be

connected to a cloud storage system

which for me is going to be my one drive

so if I click on Co pilot right now

it'll even tell you that autosave is

turned off you need to connect to

autosave so that's the first thing even

if you're not connected or you don't

have a table yet it'll remind you so I'm

going to actually click on turn on

autosave give that a click and it's

going to say how do you want to autosave

I'll select my one

drive and now it's saved to my cloud

storage system but that's not it it's

still going to tell me hey I only work

in Excel tables are you ready to

actually turn this into a table and it's

really great because all you have to do

is Select into your data set and it

picks up that it's from A3 to

f105 so it's not including the title or

my little bar that I made here it's only

including the headers and the data and

I'll click on convert so already you can

see artificial intelligence is helping

us we can quickly change this into a

table without have to select our data

now I am going to just change the table

Styles here you could do this if you

want to as well and let's just go with I

don't know I'll go with something like

this or you know let's do this

one so now I have my table it looks

great you'll notice that co-pilot pane

is now on the right and if you exit out

of it no worries you can always go back

to the Home tab and then click on

copilot to open up the pane again

now when you first open up the pane

you're going to see that immediately

we're getting prompted right whether we

want to add a formula column or

highlight our data or sort and filter it

or even just analyze it also in this

pane we have show Data Insights show a

suggestion for formula columns or can we

highlight filter or sort the data and we

can even prompt right The Prompt area

where we can ask a question or make a

request about our data but remember in

order to even get this Paine you first

have to set it up so that your data has

to be a table and that your autosave is

turned

on so the paint itself is going to look

very similar across the different

Microsoft Suite so whether you're in

PowerPoint or word or Excel you'll

typically have this pane here and it

works the same if you click the little

drop down you can either move or resize

it or get help on co-pilot or even just

close it out or click the ex to close

out and then when you want to bring it

back up you just click on co-pilot in

your home tab now at this moment you'll

see all the different options here once

again and that typically comes with

every single co-pilot so just a list of

different prompts that you can use and

then underneath it you have those

prompts as well so whether I'm clicking

how can I highlight filter short data

it'll give me some information but

that's the same exact thing as if I just

click highlight or sort or filter so

they give you multiple ways to do the

same thing which that's Microsoft they

always have done that you'll see once

again we can ask a question or make a

request but if you don't know exactly

where to start maybe you're thinking

what sort of prompt should I do you can

always click on The View prompts and it

looks like a little book icon here and

what it allows for is all the different

prompts and it'll give you an idea of

what you can ask it so you'll see what

kind of prompt do you want do you want

to create and then you can CLI it and

it'll showcase all the things you can

create or you can say understand it'll

show you all the different things like

how many different teams are represented

in this table or which items have the

most remaining inventory so it has a

great understanding of our data already

we can click on edit where we highlight

or sort or filter once again it's sort

of going through that same phase of what

it has listed here and then we can can

ask right how can co-pilot help so it's

sort of the same four here that are here

it's just marked a little

differently another thing that's really

awesome is that we can use our

microphone to ask questions so if you're

going to be asking a long question and

you just don't like to type it out you

can use the little microphone now once

you come up with a question and you're

ready to go then you can start to use

whether these buttons you want to use or

if you just want to ask a question you

can quickly do that now remember that

this is going to be different from

whatever you put in so even though we

may ask the same question if you're

following along with the training always

remember the randomness of the output

right so let's take a moment and click

on show Data Insights and see what sort

of insights we get for our first

command now throughout this training it

is going to say working on it

understanding your data finalizing your

data so it's going to be a little more

time that we have to wait because

remember this is AI it is newer

technology and it is going through all

different ways to analyze our data

you'll see the first thing it decided to

do was get an Insight where it took my

total items sold by price now I would

say that that doesn't really make sense

for me but maybe for some of you you

might say yeah that works for us like

which items but for me I would rather

have it so that it shows me not just the

pricing but it shows me what the items

are and then it shows me how many items

we sold right that would make more sense

to me so as you can see AI generated

this content and it still might be

incorrect you can actually rate it once

you get the results you can either add

it to a new sheet or you'll even say you

know the content might be incorrect so

you can give it a thumbs up or a thumbs

down if you dislike the way that it

pulled this I personally didn't like

this because once again

I don't want to know my pricing like I

know the south is $1.99 but yeah but

it'll also tell you right here it'll say

here's the data Insight I found for you

the total items sold by prices are $4.99

with 144 367 items sold and then it runs

through how it made this but once again

this is data that just makes no sense to

me now you can quickly change the topic

and it'll kind of refresh or you can

keep asking it questions or ask more

insights like can I see another Insight

so if I click on it again can I see

another Insight it'll show me total item

sold increase over time so it's showing

me now the years and the total item sold

okay A little better this is something

that makes sense for me so it looks like

we are increasing in sales which is a

good thing not as much as I want maybe

but it's a good thing and I'll give it a

little check mark and say I really like

that now you'll see here that once you

click whether it's going to be the down

dislike or the up like you can then say

what did you like give it detail you can

include a screenshot as well or even

upload the file as long as you're you

know keeping compliance with your

company you don't want to upload just

your file if there's some data that's

confidential there you'll see share my

prompt and my generated responses so

Microsoft is definitely working to

improve co-pilot every single day and

the more feedback you give co-pilot the

more better it's just going to for us in

the future now once you're done with

this of course I won't submit this right

now you can click the little X and it'll

bring you back to your co-pilot preview

here you can say that you can add all

the insights to the Grid or once again

you can get another insight and you'll

see it's a little quicker this time now

it's telling me the frequency of total

items sold like you get another one now

it's telling me for products notebooks

the month in March has a noticeably

higher total item sold can see that in

March we sold a lot of notebooks

something I wouldn't be able to just

look at and tell right away so once

again this is pretty amazing technology

so take a moment and try some of these

prompts out on your own get some

insights and when we come back we're

going to talk about being able to really

visualize our data in a way that's

meaningful to

us always remember that you can change

the topic which means it's going to

erase everything else

that you've done they'll say okay let's

change topics what would you like to

talk about and then it'll go through

that same prompt before where you could

get Data Insights formulas or even

highlight filter or sorting the data but

let's say that we want to visualize this

data we know that if we go to show Data

Insights it'll work on it and it's

probably going to give us a visual that

might not make sense for us so go

through understanding our data almost

there and let's take a look at what it

comes up with

so at this point once it shows cases are

visual and you'll see total items sold

by prices again it sort of repeats it so

that change topics is kind of like a

reset button and you'll see that this

just definitely makes no sense right I

don't want to see the total item sold by

prices I want to see the total item sold

by product so what I'll do is I'll

actually put that in the prompt here

I'll say

please create a chart that

shows and then I'll name the columns and

you don't have to put it in quotes or

anything that's the best part about this

total items

sold per

products or you can say by products

whatever makes

sense and you can even tell it please

make it

a clustered

column and then I'll press enter so it's

going to go through it's going to read

my prompt I'm just simply telling it to

instead of total items by pricing create

total items by products and make it into

specifically a clustered column chart

and look at how amazing this is

immediately it creates it for us now

this is exactly what I wanted it tells

me not only I created a cluster column

chart and it'll show me what the highest

total items sold for binders was but it

also creates the chart for me now at

this point I can add this to a new sheet

by just clicking this button here add to

new sheet and you'll see it

creates a pivot table and then it

creates a pivot chart so when it's

talking about visualizations or it's

talking about anything type of table

it's typically talking about pivot

tables and pivot charts because of their

unique values and unique ways of working

now I have an awesome looking table an

awesome looking chart and they put it

all in a separate sheet now just because

I'm in a separate sheet doesn't mean my

co-pilot's not there still you'll notice

that over here yes we have pivot chart

Fields but right above it we have our

co-pilot so you can switch between the

two so I'm going to just switch back to

co-pilot and I'm taking a look at this

it says done I added total item sold by

product to sheet one and I can even go

back to the table and it switch me back

now at this point it's going to give you

more prompts so the more you put into

co-pilot the more it prompts you for

other ways to analyze your data for

instance it says you could get the

percentage of total total items sold

that comes from the division East or we

can ask something like which is the

division for months in February or

months with total item sold higher than

8 800 or 81,000 right that's the best

part now if I do ask it something like

please

create a pie

chart with

sales per

Division if I do something like that it

might not come back with the results I

want and the reason it's doing this is

because you're saying division but

you're also saying sales per Division

and on our table we don't have a header

called sales so it's probably not going

to understand what I mean by this and

it's thinking and actually like I said

there's always different results

sometimes things will work sometimes it

won't and let's see if it worked right

so yeah it looks like total items sold

by division C it is learning last time I

did this it actually didn't work and if

it doesn't work for you once again

remember that that this is randomized

outputs so sometimes they'll say hey

co-pilot can't do this that's okay just

rephrase your prompt and then it should

work so this looks pretty great and

let's say I created a pie chart by click

copy copy to the clipboard you could

actually paste that information in here

right and then just change this in now I

have the

highest total

sold so pretty cool

now I'm not done yet I still want to

continue with this so maybe I want to

see

the pie chart I'm going to add it to a

new sheet here and then you can do your

editing now you might be thinking well

is there a way to add to this pie chart

now right and you'll notice that if

you're on this sheet it's actually

clicked off of it your prompt area is gr

out because this prompt was created from

a table so even though I'm clicking on

the results I can't do any changes to

them now that's not to say if I go back

to my data that now it's available again

I can't say something like

please

add

percentages to my pie

chart if you don't Click Change topic it

knows what you're talking about so it's

going to keep a history of everything

you've asked it so far so let's take a

moment here to

let it do its

thing and here is a great example so it

says I'm sorry but I'm unable to add

percentages to the pie chart is there

anything else I can help you with so

there are certain limitations to what

co-pilot can do that doesn't mean I

can't go in and change this myself the

data is already here so you can always

go to the analyze tab or design Tab and

start to add those elements in there so

I'm going to add those elements I'll

probably add data

labels probably on the

outside and there it is and if I wanted

to change these to percentages just

double click to open up the format data

labels and instead of value you just

choose percentage so it almost got the

work done not fully for us but at the

same time it still looks great and then

of course you can change the design the

formatting whatever you like maybe you

don't like this design I'll make this

more of a blue color

well I don't want them all blue just

select one of

them I'll make that

one darker blue I'll make this

one

red let's go with a yellow let's go with

a lighter

green and there we go and if you wanted

to add the percentages once again you

can always change go back to design tab

layouts or you could click the little

plus icon and choose where you want the

data labels to go maybe I want them to

be in the

center and then also you know what I

want to do here I want to make

these

white except for this one this one yeah

that looks better something like that so

and maybe you want to make them a little

bigger whatever works but now we have an

awesome looking pie chart and even

though co-pilot couldn't do the work for

us it did most of

it the next thing we're going to talk

about is being able to highlight filter

and sort our data and if you're ever

wondering how to always remember you can

reset by clicking change topic reset to

the original Three promps so if you just

click on change topic it'll reset and

then you'll see show Data Insights

suggests the formula or how to highlight

filter and sort data and you can click

here and it'll give you a couple of

how-tos on filtering and sorting I like

filter to items that are due next week

or highlight the data and a couple other

things you can do as well but if you

actually want to sort you can actually

click on The View prompt and go to edit

and this is where you'll see sorting

highlighting and filtering and also

applying so these are the prompts to

actually do what we want we could say

that we want to sort this and you'll see

sort now it is important to note that

you can only sort in ascending or

descending order if I try to sort in any

other order uh for instance let's say I

want to sort this sort by

products in ascending

order that's going to work perfectly so

I'll go through its prompt make sure

it's looking at our

data and then we'll get the results

and there we go you can now see that

products say

binders then notebooks then paper clips

and then pens and it'll even tell you

that it's done and it's done that for

you but what happens if I want to sort

this in a specific order maybe I want to

sort it by

division right and I could say sort by

division but I want it in this

order let's do sort by

[Music]

division

north east

south west if I put in that prompt it's

actually going to return a result that

says it can't do it because co-pilot as

of right now can't do custom sorting so

let's give it a second to finish up

here and you'll see here it says I'm

sorry but I'm unable to sort the

division column in the custom order you

specified however I can sort the

division column in ascending or

descending order so right there it tells

me that we can't do custom orders yet do

I see this you know in the future of

course it's always going to update

remember I said it's a learning language

so it's going to start to learn more and

we should be able to do that sooner than

later now with that said the next thing

we're going to talk about is

highlighting and once again if you just

click on change topic you can go back to

the original prompts but anytime you ask

it to do something it comes up with

other prompts like how many products of

binders or are there any outliers in my

data so that's a really great thing that

gives you just prompts that you might

have not thought of but let's go back to

change

topic the keyword prompt for highlight

is going to be highlight so if you click

on how can I highlight it'll tell you

you can say highlight data try saying

bold the top 10 values in the sales

column or I can click on view prompts go

to edit and choose highlight the and

then you'll see that it automatically

puts that prompt in or you could just

use regular English and say something

like

please

highlight

the let's do top toal top 10 we'll say

top 10 total items

sold in a

yellow something like

that so hopefully we'll see this work

very nicely where it's going to

highlight the top 10 total item sold so

this is the column that we

want and it'll do it in a yellow

color and there we go it's actually

starting to work right now it'll say

done

I applied a yellow fill color and a

black font color to the top 10 items in

the column total item sold so I can now

go through and see that that conditional

formatting was done for me now what's

really cool about this is even though

co-pilot did this for me if you want to

see the rule you can always just go back

to conditional formatting and go to

manage rules and you'll see it's in

there now so it's applying this top

pen and it's applying this format so

that's a really cool thing to see that

we didn't even have to do this

artificial intelligence did so with that

said since co-pilot was so good at doing

this um let's just clear that out you

can either go to conditional formatting

clear rules and clear from entire sheet

but I'm very curious to see if it'll

actually clear it clear e

yellow

color highlight or clear the

Highlight see if it will do that one

now remember it is keeping track of the

ones before but if you did close out

this file and open it up again well then

it's going to continue a whole new

session for

co-pilot and it's finishing up let's see

if it's going to apply it done I cleared

the formatting on the top 10 items and

the columns so it looks like it still

didn't do it it says that it did but it

didn't and this is once again one of

those examples of how it's not fully

there right the capabilities are great

but it doesn't always work in our favor

so I would say that this is incorrect I

actually didn't clear the formatting so

I'll put a little down arrow and I'll

just tell

it

didn't do what I

asked but set it

did and then I include a screenshot if I

wanted

to and then I can say share my prompt

and generate a response relative to this

and then may we contact you and then I

could

submit so that's pretty cool once again

this is something that I would do

feedback always helps to generate a

better application later on but as we're

clearing this formatting once again we

could just clear it ourselves clear from

entire sheet and it's back to normal

last thing I want to showcase is

filtering so you can either just start

by saying filter for this or I can say

change topic ICS and go back to the

prompts so if you want to go back to

prompts you always can but in this one

I'm not going to I'm going to say please

filter for the month of

January and let's see what it comes up

with so we have our months it should be

smart enough to see that in our data and

look how cool that is immediately we see

and it'll even tell us done I applied a

filter on month column to show only

things equal to January so this is

working immediately for us now that we

have this we can always just use the

same thing where we click the little

drop down and clear the filter but I am

curious once again to see if it will

clear the

filters clear all filters on

table and it's always fun to just play

around see what works what doesn't work

and just see this technology

grow so we'll give it some time once

again it's almost there let's see if it

returns our

data and there it goes that one actually

worked really well and let's see if

he'll say I removed the filter on the

table is there anything else you would

like me to do so since we made this a

table this is actually a good point to

look at it does say table one it is a

good idea to click on your table and

rename it that way you just have a

better way of calling out to it and

referencing it but yeah it's not

necessary but I just think it'd be nice

to

do the next thing we're going to be

talking about is how we can have

co-pilot create formulas for US based

off of colums that already exist for

instance we have here our total item

sold and we also have our prices and

maybe what I want to see is what is the

profit from that now we know that we can

do that by taking the total item sold

and multiplying it by pricing so I'm

going to go over here and ask a question

and ask to please

create a new

column called

profit by

taking

items and

prices and multiplying them

so we're going to take a moment and see

that

work and we'll see what it comes back as

the

result and there we go so when you're

asking fors to create a column it'll

calculate the profit for each row in the

table by multiplying the total item sold

you'll notice I only said items you

don't have to actually write total item

sold

it knew what I was talking about and

it's going to multiply it by the

respective prices it'll even show you

what that looks like and it's naming the

entire column by header so at total item

sold this multiplied by prices

here if you need to explain the formula

a little more you can click the little

carrot icon and it'll even showcase it

again calculates profit it tells you the

following formula multiplies the value

by the value and prices and then it

formats the results as a currency value

with a th separator and two decimal

places and any negative values would be

displayed as red we don't have any

negative values so that's great but now

what we can do is insert the column and

just by hovering over you'll notice that

right next to prices now we see profit

which is pretty amazing now I can click

insert

column and there we go it says done I

inserted column to column G and named it

profit for us the one thing I might do

is just select these

and merge and center again that way I

can extend the line but this looks

amazing now I can quickly

see that it works and I can even get

insights based off of this column now

for instance I can ask a question saying

what is my

top profit

and it'll tell me what my top profit was

which is binders from our May sales in

2023 North it'll tell us that it's

44810 or 8.40

and4 and that looks awesome we can even

create more columns as well let's say I

want to change the topic again to go

back to the

original I can show a suggestion for a

column

and let's see what it comes up

with so it's saying right here that what

we could do is calculate the profit

margin and we can do that for each row

by

taking dividing the profit by the

product of total items sold and then

their prices as well so once again it's

going to show us the formula if we need

to explain it a little more it will and

then we can insert the column and of

course it's going to be at 100% because

there's nothing that we didn't have a

percentage wise so this works beautiful

now I can decide not to do this because

I don't think it works for me I I know

that everything is sold at 100% so I

think we're pretty good but I can say

that maybe I want to

create a

projection projected let's say

um sales projections or

profit

projections by taking

profits and increasing

them by

3% so create profit projection

column and it's going to take the

profits and increase them by 3% and

let's see if they can do that and once

again it's going to work on

it it'll go

through I know it's working because it's

saying that it's generating the formula

column

and typically when it gets this far it

means it's going to work if it does

return like a result that says it can't

do it no worries and look at this it's

actually going to take it by a 3%

increase which would be 1.03 and that's

what it's going to multiply by and we

can insert that and that's what our

profit projections would look

like and there we go now if something

like this happens just because of

formatting you could just use the format

painter

and that looks good

again once again merge and

center merge and

center and now we have an expanded table

so adding formulas can be very important

and it's super easy to do it now we

don't even need to know how to write

formulas or what the syntax is for Excel

formula writing co-pilot can do it for

us so we've seen how amazing co-pilot

can be with its prompts but let's

actually start to explore some different

types of ways we can analyze our data

let's say for instance that I just want

to ask at a simple question like how

much

profit did we make and let's say

June of

2022 I'll ask it a simple question just

going to go through give it a second to

work

so how much profit did we make in June

of

2022 once again this is you know

co-pilot in its beginning stages so it's

starting to create my information

sometimes it takes a little longer

depending on internet and bandwidth as

well but you'll see here it tells me

that the sum of

profit the year 2022

132 642 and it's telling me in June of

2022 that was what the profit was so it

created a pivot table now you can add

this right away to a new sheet just like

this and you'll see it's now actually

doing years and it's filtering for 2022

doing months and filtering so I love the

fact that they do this and let's just

click go back to table now at this point

as always whenever you ask co-pilot to

do something it starts to come up with

topics that are a little relevant to

what we're talking about so for instance

what is the products for month July or

percentage of total profit projected for

each product so let's take a look at

that so we're going to see the

percentage of total profit projection

for each product which I'm assuming is

going to be about 100% on each we'll see

what it comes up with it's finishing up

so that's good news and then once again

even though it suggested this so this is

a really good detail to Showcase in this

training is that even though it

suggested it itself and I clicked on

their suggestion for the prompt it told

me that they couldn't do it so that's

the little bit of Downfall with co-pilot

is you might get prompted to ask for

something and it won't work for instance

let's take a look at another one that it

prompted now it's asking us percentage

of total profit for each product let's

see if that's going to work so we'll

give that a

click and I can foresee in the future of

the

I mean it's continuous learning so it's

going to continue to learn and

understand the things that we're asking

it and look right here we now get a nice

little chart bar chart it tells us the

highest profit by products it'll even

tell you that binders have the highest

profit with not books being followed and

it'll even create a pivot chart to get

the answer for that and then let's keep

going with this let's say that I want to

ask it something else let's

do

this did I make more

profit in let's say

January months or April months let's see

if I can figure this one out now as I

said throughout this training have fun

with this explore you can write whatever

you want there's no right or wrongs here

we're going to take the time right now

to explore some things that will work

and some things that won't and once

again it is going to be different for

everyone's outputs but it looks like

this is looking pretty great it says you

made more profit in April with a total

of

215,000 as opposed to January where we

made 143 so and there it is it creates a

little pivot table for us and we can

continue to add those to new sheets but

let's keep going uh total profit by

division as a column chart or we can

show total item sold by years as a line

chart that might be something nice or

distribution of division by months let's

do that one let's see if that's going to

be something that comes

through and once again this is just

amazing technology here sometimes it's

going to work sometimes it won't but

it's going to make it easier for us to

create different content and analyze our

data

differently look how awesome this one is

here's the distribution of division by

month East has the highest number of

rows in June with nine followed by West

in March with nine and they created a

query result to get the answer so this

looks great and I can add it to a new

sheet and let's see what they came up

with and there we go so it's counting

the rows of Divisions and how many

entries were in each row so this is

something that is amazing now at this

point I'm going to go back to the table

again and what we're going to be talking

about is the co-pilot lab next up but

before we get into the co-pilot lab part

let's actually click into view prompt

and take a look at some of these as well

let's do this let's say that we want

to add a column that calculates the

profit per order okay that sounds like

something pretty cool so let's see if we

can do

it

profits

per item

sold and we'll see what it does

here it might return something that just

makes no sense add a column that

calculates profit per the item sold so

we can see if the price was $299 so we

should get the same prices as the new

column

now and there we go it looks like it's

doing price and if we enter so it's

almost there it's it's got a little bit

of an incorrect there so it's given us

209 but let's view what it's doing here

it's telling us that and it's just going

to explain the formula but it's doing a

70% so it's calculating multiplies the

value and prices column it's taking our

prices column and it's multiplying that

by 70% to find the profit per item so I

don't know why I would do something like

that I don't think that's going to be

something we need we could have just

taken the profit column divided by the

total item sold and got the price

because that's what it's multiplied to

be but as I said it's a learning process

so for this one I'm not going to say

anything but let's keep going let's do

uh how much should we make in total

profit how

much did we make in

total

profits so now it's going to

do c ation where it should take the

profits all of them and sum them

together or do an auto

sum once again we'll see if that works

then it does sum of profit it says the

total profit made is

1,733 15 right so that looks great but

let's keep going with this how much

should I make total for each

year how much

profit did I make total

for each

year so what I'm assuming they're going

to do is probably create a pivot table

and then they will filter for each year

so they'll do 20 I think we have 2020

2021 2022 and

2023 and there it is they actually

created a line chart instead which is

pretty awesome still and we can clearly

see and then it gives us the answers

here so once again you can copy this

information put it into report if you

wanted to you don't have to just simply

stare at the chart here you can actually

take the information and copy it maybe

put it into a report put it into one

note whatever you

like Okay so this looks really great now

what I want to do is change the topic

I'm going to just take it all back to

the beginning which means it's not going

to feed off of this so when we ask it a

question it keeps feeding into those

questions and making things that are

relevant for us relevant prompts but if

we choose to change topic it goes back

and it erases all that history of data

which is good to understand otherwise if

you click this by accident you're

wondering why it's not giving you those

other requests like it did here those

other prompts it's because of the fact

that we've changed the topic to put it

back to its original state all right so

try out a couple of these on your own

with this data have fun with it ask

questions that we didn't even ask here

and see what you come up

with we've seen co-pilot pain in our

application here but we can actually go

even further with

co-pilot if you'll see here the little

view prompts area we can click on that

little book icon and then once we click

on it it brings us to those prompts that

I showed you before but we can click on

view more prompts and it's going to open

up this dialogue box and it's going to

show us some of the prompts from our

co-pilot lab now co-pilot lab offers a

collection of prompts that will teach

users how to create learn and use

co-pilot and the co-pilot prompts are

the instructions or questions that

you're going to use to tell co-pilot

what you want to do you'll see here some

of the ones that they have pre-created

for us and we can even save those

prompts by clicking on the

little save icon or tag and then you'll

see all your saved prompts by clicking

here you can also go through the

different categories create understand

stand or edit and filter through them

but if you truly want to get the most

out of co-pilot lab you can see all the

prompts in your co-pilot lab here and

when you click on this this is where

it's going to bring you to that co-pilot

lab in your web

browser now that we're in co-pilot lab

this is where we can explore the in

products so where co-pilot exists for

example

365 word teams Outlook PowerPoint Excel

even in OneNote or Windows Loop

whiteboard or forms you'll see all the

prompts to try and remember that

co-pilot lab is here to teach us what

sort of prompts

exist now in the prompts to try we can

save prompts by clicking the little

ribbon icon and we can quickly save a

prompt so if I want to save this one

just click it and now I have all my Sav

prps here so that's a nice little flag

system right next to it you'll see we

can filter for whatever app we're using

this for whether we want only Excel or

word or even Outlook prompts and then

what sort of category do you want what

prompt do you want to create something

or edit it or understand it ask

questions about it or even just catch up

and get

summaries you'll notice that when we're

looking at the prompts it'll explain

what the prompt is a nice little

category what's new and then you can see

an example of what you would write for

the prompt you'll see in the bottom left

hand corner what the prompt is coming

from so which application in this case

this would be Microsoft

365 this one's a one note prompt this

one's an Outlook prompt a loop prompt

you can go through and find

Powerpoints and this is also what's

really cool about this system is that

you're learning right so if you're like

how do I add an image to a slide it'll

show case add an image of and then you

can say for example a puppy dog to the

slide so if you don't remember that you

can always just save this and then use

them later on in your applications once

you're there and there's so many

different ones like that shape that's a

pretty cool one Microsoft pre-created a

bunch of these for us we can click on

show

more show

more and there's just so many different

ones now once you go through the

different prompts and you choose which

ones you want to save and use inside of

co-pilot in those applications you can

then go back to the app and start using

them you'll see down here we do have a

couple of articles for quick tips for

better prompts so summarize this email

in two bullet points you'll see that

you're giving more detail and you're

specifying that you want two bullet

points that's something that we're going

to learn about for co-pilot is that we

need to really be specific on what we're

asking it because well it's still a

computer right we're still talking to

computer

language underneath it we have FAQs what

is co-pilot prompt or what can I get

done with co-pilot how can I get the

best responses or

results you'll see a bunch of different

articles

here now going back to the

top you can go into the get started and

there's so many different ones they have

for home users Business Leaders or for

admins let's say that I'm a business

leader and I click into that if you

notice the servic is unavailable right

now so these are services that are

starting to come out slowly there are

slow roll outs so just you know be

patient with

it you'll see this one's still

unavailable and I believe admins is

unavailable too oh no they finally got

the admins up so so just wait for the

Business Leaders that should be coming

out soon and then we'll see here

Microsoft co-pilot 365

documentation get ready for co-pilot

with 365 get started with manage it get

trained on they have so many different

resources for you to take a look at and

utilize another thing that's really

important with co-pilot that I always

say is the plugins this is going to plug

into different apps and Integrations not

only with just Microsoft with other

Technologies as well so take a look at

some of the things that are coming up

and once again enduser co-pilot resource

co-pilot lab is one of those

resources this lab is where you meet

co-pilot you understand what it can do

you can see it in everyday

apps and those are once again those

prompt and you can click the little save

prompt so this is more like that main

page here this is landing page for

copilot lab but it's pretty much the

same if you go to prompts to try it'll

just bring us back to where we

were what's new that's always something

that's important I'm always reading up

on the latest updates for Microsoft

co-pilot so you'll see here prompts of

the month what were the most important

ones or what were things that weren't

working that they've now updated

transform a doc into a presentation

that's a really cool one and you can

upload document files actually talk

about this in one of our other classes

when we get into using co-pilot with

word and also using it with

PowerPoint but as you can see here

improvements based on our feedback

before we talked about how you can

submit feedback it is important remember

this technology is Young still so we

need to really Express what's working or

not working for us to make sure that

co-pilot can evolve into the tool that

we need to you can take a look at some

of the previous updates as well our

February updates January December just

click on the little carrot icon you'll

see all of them and I just love the way

that they display like all of the icons

you know that this is specifically for

word it tells you you know what it's for

we're actually going to be talking about

word for the web so transform your text

into a table this looks like it's only

available for word for the web so not

for the desktop app that's another thing

that's really important when using

copilot there's going to be different

things that work in the online version

versus the desktop

version and there we go so always

remember that this is still co-pilot lab

we can have all the same tabs up top we

can switch between prompts to try

getting started remember for business

leader is one that's not available right

now but it will be rolled out sooner

than

later and there you

go we've seen co-pilots capabilities

inside of Excel for desktop we've even

went into the co-pilot lab and saw all

the different prompts that we have

access to what we're going to do next is

Showcase how we can access the online

version of excel so in order to do that

we do need to sign into our 365 account

so I'm going to go grab my 365 account

and we're going to then be able to take

a look at co-pilot for Excel

online so now that I have my web browser

open I'm going to just go to

office.com sign in with your credentials

and that will bring you to your 365 now

as you can see here we do have co-pilot

for 365 we're actually going to talk

about that in another video but for now

let's just go to all of our apps and

what we're going to do is head over to

Excel for

online now at this point I'm going to

just open up one of the ones we've been

working on that's sales data the same

one because remember in order for a

co-pilot to work for Excel it has to be

a table the data and it also has to be

saved to R 365 or one drive online

account so now that I have it here I can

quickly just open it up in the online

version now there are a couple

differences in the way that it looks of

course because this is the online

version of excel but everything works

the same way so I have my data here I

can click on co-pilot and it'll still

open up that co-pilot paint you'll

notice that the icon looks a little

different but everything else is the

same we can view prompts create

understand edit or ask

questions we we can also if we click on

to like add columns or we want to see

more prompts we can go over to the lab

so let's

say learn more about

co-pilot we can open it up and then

we're back in the lab

here also remember that we can change

topic to reset this and then it works

the same way that we saw before let's

ask it a question about our data let's

say how much should we we make in pen

sales and it's going to go through the

same way that we've been doing this in

the

desktop and there it is so it tells us

that our profit for products pens was

$358,500

16 and here's a pivot table I created to

get the answer we can automatically add

that to a new

sheet and there it is

all right so online version you can use

co-pilot desktop version you can use

co-pilot you can access a lab co-pilot

lab from both of those everything's the

same there's not a real difference

between a desktop version for co-pilot

and the online version now if you do

notice like I showed you before with the

labs that there is something that says

it is only pertaining to the web version

well then you know that that prompt will

soon carry over but for now it's only

for the web version I haven't found too

many of those for Excel but yeah just

explore and have a lot of fun with

this as you can see co-pilot is so

amazing and there's so much you can do

with it I hope you all enjoyed this

course and I'll see you all next time

thanks hello and welcome to excel power

user you're learning with Mo Jones IT

professional and educator for our power

user course it's all about conditional

functions so we'll take a look at

conditional functions that can summarize

or aggregate data for us we'll take a

look at lookup functions in particular

the X lookup function which replaces

vlookup hookup and index and match we'll

also take a look at text functions very

nice functions for cleaning up your data

or even to improve your data set as well

we'll also take a look at Advanced pivot

tables so what are some other tools that

are available to us after inserting a

pivot table creating a pivot chart and

filtering our data as well that's what

we'll be covering in module one of our

course

today for module two we have another set

of objectives we'll be taking a look at

some useful array functions

we'll take a look at Advanced

conditional formatting we can create our

own conditional formatting based on

formulas we'll also take a look at

Advanced charts then we'll finish up by

creating some macros by turning on the

developer mode so go ahead and open up

the practice file for module one and

come right back and we'll dive

in I've opened up my power user module

one file here and let's go ahead and

take a look at our very first worksheet

so our very first worksheet we'll be

covering the IF

function and the IF function is

basically the foundation for our logical

functions so in preparation for our

database functions we want to make sure

that we understand the IF function so

for our functions we're going to be

inserting the function you can either

type the function here in the

cell we can also type it in the formula

bar if we want to but we're going to be

inserting it the traditional way from

the formulas Tab and clicking on the

insert function dialogue here as well we

can also press shift plus F3 to get that

done now in order to understand how this

works let's take a look at the IF

function and its most basic form in this

case we have some sales figures for the

month of February a few sales

representatives here for example for S

Sam we're tracking the week sales Week 1

9550 week 2

9230 week 3 8500 and week

48965 so in the end the totals for S Sam

for the month of February is 36,24120

and i12 we would say something like well

is the value in

F5 greater than or equal to the value

that is an

i12 so that's our logical test Excel

will compare the values in those two

cells and give us either true or false

but in this case if it's true we just

want to go ahead and say yes so

obviously we asked the question here did

Sam meet the monthly goal the answer is

yes because

36245 is greater than or equal to

34,000 let's go ahead and express that

so that Excel can understand our logical

test I'll go ahead and click on Cell

H5 and I'm going to insert my function

so I'll choose H5 here I'll go ahead and

click on the formulas tab I'll go ahead

and insert my

function you can also press shift F3

I'll go ahead and type the IF function

at the top here I'll press go and then

I'll press

okay here's my function arguments

dialogue box tells us what the function

is what the function does and for each

argument what is actually asking us for

so we can see that this function needs

three things we need to introduce our

logical

test optionally we

can specify what should be displayed in

the cell if the value is true or if it's

false notice how these are not bold as

the first argument so these two are

optional because by default they're

going to return either true or false

well let's go ahead and set this

up so our logical test we're concerned

about two cells we concerned about cell

F 5 that has s Sam sale we want to see

if it's greater than or equal to the

value that is

in

i12 Excel has already determined that

the value here is greater than the value

that's in here therefore it's already

telling us that it's

true but we don't want it to say true so

if it's true we'll just simply say yes

yes and

quotes if it's

false we'll simply say no here's our

answer it's already telling us it's

going to be a yes I'll go ahead and

press okay and we should be good to

go so there it is we have our yes now we

want to be able to autofill down so if

we grab our fill handle here we can fill

down and end answer the rest of the

questions here as well so what I want to

do is I want to go ahead and absolute

reference my monthly goal that is in

cell i12 so to do that I'm going to

rewrite it this way it's going to be F5

is it greater than or equal to dollar

sign I dollar sign 12 so this way when

we write our

formula the values in column F will move

down row by row but we're going to go

ahead and lock on to the monthly

goal so I can just go back to my

original formula here in the formula bar

or if I double click on the cell I can

see the formula and I can go ahead and

right here I can just go ahead and press

the F4 key on my keyboard and that will

absolute reference the i12 for me so

I'll press

F4 there we go and now I can just go

ahead and press

enter I'm good to go

if I autofill

down now as we take a look if I double

click on that second cell I can see it's

moving down in column F but it's still

locking on to

i12 if I double click on the next one

here moving down as expected in column F

but we're still locking on to i12 so go

ahead and write your IF function for

column H answer the question was the

gold net

you should get three Nos and three sells

with yes and come right

back now that we've answered the

question what's the goal met for each of

our sales reps let's move on to column I

we're going to go ahead and calculate

the bonus if our sales rep met the

monthly goal so we do have a bonus

opportunity here of 6% of the sales if

the goal was met

so in this case s Sam did meet the goal

so what we're going to do is run the

calculation we're going to multiply

the value that is in cell F5 *

6% so we're simply going to

say if the value is true we'll say

F5 times the value that is in I13

we can use the same statement as before

so for

example we can use our same conditional

test here if the value in F5 is greater

than or equal to

i12 instead of saying yes though this is

where we're going to perform our

calculation so instead of saying yes

we're going to perform our calculation

we'll say

F5 times the value that is in I

13 so that's the only change that we

need to make here as well so let's go

ahead and set this up I'll go ahead and

click on Cell I5 I'll go ahead and

insert my IF function

again it's already up here so I'll just

go ahead and press okay again I can use

the same test I could have even copied

the formula here as well but I'll go

ahead and write this over

so F5 is a greater than or equal to the

value that is in i12 since I'm right

here I want to go ahead and highlight

the i12 and press the F4 key on my

keyboard that will absolute reference it

for me now the value if it's true

remember we want to perform a

calculation so I'll multiply the value

in F5 times the value that is

in

I13 I am going to Absolute reference i3

because I want to be able to fill down

my formula so I'll press the F4 key on

my keyboard again and there we go we can

see it's already giving us the

calculation because this scenario is

true it's already performed the

calculation for us so we should be

getting a 2175 bonus for S Sam if it's

false I'll just say something like na

and quotes and we're good to go so just

to recap our logical test is the same as

before is the value in cell

F5 greater than or equal to the value

that is in i12 so in this case because

it's true we're performing the

calculation I'll go ahead and press

okay and now I can go ahead and autofill

down because I have values to the left I

can double click on the fill handle here

and it will copy it down for me so there

you go I'm good to go with my IF

function here so the IF function we can

either say true or false we can have our

custom text or we can perform a

calculation as well now this is dynamic

so if I were to give Sandberg the extra

dollar here to meet the monthly goal

I'll go ahead and change the value here

and notice how this will change to yes

and we'll also get the bonus status

updated right here as well so because

we're using the cell reference

everything is dynamic

so now all we need to do is just input

values

here or actually over

here so for each month as these cells

are

populated these two columns will be

updated automatically as well I'll give

Sandberg the extra dollar so

34,000 I'll press

enter and there we go right go ahead and

write your second IF function to

calculate the bonus status and I'm right

back let's go ahead and write one more

function to complete this worksheet here

so we have one

more opportunity here in cell K5 so even

though you did not meet the monthly goal

we still have an opportunity for a

department bonus of 1K so everyone gets

1K but it's based on two conditions so

the first condition is that the total

sales needs to be greater than or equal

to

200,000 and then the average sales

here needs to be greater than or equal

to

40,000 so we can see that one is true so

we're already at 213 so this is true

however this is

false so we need to go ahead and write a

function to determine whether both

conditions are true the keyword here is

and there's actually two ways we can do

this we can either write a single and

function the and function will just give

us the default true or

false if we want to go ahead and write a

custom message however such as you know

great job team or keep trying we would

need to Nest the and function inside of

our IF function well let's go ahead and

write our and function first and then

we'll just go ahead and Nest that in in

our IF function so we're using the and

function because the IF function only

allows for one logical

test so if I open up my if statement

here I can see it only allows for one

logical

test but what about the end function

I'll click on the insert function here

I'll go ahead and insert my and function

I'll press go and I'll press okay so

here's my end function and as you can

see it provides opportunities for more

than one logical test we can actually go

up to

255 logical tests as well well let's go

ahead and enter this so our first

logical test we're concerned about the

value that is in cell F12 is that

greater than or equal to the value that

is

in l12 so

213,000 is greater than or equal to

200,000

so this first part is

true now logical test

two I'll go and click here is concerned

with the average

sales so is that greater than or equal

to the value that is in cell

l14 so we can see that that is not the

case

35,000 602 is not greater than or equal

to 40,000 so we end up having one that's

true and one that's false now the N

function both need to be true so because

one is false we end up with false here

as well so if I press okay this gives me

a simple answer letting me know that

well it's false right we're not going to

get that department bonus of

1K but what if I wanted to write

something custom in here such as you

know maybe if

the department gets a bonus we could say

something like great job

team or we can just say something like

keep

trying as a department well to do that

we would need to Nest the IF function so

look what I'm going to do I already have

my and function here that's giving me

the

result so I'll just go ahead and copy my

and

function so I'll copy that to the

clipboard I'll press escape to get away

from my formula bar here and maybe over

here create a little space for

my function so I'm going to insert the

IF

function okay so my IF function is only

concerned with either a true or false

value well remember my n function that I

just wrote is giving me either true or

false so I'm going to go ahead and copy

and

paste the end function in here right so

my logical test is the result of my n

function and we can see that it's giving

me false so mission

accomplished so now if it's true I can

say great job

team and if it's false then you know we

can say keep trying

as you can see in this case our n

function is returning false for us

because both conditions are not met so

therefore the result will be to keep

trying now one of the cool things here

now that we have the function arguments

Box open to simplify things what I can

do take a look at the formula bar is I

can go back and forth between the IF

function arguments box and the and

function arguments box so if I click on

the and function here notice how it's

switch over to my and function so this

is just a

little tip helps us when we're nesting

functions here now I can toggle back and

forth between if and and I'm good to go

I'll go ahead and press

okay there is my answer now remember all

of this is dynamically

linked so if these values were to

change once we get to 40,000 average

sales or more our message here will be

updated as well go ahead and write your

and function and go ahead and Nest your

and function inside of your IF function

as well and come right

back on this worksheet let's take a look

at several variations of the IF function

in particular count if sum if average if

and sum ifs let's take a look at our

expense report we have some expenses

from rows six down to row 50 if I scroll

down it goes down to row 50 here so we

have the date of the expense we have the

office either Uptown Midtown or downtown

we also have the actual expense so

technical support communication office

supplies and transportation and so forth

we're also tracking the amount so for

example on July 1st we had our first

expense it was for the Uptown office for

technical support and the amount of

$747 the first thing we want to do is we

want to go ahead and highlight all of

our Tech supports here as well so how

many expenses did we have for tech

support well here's one and if we go

down here here's here's

two and here's

three and so forth and so on so what

we're actually doing is we're taking

this input value here so we want to

count how many expenses did we have for

tech support we're taking this value and

we're searching the expense

range and we're counting how many times

tech support actually appears in there

so if you think about it what we're

doing is we need a

range we need a

criteria and that's it we already have

that information so the range that we

want to search well that range is from

C6 down to

C50 and our criteria well our criteria

we can either type technical support in

there or we can refer to cell

G10 we want to use the cell reference

because if we change Tech Support to

another expense we can get a real time

update in terms of how

many times that expense was logged as

well but the first thing we want to do

let's go ahead and name our ranges so

instead of highlighting you know C6 all

the way down to C C50 every time we want

to run some type of function fun on here

let's go ahead and name the range I'm

going to go ahead and highlight the

entire range and right up here in the

name box I'm going to go ahead and give

this a name so for the office expense

range I'll call this

expense or

expenses so

notice I've highlighted the entire range

and question and I'm typing the name of

the range right here in the name box

well I'll go ahead and press

enter and now I have a range I'll put

the name right here so I can remember

what it is I call that range

expenses going to do the same thing here

well now what I can do I'll go ahead and

click on the drop down and I can see I

have some ranges in here notice we have

some table ranges we'll take a look at

this in just a little bit if I click on

that expenses range notice how it

highlights this range for me

so from now on when I refer to expenses

it's going to refer to C6 down to

C50 well we'll see how this will help

for example if I want to go ahead and

summary and maybe

count amount of expenses in that range

well now I can come in here and I can

use the countif function so I can insert

my counter function

and as I mentioned before it's going to

ask you for the range and for the

criteria well for the range I'm just

going to type expenses in here and

notice how it's giving us a preview of

the actual range so tech support

communication office supplies my

criteria I can either type technical

support in

here and quotes and it tells me that

there were six entries for Tex

support but I want to use the cell

reference instead so I'll go ahead and

refer to cell

G10 so I'll go ahead and just click on

Cell G10 in

here so whatever

value me delete that there you go so my

range is now expenses AKA C6 through C50

my criteria is tech support AKA cell G10

if I press okay

okay I'm notified right away that I have

six expenses for tech support now I can

change this to kind of give it a test

maybe I'll go ahead and check to see how

many times did we have meal expenses

locked so I'll type meal in here and

I'll press enter and we had seven so

that is what the countif function does

but combining that with naming the

ranges helps us to work more efficiently

go ahead and name the office range

so B6 to

b50 go ahead and name the expenses range

C6 to C50 go ahead and name the amount

range which will be D6 to D50 go ahead

and enter your count of function give it

a test and come right

back let's go ahead and answer our next

question here so what were the total

expenses for office

supplies so in order to answer this

question we need to find all of our

entries here for office supplies go

ahead and locate the amount and the

amount column and add that to the

running total so if we were to do this

consistently we would actually get our

answer right so at least in my view

right now here are at least the very

first

three entries for office supplies we add

these numbers together

and that is what the sum IF function

will do so let's take a look at the sum

IF function now we already know we're

going to be asked for the range and the

criteria we already have the information

that we need I'll go ahead and click on

this cell for

ag14 and I will go ahead and insert the

sum IF

function so here's some if and again

what's the range R well the range is

going to be my

expenses so I'll type expenses in

here what's my criteria well it's office

supplies but I want to use the cell

reference here which is

G14 and then the third one what is the

range that I want to summarize well I

want to summarize the match that's found

in the amount column I did name that as

amount so I'll go ahead and type amount

in here

and there we go it's already giving us

our answer the total for office supplies

is

392 right I'll go ahead and press

okay and there we go all right just need

to go ahead and give this the

accounting

or currency format I'll give this

currency and press okay and there's our

number format in here as well so this

works well because I already have my

ranges named so here's my amount range I

can see all of my numbers under the

amount column are

selected my

expenses and then my office as well now

we can repeat the same thing in order

for us to calculate the average cost per

meal it's going to be the same

thing the expense range our criter IIA

is meal and we want to run the numbers

on the amount column so I'll go ahead

and this time I'll insert the average IF

function this will give me the average

per meal expense so I'll search for

average

if I'll press go and I'll press

okay so same thing

range expense meal so range is going to

be expenses

again my criteria are the meals so I'll

click

here and the range that I want to run

the numbers on well the

only range is the amount range so I'll

type

amount and there you go so the average

meal expense is

$594 so I'll go ahead and press

okay again I'll give this

the currency format I'll just use the

format painter this time and there we go

so three down one to go but as we can

see it's always asking us for the same

thing what is the range what is the

criteria and what is the column of

interest that you want to run your

numbers on the function takes care of

everything for us in the background a

lot of calculations that we cannot see

that we're not privy to always we're

asked to do is just point to the

different range and what is the criteria

as well now what I can do because we're

using the cell reference what is the

average expense for let's say office

supplies so I can type office supplies

in

here and it gives me the answer right

away so 650 and

33 go ahead and write your sum if and

average if go ahead and change the

values and get some real time updates

here as well it's a really nice way to

get some insight on

a data set come right

back for our last example here we have a

unique situation so in this case we want

to summarize what are the total for the

meal expense in the Midtown office so

now we have two ranges office and

expense and we have two criterias the

office needs to be Midtown and the

expense needs to be male now the sum if

only allows room for

one criteria and range so we need to use

another function that will allow us to

have multiple ranges and multiple

criteria and that is some

ifs I'll go ahead and click on Cell G20

23 and I'll click on the formulas tab

let's go ahead and insert our sum

ifs function here I'll press go I'll go

ahead and press okay there's also an

average ifs that allows us to do the

same

thing in this case the very first thing

it wants our function arguments here it

wants us to specify the sum range now

remember our sum range is going to be

the amount column

so let's go ahead and type amount in

here so I'll type

amount there we go here's a preview of

the amount array 747 907

540 and now we can enter criteria range

one so criteria range one is the office

and the criteria is

Midtown so criteria range one we need to

search the office column here so I'll

type office in here

I know it's locked on to the right range

here's the preview of the range criteria

one is going to be

Midtown so so far we're summarizing the

amount column where the office is

Midtown now we have criteria range two

so I'll click on criteria range two in

this case the expense column needs to

return the meal expense

so I'll type expenses in

here and the criteria two needs to be

meal so I'll click on

meal and there you go so basically we're

summarizing the amount

column the office needs to be

Midtown and the expense needs to be

meal here's our answer 3310 I'll go

ahead and press okay and let's just give

it a Qui Quick Test we can change the

office and the expense and let's see

what we get

here I'll also apply the accounting

format or the currency format rather to

here and we're good to go so now we've

created a really nice Dynamic worksheet

and it's very typical sometimes this

worksheet will live on another worksheet

Tab while the data is being entered in

here as well so again this is

dynamically linked to this data set so

any

changes to our data set here is going to

update our little worksheet over here as

well well I'll do one more thing here

I'm going to go ahead and change this

let's change this for what are the total

expenses for let's say communication in

the Uptown office so I'll change this to

Uptown and change this to communication

and there we go so all matches for

Uptown communication we have

2,545 so those are our database

functions let's head on over to our

xlookup worksheet tab xlup is a really

nice function basically it allows us to

do several things one of the things that

allows us to do is to return a value

from a

database or return different parts of a

record from a

database let's see how this works we

have a kiosk here and we're basically

inputting the employee ID

number now after we input the employee

ID number Excel is going to head on over

to the employee ID column here and it's

going to find that employee ID number

once it finds an employee ID number in

that column we can return the last name

first name department and the hire date

and they will all be displayed in our

kiosk right here so this way if someone

ask you what is the higher date for

employee with the number

1054 we don't even have to see this

database we we don't have to come and

scroll through it to look for that

employee ID we simply just need to set

up our X lookup so that it does the work

for us if I change this to

1368 we can see that

1368 it's down here this is the record

for Tammy

Woo and as expected it's returning the

last name first name department and the

higher date as well

this is a really nice way as well to

just automatically populate maybe a

form with using X lookup So based on the

value that you input in one cell it's

going to return the

corresponding field for you here as well

so that is basically how X lookup works

if you've used other functions like

index and match or vlookup you know that

they have prerequisites with X lookup we

don't really have any prerequisites here

as well let's go ahead and enter our X

lookup I'll go ahead and delete my

formulas in

here let's go ahead and enter our X

lookup let's return the last name first

so I click on Cell

B4 click on my formulas tab here's my

insert function I'll go ahead and look

for X look

up I'll press go here's X lookup I'll go

ahead and press okay so now with X

lookup again the function will do the

work for us we just need to point to the

different places so it's asking us for

the lookup value well the lookup value

is what gets everything started it's

going to be whatever is in currently in

cell

A4 the next thing it's asking us for is

the lookup

array the lookup array is basically

where can we find those employees ID

numbers so it's going to be in column G

right starting with

G10 down to about row

46 the next thing it's asking us for is

what is the return array well the return

array is what you want to return so

remember we want to return the last name

that can be found on that First Column

so it's going to be A10 down through the

last row which I believe is

46 so basically if we were to plug this

in manually it would look something like

this right so our lookup value is going

to be

A4 and then our lookup array is going to

be basically where can we find those

employee ID numbers that we're placing

in cell A4 it's going to be

G10 down to g46 we have 46 rows of data

well we go down to row

46 and then the return array we want to

return the last name which

begins

A10 down through

a46 as soon as we plug in the

information here it's going to give us

our result so it should give us the

appropriate last name based on the

employee ID

number so let's go ahead and plug these

in so our lookup value let going to to

be our input cell which is

A4 already confirms that 1368 is in that

cell for

us for the lookup array well I can find

those employee IDs in the from G10 down

to

g46 so there we go here's the array

which array do I want to return or range

I want to return from the last name I

don't feel like scrolling through I'll

click on the first last name and I'll

press control shift down highlights all

of them for me and there we go look up

values

A4 we can find them in G10 through

g46 the returning column is A10 through

a46 we have woo for Tammy I'll go ahead

and press

okay and there we

go so that is is how X lookup works and

as you can see much more efficient than

the other lookup functions that we can

use I'm going to go ahead and change the

employ ID to 1054 and I'll press enter

and I am good to go right now there's

some other things that we could have

done we could have

named right we could have named the

employee ID range if we want to and then

we could actually name these ranges here

as well last name first name department

and higher date so that we could do that

the other thing that we can do is we can

just modify our original X lookup here

and we can lock on to cell A4 so in the

formula bar I'm going to Absolute

reference A4 so I'll press the F4 key on

my

keyboard we're also going to Absolute

reference the employee IDs so G10

through

g46 so I'll press

F4 absolute reference those as well

so now we're locking on to the employee

ID in cell A4 we'll also lock on to the

employee ID range over here as well so

now what we can do we can autofill over

and just change the return range I'll

goad and press enter here and let's see

if I autofill

over to the

right so we can see it's giving us the

last

name here's the first name it's moving

over the

department that's fine and for the

higher date this is incorrect we just

need to go ahead and change the return

so that we're returning the values in

column H and not column D so right here

I can just say

h10 through

h46 and if I press enter

we get the higher date it's giving us

the serial number for the higher date so

I'll just go ahead and change this to

the date

format I'll use a short date here and

there we go so there is our X

lookup input a value ex Excel will find

the values in a a database for you and

return feels from that record here as

well go ahead and enter your X lookup to

complete our kiosk go ahead and give it

a quick test I'll put 1075 in here and

here's our record for Cheryl

Kane let's take a look at a really nice

example of how we can use x lookup here

so it's very common in the data world to

have different data sets so if you take

a look on this worksheet for joining

lists we have a few data sets here we

actually have a table table over here so

here's our products table we have

several products the Aspen product is

$21 we also have a region table so the

region table we have our sales rep for

example Alan Ramos is from the Midwest

region and then we also have an order

form over here as well so here's our

order form we have the date of the

transaction we have the sales rep from

our region table we have the

product from our product

table and but we're missing something we

don't have the price

here and we need to go ahead and

calculate the

sale and if we want to run a pivot table

analysis on this data it would make

sense for us to maybe insert a region

column so that we can run some Regional

reports as well so that's what we'll do

I'm going to go ahead and insert a

column

first to the left here right after the

sales rep so I'll go ahead and insert a

column and I'll call this column

region so now we can run pivot table

analysis based on the date sales rep

region product and the sale as

well well let's go ahead and return the

region based on the sales rep so let's

use Austin Reynolds as an example here

if I said let's go ahead and return the

region for Austin

Reynolds well you'll take Austin

Reynolds here as our input you're going

to go down the

region table here for sales rep and once

you find Austin Reynolds you say oh well

Austin Reynolds is from the East so

you'll go ahead and put Easton here for

Austin

Reynolds but uh we don't want to do that

manually and that's exactly what xlup is

going to do for us here on this

worksheet this is 10,000 rows of data so

if I press control end we can see it

goes down to row

10,3 so let's see if x lookup is going

to do a good job for us I'll press

control home to go back to cell A1 let's

go ahead and return the region based on

our sales rep same thing lookup value

lookup array and the return array so in

this case I'll actually plug it in to

kind of help us here so our lookup value

in this case is going to be Edwin Malone

okay so AKA cell

H4 so I'll click on Cell H4 for my

lookup value

as far as my lookup array where can I

find my sales reps well they're going to

be in column

D so it goes down to row 75 right so

it's going to be D4 to

d75 so this will be

D4 through

d75 and then what do I want to return

well I want to return the region which

is going to be

E4 to

e75 so I already have all of my inputs

here that I actually need and now I just

need to go ahead and plug them into my

xlup and allow xlup to

do it's work for me go and make this

Orange let's go ahead and return the

region based on our sales

rep I'll click here on Cell

I4 and I'm going to insert my X lookup

function my lookup value Edwin Malone

and cell H4 so I'll click here and I can

confirm it's recognizing that Edwin

Malone is in here now it's giving us the

table name for that cell because we're

working with tables now for the lookup

array well we can find those here in

cell D4 to

d75 so I'll go ahead and highlight all

of

those and it also gives me the

table name here as well right so table

two sales rep column so it's converting

it for me automatically we can also type

that in if we want to so what do we want

to return we want to return the region

column from table two so I can actually

type it here so I can say table

two and then brackets I'll type the name

of the column which is region and close

my

brackets so that's one of the benefits

of working with a table it automatically

names the ranges for you as far as the

column well here we go according to xlup

Edwin Malone is from the east

region right I'll go ahead and press

okay now because this is a table when

you enter a formula in the

top row here it's automatically going to

fill down

the formula for us so that's a major

benefit here so let's press okay and

let's see what

happens so look at that we can see that

we have all of the regions based on our

sales rep well let's do a quick test

here's bernardet page is bernardet page

from the Midwest region well let's find

bernardet here's

bernardet yes so we can see our X lookup

is working perfectly fine here and if we

go to the end of the data set I'll press

control end we can see it's inserted the

region for all of our orders here as

well so we're good to go let's go ahead

and do the same thing here for the price

right so in this

case here is our lookup

value it's going to be the carot product

the look

array is the

products the return array is the

price so we already have the information

we need let's go ahead and plug that in

here so X look up one more

time look up

value first

product on the top

row look up

array all of my products from my product

table the return array my prices from

the product Table and there we go it's

already giving us our price which is

$22.95 and if we locate the carada

product we can see that that is indeed

$22.95 so X lookup is taking care of

that for us let's go ahead and press

okay and just like with our region adds

the prices for all of our products here

as well so we're good to

go last thing we need to do is just to

calculate the sale so we'll just do a

simple calculation we'll multiply the

units times the

price and to get our sale here so I'll

click on sell M4 I'll say

equal and I'll click on the units here

and multiply that times the

price press enter and there we go we

have all of our sales so that's a real

world use of X lookup go ahead and

insert the region column go ahead and

populate the region

column go ahead and populate the price

column and go ahead and calculate the

sale and come right

back let's go ahead and convert our

order form into a pivot table and let's

go ahead and analyze the data

so because we're working with a table we

can actually click on the table design

Tab and we can use the command to

summarize this with a pivot table so

I'll go ahead and click

here and I can actually place this on an

existing sheet I do have a sheet here

called Advanced pivot table it's empty

right now so I'll go ahead and place

this there so I'll click on here I'll

head over to Advanced pivot table and

I'll go ahead and place it over here in

cell A2 and I'll press

okay so here is my pivot table now that

I have my pivot table up and running I

can go ahead and build out my pivot

table now this is the default view for

the pivot table so it's stacked we have

our Fields up here on the top and we

have our area boxes on the bottom I like

to click on the little gear icon here

and just change the view and also sort

my fields in alphabetical order as well

so I'll click on the gear icon and I'll

go ahead and choose side by side I'll

also go ahead and sort A to

Z just a matter of

preference so there's side by

side and now I'll go ahead and sort my

Fields A to

Z going to build out my table here I'm

going to go ahead and drag the sale into

the values

box and right here I get my sum of sale

so I have my sum of sale in here so by

default it wants to summarize the values

that I place in this box here now I

don't have order IDs in here so what if

I wanted

to not get the sum of all of our sales

which is perfectly fine but what if I

want maybe the count of the sales right

so how many transactions did we

actually have so to do that we're going

to

display the value in a different way

instead of using the sum we're going to

use the

count instead so to do that I'll go

ahead and pull up my value field

settings I can click on the drop down

here in the actual area box here is my

value field settings

I can also right click on the number

here and I can also get to Value field

settings here as well so either way I

can come in here and get that

information I'll click on the dropdown

value field settings so notice right now

we're summarizing the value field by the

sum if I change this to the

count right and now I can also change

the custom name that appears here as

well

so I want to know how many transactions

did we have and I'll change the name

here as well and I'll just call this one

transactions I'll go ahead and press

okay well before I do that I want to get

rid of the decimals so I'll click on the

number format here on the bottom left

and I don't want any

decimals I'll go ahead and press okay

and if I press okay again this tells me

that I have have 10,000 transactions

which is correct because we had 10,000

transactions on the original data set so

now that I have 10,000 transactions now

I can get some information such

as how many transactions per region so

we can see that we had most of our

transactions in the Midwest region

3,63 and we had fewer transactions in

the South as well

right of course I want to go ahead and

move things around a little bit maybe I

want base on region and product now I

can go ahead and be a little more

precise we had

125 transactions of the Aspen product in

the east region here as well so go ahead

and insert your pivot table go ahead and

add the sale to the

values check change it to summarize by

the count

function and create a couple of visuals

and come right

back let's go ahead and reset our table

and let's take a look at the calculated

Fields Advance option so we want to

create a calculated field and we'll see

why let's go ahead and reset our table

just going to go ahead and put the sale

back in here

here and I'll just switch over to the

accounting

[Music]

format so now we have the sum of sale

well I want the sum of sale by sales rep

so here are all my sales reps and we can

see all of their sales here as well now

what I would like to do is calculate the

commission so let's say we're giving a

10%

commission based on

sales now I don't have a field over here

that's called commission so how can I

calculate the commission well one thing

that you can

do just temporarily is to you can create

an outside formula so if I say you

know commission here I can go ahead and

enter a simple equation so I'll say

equal the sum of sale times

10% I could even use the cell reference

if I want to if I press okay okay here's

the commission for alen Ramos and now I

can go ahead and autofill down and this

shows me all of my commissions for all

of my sales reps so that's one way that

I can do that but this is temporary and

the reason I say this is temporary is

because as our pivot table expands it's

going to overwrite any data that's in

that's over here right so if I just kind

of let's say if I put my region into the

columns

it basically wipes that data out right

and notice that these cells are no

longer just the overall values so it's

kind of a temporary solution so what can

we do here instead going to remove the

region the other option is to go back to

the original data set so if I go back to

joining list what I can do is insert

another header over here that's called

Commission and this will calculate the

com the commission based on the current

sale then I would need to just go ahead

and make sure all of my formulas are

added here as well then I would need to

go back to my pivot table refresh the

pivot table so that we can display the

field for that new header well we don't

need to do that we don't need to create

a outside formula we don't need to even

add a field to the original data set

what we're going to do is create a

calculated field to do that I'll click

on pivot table

analyze and over in the calculations

command group we have Fields items and

sets if I click on this drop down I'll

see an option to create a calculated

field I'll go ahead and click here so

there we go right so I'll go ahead and

click on calculated

field here's my insert calculated field

dialogue box I need to do two two things

I need to First go ahead and name it and

then enter my

formula once I'm done I can go ahead and

add

this to my field list here and we'll see

how cool that is we can add a field here

and use that at different places in our

pivot table then I can go ahead and

press

okay and this will display the field in

column C for me and display all the

values here as well

so I'll go ahead and call this

[Music]

commission now as far as the formula we

can use Simple functions in here we

cannot use anything too complex if

you're looking for more advanced

features go ahead and click on the

question mark here this will open up a

Microsoft article that will show you

just some of the limitations in terms of

what we can do but the purpose is here

is just to show you how a calculated

Field Works so for my formula I'll

delete that zero so we

can run calculations on columns so in

this case I'm concerned about the sale

so I'll go ahead and insert the sale

field here so it's going to be the sale

times

10% and that's it so it's called

commission the formula is equal to the

current sale times 10% I'll go ahead and

click add

and notice when I clicked on ADD I now

have a new field that's called

commission I'll go ahead and press

okay and now we have a field here it's

sum of commission and it's giving me all

the commission follow of my sales reps

if I go to the bottom here's the grand

total of all commission and that's

correct because 10% of 915,000 is

basically

91,500 so that is how we add a

calculated field now if you want to

change that let's say we the commission

rate changes to

11% this is the benefit of using the

calculated field I simply need to go

back to pivot table analyze and I can

click on calculated field

here I will click on the drop down here

locate the calculated field that I want

to

modify and I'll just change this to

11% I'll click on modify

I'll click

okay and you can notice in real time all

of my values have been updated here so

11% of

54.97 yields

60467 so that's a really nice way to

create another field that you can reuse

in our pivot table now here's the

benefit of it what if I wanted to go

ahead and calculate the commission based

on region well I'll go ahead and

let's see I'll go ahead and add I'll

remove my sales rep

here so as you can see now here's the

total commission if I want commission

based on

region here are the commissions that are

paid out based on region so just like

with our fields we can reuse them at

different places in our pivot table go

ahead and create a calculated field of

your choice and come right

back now when you're working with your

pivot table we natur want to be able to

filter our pivot table to display the

values that we're looking for now if I

click on the pivot table analyze tab we

have some more advanced filtering so

remember we do have the row and column

label filters that we can interact with

we also have our filter box over here as

well but we're going to take a look at

two types of filters one is the slicer

and one is the

timeline so I'm going to go ahead and

and remove the commission here I just

want to focus on the sale based on

region and also based on product so I'll

move the region to the columns here and

I'll put the product into the rows so

here's our simplified condensed table

showing all of our transactions by

region and product as well so here are

my two here my row label filters I can

filter by product here's my column label

filters I can filter by region well I

want to insert a slicer for that so on

pivot table analyze let's first insert

our slicer and let's see how our slicer

behaves I'll click here notice we have a

slicer for each of our Fields here we

don't have one for commission though

because it's a calculated field I want

to go ahead and insert a slicer for

the product and want all so for the

region I'll go ahead and press okay here

are my two slicers I'm going to go ahead

and move them around I can resize

them I have quite a few products so this

slicer is a little longer so I can

actually display this Slicer in two

columns but before we do that let's see

what our slicer can do so if I'm

presenting this data and I'm asked about

a particular product if I just click

here let's say on the sunset product

notice how it filters my pivot table

it's only displaying the values in each

region for my Sunset

product pretty cool well what if I want

to focus on the Sunset and the Aspen

product if I hold down the control key

and click on

Aspen now I'm showing only for the Aspen

and sunset product so notice how all of

these numbers all 15 of these cells have

been recalculated based on that

filter I can take it a step further what

about the Aspen and sunset in the east

region there we go so that's what our

slicers are

for again you can select multiple by

holding down the control key and

clicking because by default you can only

choose one value at a

time there's another way that we can

select multiple if we take a look at the

top right of our slicers we'll see a

little tool here this is the multi-

select tool this changes the mode so

that we can select multiple

items going to go ahead and clear my

filters so I'll clear my slicers here to

make sure everything is selected I'll

click on that clear filter icon

everything is

reset going to turn on the multi- select

tool for both of my

slicers and now I can select multiple or

in this case unselect what I don't want

so because everything is

selected I can pretty much unselect what

I don't want so if I just want Aspen

doublers and the

sunshine I can go ahead and do that

right for my regions if I just want

the East and the South regions there we

go so our slicers allow us to filter our

data that's being displayed on our

table now for our slicers I'll go ahead

and reset them

again or because it's a filter we have

the clear filter option here now our

slicers comes with its own contextual

tab so if I click here I can see slicer

appear at the end of my ribbon and I can

do things like change the name of the

slicer I can choose a slicer style I can

even choose how many columns I want

because my product slicer has a really

long one column here I'd prefer to have

maybe two columns right this way it

doesn't have to be that long so I'll go

ahead and change this to two columns

instead of one let's see if that looks

better there you go so there's two

columns now it's not as tall I just need

to make it a little wider so this gives

me a little more control now I can stack

them like

this I'm good to go we can change the

colors there we go so those are the

slicers right so if you click here we

can change the name of the slicer here

are the different slicer settings that

we can interact with again just some

basic information here by default it's

sorted A to Z here's the caption option

here's the name right if you do have

blank values that's showing up you can

always choose this option that says hide

items with no data this way you will not

see a blank button on your slicer as

well so that's it for slicers go ahead

and insert a few slicers go ahead and

play around with them click on the

buttons and check out how your pivot

table is being updated to delete or

slicer just just click on it press the

delete key and we're good to

go let's take a look at the Timeline

going to move my slicer down here to the

bottom left and let's go ahead and

insert a timeline so I'll click back on

the pivot table analyze tab here and

here is my timeline right next to my

slicer I'll go ahead and insert that now

the timeline is a very powerful tool

because a lot of our data is based on

time time such as quarterly reports or

monthly reports or yearly reports a

timeline takes a lot of the guess work

out for us and it's a very efficient

tool it's a very simple tool as well so

we'll take a look at it here I'll go

ahead and click on insert timeline and

notice the timeline tool looks for a

properly formatted date

field so make sure that you have a

properly formatted date field otherwise

you will not be able to use the time

line I'll go ahead and click here and

I'll press

okay here is our timeline right now the

way our timeline works I'm going to

change the color just so we can have

some contrast here just like with our

slicer we have a timeline tab here we

can do things like rename it and we can

go ahead and change the color I'll just

make it

Orange right so here is our

timeline the way the timeline works is

we have a hierarchy so we have years we

have quarters we have months and we also

have days to the top right of our

timeline we need to First decide how do

we want to view our data do we want to

view it by month year or by quarter

let's start at the highest level I'll go

to years here so as you can see we have

years quarters months and days days if I

click on years so it looks like I have

transactions covering 2019 2020 and

2021 so if I click here it's a little

time capsule if I click on

2019 we can see we had looks like maybe

just one transaction so we had sales in

2019 of

$798 well what about

20120 here we go that looks better so in

2020 so all this information here that's

being displayed this is based

on the information for the year

2020 well if someone says well what

about 2021 did we do better did we

improve from

2020 and we can say yes we increase

sales by $115,000 from $450 to

465 so that is pretty cool so no need

for us to write a lot of functions and

formulas the timeline is giving us this

detail here let's go ahead and change

that I'll go to The Quarters here so

I'll click on

quarters and we have several Quarters

here now because we're we have three

different years of transactions so now I

need to go ahead and click on my

timeline and just kind of make it wider

so I can see the quarters for my

different years here as well right so

here's

2019 looks like we only had one quarter

for 2019 let's see

yeah so for 2019 quarter 4

$798 how did we do for quarter one in

2020 quarter 1 in

2020 sales of

41,600 all of my numbers are updated

right

away what about quarter 1 for

2021 here are all of my numbers so that

is a pretty pretty nice tool to be able

to use here the first half of 2021 sales

of

69360 finally we'll take a look at

months we can drop down to days as well

that's a little granular I'll just go

down to

months so here are my different months

again you may need to just readjust your

timeline so that you can see all of the

data I'll just focus on 2021 here so in

January of 2021 here are our

sales in

February March April so forth and so on

so that is a really nice tool to get

some real-time data on your data set by

using the timeline I'll drop back down

to the years here I'll go ahead make

this a little smaller so I can see I'll

go ahead and choose 2020 as my target so

that is the timeline highly recommend

you use that especially if you're

analyzing your pivot table based on

time-based information or if you're

presenting your pivot table as

well let's take a look at report page

filters I'm going to go ahead and remove

my slicer here just make sure it's reset

I'll go and remove that I'll go ahead

and clear the filter from my timeline

here as well then I'll go ahead and

remove my

timeline and let's go ahead and take a

look at what's called report page

filters right so we'll take a look at

that it's another type of filter that we

can

use so what I'll do now is let's go

ahead and focus on our

regions so what if I wanted to do

something like get a separate pivot

table for the east region that I can

manage

independently right so build me another

pivot table for the

East also build another pivot table for

the

Midwest one for the

South and also one for the West right

and by the way go ahead and put them on

their own worksheet

tab so I can kind of just analyze them

independently

they're still connected to the original

data source but not necessarily to this

pivot table here as well so those are

called report page filters so how can we

get Excel to create to insert four

worksheets here with a pivot table on

each of the worksheets for each of our

regions well let's take a look the first

thing we need to do is we need to

introduce our region into the filters so

here's our filters

box over here we need to go ahead and

introduce our region so if I wanted to

have different worksheets based on

product I can do that or sales rep but

right

now regions I only have four so it's a

perfect candidate I don't necessarily

want 75 um worksheet tabs for my sales

reps so I'll go ahead and drag the

region in here and then I'll run the

report page filter now before I do that

let's see where we can insert insert

report page filter I'll click on the

pivot table analyze Tab and all the way

over on the left under the pivot table

command group if I click on the

drop-down here is the show report filter

Pages you can see that it's grayed out

and the reason it's grayed out is

because we are

missing a

filter field here in this box

so as soon as we drag our region in here

we'll notice that this will become

active and we can go ahead and run the

report and it's going to do exactly what

we wanted to do so let's go ahead and

drag the region into the filters

here now we clearly have the region and

the filters I'll go back and I'll click

on show report filter Pages let's see

what happens I'll click

here and notice it's asking us to show

report filter pages of anything that we

have in the filters box so I'll go ahead

and press

okay and I'll scroll over a little bit

here so that we can see it a little

better we can see that Excel inserted a

worksheet for the East so here's a pivot

table for the east region we have one

for the Midwest one for the South and

one for the West as well so these tables

they are behaving

autonomously they're behaving

independently they're not connected to

my other pivot table they are connected

to the original data set but no longer

connected to this pivot table here as

well so for example here in the East I

can rebuild this table to the way that I

want it to be right so maybe I'll go

ahead and put the product in the columns

I'll put the sales rep into the rows

right and now we can have our detail

here so this is all the east region if I

go back to my original pivot table I can

see that it's not implementing those

custom changes here as well so why would

you want to do that well maybe you just

want to go ahead and analyze data for

the Midwest region without you know

moving things around on the original

pivot table so this is kind of like a

drill down or drill through report that

you can use as well so that is how we

can insert the report filter page

to finish up our practice file here

we'll take a look at the very last

worksheet which is text functions now

actually what we're going to be using is

a function it's my favorite tool it's

actually tool but it operates like a

function as well and this is called

flashfill so again flashfill is a really

nice tool it allows us to do things

like what we would otherwise use concat

or concatenate or text join or text

split or text to columns so it's a

combination of all of those different

text tools that we can use in this case

what I want to do I just want to go

ahead and focus on column D column

G column I and column J and basically

what I want to do first I want to go

ahead and combine the rep first name

name into one cell so to do that I'll go

ahead and click on the data Tab and

Flash Fill is on the data tab over in

the data tools command group here is

Flash Fill little lightning bolt going

through a table that is flashfill if you

hover over it tells you automatically

fill in values enter a couple of

examples you

want and Excel will fill it in for you

we can also press contrl plus e but

let's go ahead and watch it work we

don't necessarily have to click on it

let's go ahead and enter the rep full

name and column D so I'll go ahead and

type I'll just say Cara Olsen and I'll

press the enter

key now I don't feel like typing

this everyone's name here so let's see

if Excel will pick up on the pattern for

me so I'll go ahead and start typing

here for

Anthony and notice as I'm typing

Anthony's name here it's automatically

recognizing the pattern right that I

want the full string value that's in

column B add a space and add the full

string value that's in column C here's a

preview of what it would look like so

yes I do want that so to accept that

I'll press the enter key I can always

test to make sure it's working fine I'll

press the enter key and here we go so if

I go all the way to the bottom I'll

check that last one here here is Jerry

ER and so pretty good so that works well

as soon as you run Flash Fill we get a

little Flash Fill options here can do

some basic things like undo the Flash

Fill if it did not pick up on the

pattern

properly or I can go ahead and accept

the

suggestions I can also go ahead and

select all 41 change cells I'll just

accept these suggestions here did a

pretty good job so basically Flash Fill

looks to the

left senses a pattern

and then it offers to complete that

pattern for you now you'll be surprised

to see what types of patterns flashfill

can actually pick up on well let's head

on over here let's see if we can extract

the

actual month from our date here right so

the month here is January so I'll go

ahead and type

January and let's run flashfill this

time so I'll click on the flashfill icon

here and let's see what

happens and look at that so it's

extracting the month from this

particular date for

me right now this only works because