Welcome to the data analysis section of this beyond formulas Excel modeling training in this lesson
will cover everything you need to know to instantly summarize your data with Pivot Tables.
The goals of this lesson are to create your first pivot table and learn the basics of adding and manipulating
fields to take advantage of exile's more advanced pivot features including field settings formatting
filtering and sorting and to make use of data in your summaries using the powerful grouping feature
and to complete the lesson you will need.
The Calumet case five point to the table starter Excel file by the end of this video you would have
learned everything you need to know about how to pivot as well as how to use pivot tables to crunch
the census and sales data needed to crack this case.
In particular you would have summarized various metrics by a combination of geographic area and time.
So what are pivot tables anyway and why should you use them.
Think of them as Axel's convenient visual database query system without any of the nasty code they're
used for static and typically very large data sets.
The pivot table is the perfect approach to summarizing information with little more than a few clicks
allowing you to perform very complex aggregation sorting and filtering.
So with that context let's jump into Excel and start creating open up the Kerry Mack case 5.2.
I have a table start to file and jump to the combined census data tab.
Remember that from the previous lesson we had already turned this table into an Excel table.
This makes convincing this into a pivot table a cinch.
Simply click on the insert ribbon followed by the pivot table button which populates this dialog with
the table census data table name that we had configured previously.
We'd like Excel to create a new worksheet with a pivot table on.
So hit OK at this point I'd like to cover some of the basics of the pivot Explora which is the window
that appears on the right hand side.
Every time your cursor is within it pivot area the top part of the pivot Explorer will show you all
of the fields available in the dataset within that range and below.
There are four boxes that allow you to configure the pivot table accordingly.
Those being the pivot raport filter the column labels the row labels and the values to begin playing
around with a pivot table.
All you need to do is drag one of the fields from the area above into one of the areas below to get
started.
Let's go down and select our population 2012 estimate and drag that into the values field.
Notice how Excel defaults to a some aggregate technique and that the total ends up at 313 million.
We can continue to add fields to the various boxes below to make more sense of the data.
Let's go pullouts CBSA name and drag that into the Rose field.
You'll notice that Excel has immediately broken out the revenue by CBSA name and then if you scroll
to the bottom of the pivot table the grand total remains the same.
From this area we can add additional aggregation methods to.
We'd like to count the number of unique zip codes within each CBSA area go up and drag the zip census
tabulation area field down into the value section and notice how once again Excel defaults to the sum
aggregation method.
To change this click on one of the entries in the new field you've just created it.
Pivot Table options and then click on the summerize values by tool to change to count the dates.
Now reflect the correct number of unique zip codes in each CBSA area lastly we'd like to complete the
section by adding the average latitude and longitude to this pivot table.
Scroll down to find the latitude and longitude and drag both of them independently into the data set.
Next place your cursor and each one respectively and select summerize values by and select average and
repeat this for latitude
as it stands the pivot table has done a pretty messy job of aggregating data with the numbers very poorly
formatted.
This is easy to fix.
Move your cursor into the sum of population 2012 number and then either click the field settings button
in the Pivot Tables ribbon above or right click on the value itself and go to value field settings.
You'll see that you can also change the aggregation method here but in this case we're interested in
the number formatting.
Click on the number format button to open the new dialog and then select a more appropriate formatting
for these numbers will pick a number formatting with zero decimal places and including the thousands
separator.
It's OK and OK again to apply only to format as mentioned earlier.
Pivot tables can be an incredibly powerful sorting and filtering tool if we'd like to sort by 2012 population
simply move your cursor into the field and click on the sort button which offers us a range of options
in a dialog.
In this case we'd like to sort from largest to smallest and hit OK.
Notice that all CBSA names have now been sorted in order of descending population.
However there is a blank row that has appeared in our dataset as a result of bad data.
We can easily see from the summary that this blank CBSA name range includes around 19 million people
out of a total of 313.
So not a very large amount but not a small amount either.
We can also see that the areas in those blank ranges are made up of eleven thousand seven hundred and
ninety eight different zip sensus tabulation areas which suggests this is a problem of the long tail.
For the purposes of this analysis I'm going to propose that we filter these out to help clean up our
data a little bit.
To achieve this filtering we have a number of options.
One option is that we could include a new field CBSA in the report filter section and then use that
section to filter out anything that doesn't have a value.
But in this case I would choose to do this more directly by clicking on the down arrow above the rose
fields section in the pivot table and then unsticking the checkbox beside the blank items field in the
list.
When I hit OK this is going to fall to the pivot table for all items that do not have a blank value
for CBSA name.
Let's say we need more granularity than CBSA name which are pretty large geographic areas.
We can easily add more specificity to this pivot table by adding additional subsectors to the Rose field.
To do this let's go and drag the two digit zip code into the rows section which is how the pivot table
has not created a tiered hierarchy section.
We can see that the New York Newark New Jersey CBSA is basically made up of six areas of two digit zip
codes.
The pivot table automatically breaks out the totals of population for each one of these and includes
an automatic subtotal at the CBSA level called out in bold.
Excel gives us quite a lot of power of the subtotals.
If I right click on this field and go to field settings Excel allows me to specify exactly how I would
like this summarized.
I could for example select custom and then select average and hit OK.
To tell Excel to calculate the CBSA average for the various two digit zip codes within each.
Be very careful of when you use this as discussed earlier.
Averages can sometimes be a bit misleading.
Let's go changes back to automatic it field settings change the subtotals back to automatic and hit
OK.
The field settings function of pivot tables also gives us a high degree of control over how this pivot
table is laid out.
If I select the CBSA name and go back into field settings this time open up the layout and print tab.
You'll see it gives me a number of layout options I could for example insert a blank line after each
CBSA name.
Doing so when hitting OK opens up a space in between.
This makes your pivot table much more readable if you have clearly defined sections going back into
the field settings and back into the Loughton print tab.
One of the biggest changes I can make is to move from this indented outline format to a tabular format
getting OK now makes a number of pretty severe changes to my pivot table rather than the first and second
layers of the hierarchy.
Being in the same column as previously it's now broken up.
The CBSA name in column A and the two digit zip code in column B.
If I wanted to take this out to an extreme so that I could for example format the data for inclusion
in another database I could go back into field settings I could select subtotal value of None for the
CBSA level so the subtotals won't be calculated.
I can go into the Loughton print and I'm going to ask Excel to repeat item labels and show items with
no data.
Ok now convert my pivot table into a flat file format.
I could easily use this summary as a lookup table to power other aspects of my model.
The next pivot table technique I'd like to teach you is the inclusion of slices which allow you to make
your pivot tables a little bit more interactive.
Go ahead and insert one now a slicer is essentially a visual filter including them from the pivot tools
options ribbon above opens up a dialogue that invites me to select one of the fields for which to create
a slicer.
In this case select urban and rural and then select OK.
Notice how Excel creates this floating window with an R and A U which are the two distinct values that
the rural and urban field contains.
You'll notice I can move it around and I can even cut it by hitting control X and paste it on a different
sheet by hitting control the for the purpose of this demonstration.
I'd like to cut and paste it back onto the pivot table sheet.
We were working on previously so that I can demonstrate what it does it control X to cut and control
V to paste as mentioned earlier slices are essentially visual filters.
Clicking on the arm causes Excel to filter only for the rural records and taking on the you tells X
ultrafilter only for the urban records.
To remove this filter I can click on the clear filter icon in the top right hand side which turns my
pivot table to its natural state.
You can imagine creating much more complex Lass's going back to the pivot tables tools ribbon cutting
slices and adding state postcode for example will allow me to easily slice my data by state with a few
clicks.
Here is the summary for California for example.
And here's the summary for Arizona.
If I hold control and take on California I can select more than one of the options in the slicer at
the same time.
I'll leave you to experiment further with slices in your own time.
Right now I'd like to create another pivot table.
Let's go to the 2013 sales data tab.
The legal case a some way into the table which we've already defined and go to the insert ribbon and
hit of a table.
Once again it's pre-populated with a table sales date a name and once again we'd like to create a new
pivot table in a new worksheet.
It ok.
Notice the familiar set up with the pivot table area on the left hand side and the pivot table explorer
on the right hand side.
Go ahead and put revenue in the value field this time what we'd like to do is break out this revenue
by quarter.
In the old days you would have to go and create a custom set of if statements that generate a unique
corta value based on the date with Pivot Table grouping.
This can be done in just a few clicks in order to achieve this.
Drag the converted date which is the new date that we've created with real dates serials as opposed
to the plain text date that was in the field previously.
Drag the converted date to the columns label and notice that Excel automatically creates all of the
columns above in order to group these into quarters.
You simply go to the pivot tables.
Options ribbon etic on the group selection button which correctly recognizes that this is a day serial
field has a start date and an end date and then Eliza optionality over how exactly to group the values.
We can group by months as is pre-selected by hitting OK which automatically subdivides the revenue by
month.
But in this case we'd like to do so by quarter group selection and then select quarters kicking OK as
the additional field of quartos to the revenue breakdown.
But we'd like to exclude months so that we simply have the four values go back to group selection.
Hold control and unselect months taking OK gives us what we want revenue broken down by quarter for
2013 as with the previous pivot table.
Let's drag the CBSA name down into the role field.
I'd like to introduce you to another filtering trick that Excel can perform by clicking on one of the
CBSA names gives me a host of options and going to filter gives me amongst other things.
This top 10 feature clicking on that gives me a dialog which lets me control exactly how I want this
top in feature to work including changing the number changing the top or the bottom and even changing
where they were looking at items percentage or the totals.
In this case let's leave it at items and hit OK.
And notice how Excel has already filtered off the top 10 CBSA.
Lastly one of the most useful tools for dealing with Pivot Tables is being able to drill down frequently.
Once you've created a pivot table you'll notice some strange data outliers.
You can easily see which entries or records excels including in a given summary statistic by simply
double clicking on it.
This generates a new flat file table with all of their records or entries that are playing into that
specific value.
In this way you can easily diagnose and troubleshoot any type of a table issues you seem to have at
the tables also come with a tremendous amount of power in terms of your ability to configure them and
you can identify all of these configuration options in the pivot table options button
these options give you a large degree of control over everything from the name so that you can reuse
this later.
The Loughton format including things like how to deal with empty or error values automatic formatting
to resize and rebalance all of the cells once your pivot table is refreshed under the totals and filters
tab you can influence where the grand totals are shown for the rows and columns that display that gives
you a fair amount of control over how the pivot table looks and functions.
One of the tools I've used frequently is changing it into a classic pivot table layout which takes the
pivot table format back to how it used to be in earlier versions of Excel before some of the aesthetic
changes were added.
Let's go back to the options menu if you desire specific options for printing your pivot table.
The printing tab gives you a little more control and lastly the data tab gives you control of how Excel
stores the associated data alongside the pivot table.
You can for example select not to save the source data with the file which will cause it to be recalculated
every time.
This reduces the size of the pivot cache and ultimately reduces the size of your model.
It brings with it performance trade offs
over tables are an immensely valuable tool to get to know understand and visualize the shape and structure
of your data.
My advice to any start or analyst would be to take any new dataset you're given and pore over it using
pivot tables until you really understand its distribution he relievers and major buckets.
Now know enough to be truly dangerous with Pivot Tables in the next lesson.
We'll cover some even more sophisticated of a table tools.