Below we have a typical tabular set of data.
We need to answer the following questions about our products:
- The total sales for each product.
- The total sales for all products.
- The ranking order from most sales to least sales by product.
We need to answer the following questions about our customers:
- The total sales for each customer.
- The percentage of sales for each customer.
Answering these questions will be as easy as playing a game of computer Solitaire but in a tiny fraction of the time.
Don’t Make These Beginner Mistakes
Before you begin making the Pivot Table, you need to ensure that your source data adheres to certain structural rules.
- The data needs to be in a tabular structure; columns are categories and rows are transactions (records).
- There are no black rows or columns in the table.
- There are no subtotal or grand total rows in the table.
- Each column has a unique heading, and the headings must be contained in a single cell.
Creating the Pivot Table
To create a Pivot Table, perform the following steps:
- Click on a cell that is part of your data set.
- Select Insert (tab) -> Tables (group) -> PivotTable.
- In the Create PivotTable dialog box, notice that the selected range is hard-coded to a set number of rows and columns.
The potential problem is that if we add new rows of data to the table, the Pivot Table will not see beyond the originally defined row number; in this case, row 108.
Making the Table Dynamic
To give the Pivot Table the ability to “see” new rows of data, we need to convert the original table to a proper Excel Table. By doing this, the Pivot Table will look at the field (column) for data, not a set number of rows.
Cancel the Create PivotTable dialog box and let’s back up a couple of steps.
- (UPDATED) – Convert the table to an Excel Table by pressing CTRL-T and click OK in the Create Table dialog box.
- (UPDATED) – Give the table a proper name by selecting Table Design (tab) -> Properties (group) -> and edit the default name (most likely “Table1” to something more meaningful, like “TSales”.
NOTE: This last step is optional, but it will help you keep track of tables more easily.
Now let’s get back to creating the Pivot Table.
- Select Table Design -> Tools (group) -> Summarize With PivotTable.
Notice how our range selection is no longer fixed to a set number of rows and columns. The selection is looking at the table named “TSales”.
- In the Create PivotTable dialog box, click OK.
We are presented with an empty shell of a Pivot Table (left) along with a drag-n-drop interface called the Field List for populating the report (right).
The Field List interface can be customized by selecting the gear button.
You can arrange the drag-n-drop zones as well as sort and search your field names.
The Field List presents a list of the headers from the source data.
NOTE: The cursor MUST be within the bounds of the Pivot Table to display the associated Ribbon features. You may also hide/display the Field List by selecting PivotTable Analyze -> Show -> Field List.
To begin answering our questions about customers:
- Click and hold the Company Name entry in the Field List and drag it inside the Rows
- Click and hold the Sales USD entry in the Field List and drag it inside the Values
We are presented with the following report.
The default aggregation method is to SUM the sales. If you would rather COUNT or AVERAGE (among other things), right-click on any sales value in the report and select “Summarize Values by…” and click the desired aggregation method.
Formatting the Results
If you wish to display your calculation results in a different format, such as Currency or Percentage, right-click on any sales value in the report and select “Number Format…”. This will take you directly to the number formatting section of the Format Cells dialog box.
IMPORTANT: Do NOT select “Format Cells” from the right-click menu. This would format only the selected cells, not the report results. The advantage to Number Format is that the formatting extends to new entries as the data grows. This behavior does not occur when using Format Cells.
Global Filtering of the Report
The Field List contains a zone labeled Filters. Any field placed here will appear in the upper-left corner of the report and provide a filter for the entire report.
Any filter used from the Filters zone applies to the entire table.
Rearranging Report Fields
If you would rather display each Region in the report as separate aggregations, but you don’t want to filter for each Region, you can drag the Region field from the Filters zone and drop it into the Columns zone.
Creating a Multi-Level Report
If you need to create a hierarchical report that breaks a “parent” item into “child” items, place the needed field in the same zone as an existing field.
NOTE: The stacking order in the zone determines the “parent/child” relationship.
The hierarchy automatically creates subtotals for each parent-level item.
Customizing the Pivot Table Formatting
There are dozens of customizable options available to Pivot Tables. Some of the more widely used features include (from the Design tab):
- Turning on/off subtotals
- Turning on/off grand totals
- Altering the layout of the table
- Adding/removing blank rows between parent-level rows
- Repeating parent-level labels to child-level rows
- Applying a pre-defined color scheme
- Coloring (highlighting) alternate rows/columns/headings
It’s encouraged that you experiment with different combinations of cosmetic features to create visually interesting reports.
Collapsing and Expanding Hierarchies
When you create a parent/child relationship between fields, you gain access to “plus/minus” buttons to the left of each parent-level item.
These allow you to display detailed child-level information about selected items while display summary information about other parent-level items.
These buttons can be hidden or re-displayed by selecting Pivot Table Analyze -> Show (group) -> +/- Buttons.
Removing a Field from the Field List
If you no longer wish to have a specific field in a Field List zone, you can either drag the field name from the zone back into the master field list, or you can uncheck the requisite field name in the master field list.
Updating the Default Headings
The automatic headings are usually less than desirable. You can click inside the heading cell and type a more descriptive heading.
BEWARE: Pivot Tables don’t like when you name a heading that matches an existing heading. One trick is to add a space before or after the custom heading to avoid the conflict.
Sorting the Report Results
The default sort order is by the row heading, but report readers are typically interested in things like “top performers” or “fewest incidents”. We can easily sort the report by right-clicking an aggregation value and selecting Sort -> Sort XX to YY.
Automatic Column Resize Based On Content
The Pivot Table will automatically resize the columns to ensure that no data is being visually truncated. In most cases, this is a welcomed behavior.
However, if you have resized your columns to a size of your choosing, you will likely lose those dimensions on the following update.
To deactivate this automatic column resize behavior, select PivotTable Analyze (tab) -> PivotTable (group) -> Option.
In the PivotTable Options dialog box, deselect the “Autofit column widths on update” feature.
Creating Additional Aggregations
If you recall, one of our questions to be answered was what percentage of total sales does each customer occupy?
We can determine this percentage in a few clicks without writing a single formula.
- Add the Sales USD field a second time to the Values zone (placed below the original entry).
- Right-click one of the values in the newly-added column and select “Show Values As” -> “% of Grand Total”.
Take some time to examine the “Show Values As” list. There is a GOLD MINE of mathematical wealth to be found here.
We now have a completed report that answers all our Customer questions.
Updating the Report
When the underlying data changes (additions, deletions, or modifications), you will need to “refresh” the report to reflect the changed data.
There are many ways to refresh the Pivot Table report.
- Right-click on the Pivot Table report and select Refresh.
- Select PivotTable Analyze (tab) -> Data (group) -> Refresh.
- Select Data (tab) -> Queries & Connections (group) -> Refresh All.
Creating Our Products Report
A trick to creating a second report is to duplicate the first report, then adjust the assigned fields.
- Highlight the original Pivot Table report.
- Press CTRL-C on the keyboard.
- Click in an empty cell on a new sheet or to the side of an existing Pivot Table (ensure you leave enough blank columns to allow the original report to “grow” if needed.)
- Press CTRL-V to paste a copy of the Pivot Table.
- Remove any unneeded field names from the drop zones and add the needed field names.
- If needed, sort the new Pivot Table by Values.
An advantage of copying an existing report is that all the cosmetic settings (color, number formatting, totals, etc…) are retained in the duplicate report.
Adding Slicers to Expedite Filtering
If you’ve used Slicers you know how easy filtering is performed as opposed to old-school dropdown filters.
We can add slicers to out Pivot Tables to expedite the filtering process. Plus, it’s fun and looks cool.
The Main Advantage to Slicers
Unlike filters that are built into the Pivot Table, Slicers can filter by ANY category in the data set.
Where traditional filters can only filter by what is in the report, Slicers manipulate the “back end” data, which is then carried forward to the Pivot Table.
To add Slicers to Pivot Table reports:
- Click on any cell in a Pivot Table.
- Select PivotTable Analyze (tab) -> Filter (group) -> Insert Slicer.
- In the Insert Slicer dialog box, select the categories you wish to filter.
We are presented with a Slicer(s) for the item(s).
The slicer only affects the Pivot Table that was selected when the Slicer was created. If you need the Slicer to filter multiple Pivot Tables:
- Select the Slicer.
- Select Slicer (tab) -> Slicer (group) -> Report Connections.
- In the Report Connections dialog box, select as many (or as few) Pivot Tables you need to achieve the desired behavior.
Alternate Way to Create Pivot Tables
Excel possesses a great feature that allows you to create completed Pivot Tables with a single click.
If you click in the data source you can then select Insert (tab) -> Tables (group) -> Recommended PivotTables.
Excel will analyze your data and present several suggested table designs to answer what could be some of the most common questions about your data.
These may deliver the exact table you were looking for. If not, they may at least provide a faster start to reaching your goal.
If you don’t like any of the suggested tables, you can click Blank PivotTable in the lower-left corner and start with a clean slate.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 5x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
