Data Analysis & Business Intelligence Made Easy with Excel Power Tools

Excel Data Analysis Basics = E-DAB

Notes for Video:
E-DAB-04: Summary Reports with Standard PivotTables & Slicers

Objectives of Video:
1. What is a PivotTable? What is a Cross Tab Report? ....................................................................................................... 1
2. Steps for Building a Standard PivotTable ........................................................................................................................ 2
3. PivotTable Cached Data .................................................................................................................................................. 3
4. Use Slicers to Filter Entire PivotTable ............................................................................................................................. 3
5. Use Group By Feature to group Monthly and Yearly Amounts ...................................................................................... 3
6. Summarize Values By to Change Aggregate Function .................................................................................................... 4
7. Show Values As to Change to Specific Calculations ........................................................................................................ 4
8. Dashboards ..................................................................................................................................................................... 5
9. SUMIFS and similar Functions or PivotTables? ............................................................................................................... 5
10. Standard PivotTables or Data Model PivotTables? ..................................................................................................... 5
11. Sales Data Dashboard Examples from Video: ............................................................................................................. 6
12. Cell Phone Data Examples from Video to Build Frequency Distribution: ................................................................... 8
13. CPA Data Examples from Video to Build CPA Pass Rate Report: ................................................................................ 9

1. What is a PivotTable? What is a Cross Tab Report?

Page 1 of 9
2. Steps for Building a Standard PivotTable
1) What PivotTables do:
1. Create Summary Reports that contain calculations with Conditions or Criteria.
2) Summary of how to create PivotTable:
1. Click in one cell in Proper Data Set
2. Insert Ribbon Tab, Tables group, PivotTable button.
• Keyboard = Alt, N, V.
3. From Field List, drag field name to Rows area or Columns area or Filter area. These are the
conditions/criteria for the calculation in the Values area of the PivotTable.
• Fields in the Rows area add a condition/criterion to the row
• Fields in the Columns area add a condition/criterion to the column
• Fields in the Filter area add a condition/criterion to the entire PivotTable.
4. From Field List drag the field you would like to make a calculation on to values area.
• Number fields default to a SUM calculation (add numbers)
• Text fields default to a COUNTA calculation (count non-empty cells)
5. With a cell selected in the PivotTable, click on PivotTable Tools Design Ribbon Tab, go to the Layout
group, click drop-down for Report Layout and then click on “Show in Tabular Form” or “Show in Outline
Form”. These Layouts shows the Field Names in your Report.
6. To add Number Formatting to the Values area of the PivotTable, click in one cell in the Values area of
the PivotTable, Right-click the cell and click on “Number Format…”, then in the Number Formatting
dialog box select the Number Formatting that you would like and then click OK.
7. If you want to change the summary calculation in the Values area, right-click the Values area in the
PivotTable Report, point to one of these two options:
• “Summarize Values By” and then select an aggregate calculation such as “Average”, or “Max”, or
“More Options”.
• “Show Values As” and then select a calculation such as “% of Column Total”, “Running Total”,
“Difference From” or other calculations.
8. PivotTable Styles:
• PivotTable Tools Design Ribbon Tab, Styles, More button, New PivotTable Style, then use dialog
box to create your own style.
9. Name PivotTable:
• Right-click PivotTable, Select PivotTable Options
• PivotTable Tools Analyze Ribbon Tab, PivotTable group
10. If your Field List goes away:
• It may be that your cursor is not inside the PivotTable, try clicking back inside the PivotTable
• Right-click the PivotTable and point to “Show Field List”
11. If source data changes, you must Refresh the PivotTable
• Right-click PivotTable, Refresh
• Refresh PivotTable keyboard: Alt + F5
12. Inside the Pivot:
• Pivot: drag and drop fields in Field List to “Pivot” the report.
• Filter from dropdown arrows.
• Sort from dropdown arrows

Page 2 of 9
3. PivotTable Cached Data

1. When you create a Standard PivotTable, Excel creates a copy of the source data and stores it in the Pivot
Cache.
2. The Pivot Cache is stored in Excel’s memory.
3. This is why the PivotTable does not update when source data changes.
4. If source data changes, you can right-click the PivotTable and click Refresh, or use the Refresh button in
the Data Ribbon Tab.
5. You can’t see Cached PivotTable Data, but that’s the data the PivotTable references when you build your
PivotTable, or change a Slicer selection or move rows/columns around.
6. When we group dates in a PivotTable, the Pivot Cache is where this action takes place. The Pivot Cache
save the action of grouping by dates so that when we use the date column in other places, the date
column will remain grouped.
7. When we use a Slicer or change the conditions and criteria in the PivotTable, these actions are
interacting with the Pivot Cache of data, not the original source data.

4. Use Slicers to Filter Entire PivotTable

1. Slicers can be added to the PivotTable to add a condition/criterion to the entire PivotTable, similar to a
field in the Filter area. To insert a Slicer into a PivotTable, click in one cell in the PivotTable Report, then
go to the PivotTable Tools Analyze Ribbon Tab, then in the Filter Group, click the Insert Slicer button
2. To Select Items not next to each other in a Slicer, use the Ctrl Key.
3. To Clear the selected items in the Slicer, use the “Red X” Clear Button in the Upper Right area of the
Slicer.
4. Hide Buttons in Slicer when there is no data:
• Right-click Slicer and point to “Slicer Settings”, then check the box for: “Hide items with no
data.”
5. Connect Multiple PivotTables to a Slicer:
• Right-click Slicer and point to “Report Connections” and then check the boxes for the desired
PivotTables.
5. Use Group By Feature to group Monthly and Yearly Amounts

1. In Excel 2016, when you drag a Date Field into the Row area of a PivotTable, it is automatically
grouping into:
• Year
• Quarter
• Month
2. If you WANT a unique list of Dates (like for a Daily Sales Report) you must:
• Right-click the date field in the PivotTable
• Click on Ungroup.
3. Grouping allows the aggregate calculation, like sum of sales, to easily get Monthly and Yearly
Sales Totals.

Page 3 of 9
6. Summarize Values By to Change Aggregate Function

1. List of “Summarize Values By” Aggregate Calculations:

11 Functions available in a PivotTable
What you see in Value Field Settings dialog
box Function What it does
Average AVERAGE Calculates arithmetic mean
Count Numbers COUNT Counts numbers
Count COUNTA Counts non empty cells
Max MAX Finds largest value
Min MIN Finds smallest value
Product PRODUCT Multiplies
Stdev STDEV Standard Deviation for a sample
Stdevp STDEVP Standard Deviation for a population
Sum SUM Adds
Var VAR Variation for a sample
Varp VARP Variation for a population

7. Show Values As to Change to Specific Calculations

1. Using “Show Values As” to make calculations in a PivotTable.

Show Values As Calculation What it does
Divides each value inside the PivotTable by The Grand Total, and
% of Grand Total
adds Number Formatting.
Divides each value in the Column by the Column Total, and adds
% of Column Total
Number Formatting.
Divides each value in the Row by the Row Total, and adds
% of Row Total
Number Formatting.
Displays values as the difference from the value of the Base
item in the Base field. Our Example takes current month and
Difference From
subracts the previous month to get the "Change" from month to
month.
Displays values as the difference from the value of the Base
item in the Base field. Our Example takes current month and
% Difference From
subracts the previous month and calculates the "% Change" from
month to month.
Displays the value for successive items in the Base field as a
Running Total in running total. Our Example adds each successive month to get
the "Running" Total or "Cummulative" Total.

Page 4 of 9
8. Dashboards
1. Dashboard:
• A Dashboard is defined as one location where we can present the information we create in a
neat an organized manner.
• When new data arrives, the Dashboard can be refreshed.
• Just like a dashboard in a car, a dashboard should present information that is required for
making good decisions.
• Data Visualization that presents useful information and metrics and will update automatically
when new data become available.
• Dashboards may contain: Tables, Charts, Data Validation, Pictures, Other visualizations of Data.
2. Effective Dashboards:
• Presents timely summary data, metrics or key performance indicators (KPI).
• Metrics/KPIs should be useful for the user/decision maker.
• Dashboard should inform rather than overwhelm.
• Should call attention to unusual metrics/KPIs that require attention or are of interest.

9. SUMIFS and similar Functions or PivotTables?

1. Advantage of PivotTable:
• Quick and easy to make.
• Conditions or Criteria in Rows or Columns area are created automatically by dragging a field to
Rows or Columns area. When you drag a field to the Rows or Columns area a unique list of items
from the field is created.
2. Disadvantage of PivotTable:
• If source data changes, you must right-click PivotTable and point to Refresh.
3. Advantage of SUMIFS:
• If source data changes, formulas update instantly.
4. Disadvantage of SUMIFS:
• Have to type out conditions/criteria for Rows or Columns area.
• When making Calculations with Conditions or Criteria, it often takes longer to create a formula
solution than it does to create a PivotTable solution.

10.Standard PivotTables or Data Model PivotTables?

1. Standard PivotTables:
• Use when you have about 50,000 rows of data or less.
• Use when you have one Proper Data Set with all your Data.
• You don’t mind applying Number Formatting every time you make a PivotTable Calculation.
• PivotTable Calculations are sufficient.
2. Data Model PivotTables:
• Good for two reasons when you have large data sets:
i. File size is reduced when your Data is in the Data Model.
ii. You can easily build reports from millions of rows of data (Excel Spreadsheet only allows
1 million rows)
• Great when you have more than one Proper Data Set as the source Data
• Allows you to add Number Formatting to Formulas.
• Has more options for calculations than a Standard PivotTable.

Page 5 of 9
11.Sales Data Dashboard Examples from Video:

1. Starting Data and Goals:

Page 6 of 9
2. Finished Dashboard:

Page 7 of 9
12.Cell Phone Data Examples from Video to Build Frequency Distribution:

Page 8 of 9
13.CPA Data Examples from Video to Build CPA Pass Rate Report:

Page 9 of 9