a

Why pivot tables?

hi Dave here from XL jet so today we're

going to tackle the question why pivot

tables and this question comes up a lot

when people first run into pivot tables

because they're wondering like why

should they care and you're trying to

explain that pivot tables are really

fast that you can summarize data

interactively in different ways

bla bla bla and you can kind of see

their eyes just glazed over because

until you've actually used a pivot table

yourself it's really hard to understand

why they would be useful to you so I

think one of the best ways to understand

how pivot tables can help is to look at

some examples of building some reports

side-by-side where one set of reports

were building manually using normal

Excel functions and formulas and the

other set we're building with pivot

tables and that's exactly what we're

going to do in today's video and I want

to be really upfront with you here

I love pivot tables ever since I got my

first pivot table to refresh correctly

back in the 1990's I realized like wow

these are really useful tools so I think

if you use Excel with a lot of raw data

and you need to summarize that data in

different ways and if you need to create

good-looking reports then pivot tables

are definitely worth your time so I hope

by the end of this video you'll agree

with me let's go look at some examples

so here we are in Excel and you can see

we've got a set of raw data this data

represents chocolate sales to several

customers in different locations at a

wholesale level and you can see we've

got date and customer city state and

region product and product category and

finally quantity revenue cost of goods

sold and profit now imagine that your

manager sends you this data and ask for

a one-page summary that shows a

breakdown of sales by product so

assuming that you don't know how to use

pivot tables but you are pretty good

with Excel formulas let's build out a

manual report first the easiest way to

do this is to use the sum ifs function

sum ifs it's easy to use but it can

create some pretty ugly formulas so to

make things easier to read and avoid a

lot of absolute referencing I'm going to

go ahead and name the columns first now

we have a name for each column in the

data while I'm here I'm also going to

name the full set of data so I can

easily refer to it when I create a pivot

table later

now let's build a manual report first

I'm going to grab a copy of all the

products and then remove duplicates and

sort now I'll add in some labels and

formatting

so we have the bones of the rapport in

place now but no data and it's time to

write out the first some ifs formula I

could actually use some if instead of

some ifs but because I'll be adding more

criteria in just a minute it'll be

easier if we start with some ifs because

I already named the columns I can easily

type the names as I go without going

back to select the data now we need to

copy the formula and add a total and

I'll format the numbers as currency now

we have the basic report that your

manager wants a one-page sales report

with a breakdown by product so you can

see here that the manual approach isn't

too terrible but I already knew how to

do it and I used a lot of tips and

shortcuts to speed things up if you're

not as comfortable with formulas it's

going to be quite a bit harder with

plenty of mistakes and learning as you

go now let's build out the same report

using a pivot table

since I already named the data I can

just use that name here we'll need to

add product as a row label and revenue

is a value

and then apply a number format to

revenue and that's it we now have a

pivot table with the same results and

notice that I didn't use any formulas at

all of course no manager will ever be

satisfied with their original

instructions the law request changes so

let's look at how both of these reports

handle a few change requests first let's

say your manager comes back and asked

for a breakdown by product and region

she wants to see region across the top

for the manual report I'll start by

grabbing a copy of all the regions and

I'll follow the same process that we did

for products before this time I need to

Tran

the regions across the top then I'll

need to clean things up a bit

next I need to extend the sum if fun

to handle both product and region I'll

need to lock the column reference for

product because I'll be copying that

just a moment

then at a new range and criteria for

region with the row locked now I can

copy the formulas

rhasta table and add totals again it's

not too terrible but lots of clicks and

plenty of formulas changing the pivot

table report is quite a bit easier

all I need to do is add region as a

column label and we're done

again there's no need to update any

formulas I'm also going to change the

pivot table layout to outline and line

it up again with the manual report

so you hand that report back to your

manager and an hour later she tells you

that it's great but can she also see a

breakdown by year instead of region hmm

this will be trickier in this case I

know that the data spans only three

years 2011 2012 and 2013

so I'll start there and add those in

manually as headers and get rid of the

extra column

now I just have to update the formulas

this is actually a good use case for the

sum-product function but that would

require a lot more explanation so I'll

just stick with some myths for now now

to update the some is function to match

dates by year we're going to have to use

two criteria along with the date

function and some concatenation the

first criteria matches dates that are

greater than or equal to the first day

of the year the second criteria matches

dates that are less than or equal to the

last day of the year in both cases were

referencing the year directly and we

need to lock the row

you

once I copy the formula down

cross we've got our report you can see

that this change was quite a bit more

complicated and required a good

understanding of Excel formulas and

functions and concepts like

concatenation what about the pivot table

to update the pivot table I need to

first get rid of region then I need to

add the date field and group by year as

before there are no formulas to update

so you get the idea

it's totally possible to build these

reports manually and in some cases it's

desirable to do it that way but it's a

lot faster and easier to use a pivot

table ultimately a pivot table can build

much more complex reports then you'd

ever want to tackle manually

all right so I hope you enjoyed that

comparison of building some reports

manually and the same reports with pivot

tables as you can see it's not even

closed pivot tables are way faster so

anytime you're analyzing data in Excel I

think you should be thinking about using

pivot tables but beyond simple

convenience pivot tables give you a real

competitive edge so whether you're

competing internally with your peers for

a raise or promotion or just recognition

or you're competing externally with

other companies in your market to

identify trends or opportunities pivot

tables are a tool that can really help

you stand out and do your job better

they help you analyze data really fast

they allow you to summarize that data

interactively in many different ways and

as you saw they don't require any

formulas at all

so they really help reduce errors I'll

be back in a couple of days with our

next video in this series on pivot

tables and in the meantime we'd love to

hear from you about your experiences

with pivot tables or maybe your

questions so please use the comment area

below and add your thoughts I'll see you

soon in the next video