What is a Pivot Table in Excel?
A pivot table is a tool in Microsoft Excel that lets you summarize and analyze large amounts of data quickly and easily. It helps you see patterns, trends, and insights by rearranging your data into a format that’s easier to understand. You can present different aspects of your data in rows or columns, and rotate (or pivot) between those views. The corresponding values are automatically aggregated. You choose if you want to see totals, averages, or other summaries of that data. It’s like taking a big, messy box of puzzle pieces and sorting them out to see a clear picture.
What Are Pivot Tables Used For?
Pivot tables are useful when you have specific questions about the data you want answered, as well as when you just want to explore the data to identify the patterns for analysis.
Consider the following use case. 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.
With pivot tables, answering these questions will be as easy as playing a game of computer Solitaire but in a tiny fraction of the time.
How to Create a Pivot Table in Excel
Prepare your Source Data
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 empty 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.
Insert 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 Data Source Dynamic
To give the Pivot Table the ability to “see” new rows of data, we need to convert the original table to an official 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.
Adding Fields to a Pivot Table
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 represents the headers from the source data.
NOTE: The cursor MUST be within the bounds of the Pivot Table to display the associated Ribbon tabs and the Field List. You can also hide/display the Field List by selecting PivotTable Analyze (tab) -> Show (group) -> 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.
Using Recommended PivotTables
There’s an alternative way to create a pivot table in Excel, especially useful when you don’t know where to start or you just want to explore the data.
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, populating the fields yourself.
Featured Course
Excel Essentials for the Real World
Pivot Table Fields and Areas
There are 4 areas you can arrange your fields into and you can drag and drop them between those areas to change the view of your pivot table (that’s the pivoting in action).
Adding a field to the Filters area creates a dropdown in the upper-left corner of the report on top of the pivot table, allowing you to filter your table by the distinct categories in the chosen field.
So if you add, for example, Region to Filters, you can filter the pivot table to only show values for America or only for Europe.
Adding fields to the Columns area creates vertical columns with the distinct items from this field as column headers (or “column labels”).
So, 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 area and drop it into the Columns area. You will then get two column labels: America and Europe.
Adding fields to the Rows area creates horizontal rows with the distinct items from this field as row headers (or “row labels”).
Finally, the Value area is for any fields you want to aggregate or summarize. Usually, you would add any numerical (i.e., value) fields there – amounts, quantities, prices, salaries, etc. As you saw, the default aggregation is sum but you can change it. You can also add text fields to Value, if you want to display their count.
Pro Tip: Customizing Field Settings
The Field List interface can be customized by selecting the gear button to open field settings.
You can arrange the drag-n-drop zones (areas) as well as sort and search your field names. Side-by-side usually gives you more room to work with than the default stacked layout.
Working with Pivot Tables
Formatting the Results
You’ll notice that the values in the pivot table are not properly formatted. 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.
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.
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 when expanded (after clicking on “+”) or hide it when collapsed (after clicking “-“), to only display summary information about other parent-level items.
These buttons can be toggled on and off by selecting PivotTable Analyze (tab) -> 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 pivot table headings are not pretty. “Row Labels” and “Column Labels” are not at all informative, and “Sum of …” is rather clunky. Definitely not suitable for presentation purposes.
Luckily, you can click inside the heading cell and type a more descriptive heading.
PRO TIP: 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 alphabetic, based on the row labels, 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 Smallest to Largest or Sort Largest to Smallest.
Customizing the Pivot Table Layout and Other Settings
There are dozens of customizable options available to Pivot Tables. Some of the more widely used features on the Design tab include:
- 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
I encourage you to experiment with different combinations of cosmetic features to create visually interesting reports.
NOTE: The Design tab is contextual, meaning it only appears when you select a cell inside a pivot table.
Automatic Column Resize Based on Content
The Pivot Table will automatically resize the columns to ensure that no data is being visually truncated. This happens whenever you rearrange the fields in the Field List, sort the data, refresh, or perform any similar operation. And 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 resizing behavior, select PivotTable Analyze (tab) -> PivotTable (group) -> Options.
In the PivotTable Options dialog box, uncheck “Autofit column widths on update”.
Featured Course
Visually Effective Excel Dashboards
Pivot Table Tips & Tricks
Creating Additional Aggregations
If you recall, one of our questions to be answered was what percentage of total sales each customer generated.
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 area (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 goldmine of mathematical wealth to be found there.
3. Remember to rename the column header afterwards.
We now have a completed report that answers all of our questions about customers. Now it’s time to look at products.
Copying a Pivot Table
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.
If you want to move a pivot table, you will find a handy feature on the PivotTable Analyze tab in the Actions group. It allows you to specify the destination where you want to move the selected pivot table.
To permanently delete a pivot table, simply select the whole pivot table report (including filters, if any; you can use “Select” -> “Entire PivotTable” from the Actions group) and press Delete.
More Pivot Table Features to Explore
As you become advanced in the use of pivot tables, you will be able to create Calculated Fields – custom calculations that go beyond the standard aggregations available for the Values fields. You will work with custom groups, to create category classifications that may not be available in your data source.
This includes date groupings, which are applied automatically in Excel pivot tables whenever you add dates to your report, but which you can also customize to suit your needs.
Featured Course
Master Excel Power Pivot & DAX (Beginner to Pro)
Filtering a Pivot Table with Slicers
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.
Updating the Pivot Table 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.
- Use the shortcut: CTRL + ALT + F5.
Next time you’ll want to easily analyze data, you will know how to create a pivot table in Excel and how to make the most of it.
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Black Belt Excel Package
Leila Gharani
I'm a 6x 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.
