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.

Fragment of an Excel spreadsheet with a table of raw sales data.
Data set to analyze.

We need to answer the following questions about our products:

  1. The total sales for each product.
  2. The total sales for all products.
  3. The ranking order from most sales to least sales by product.

We need to answer the following questions about our customers:

  1. The total sales for each customer.
  2. 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:

  1. Click on a cell that is part of your data set.
  2. Select Insert (tab) -> Tables (group) -> PivotTable.
Location of the PivotTable on the Insert tab of the Excel ribbon.
Location of the PivotTable on the Insert tab.
  1. In the Create PivotTable dialog box, notice that the selected range is hard-coded to a set number of rows and columns.
Create PivotTable dialog box with a range selected as source of data to be analyzed.
Create PivotTable dialog box with range as source.

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.

  1. (UPDATED) – Convert the table to an Excel Table by pressing CTRL-T and click OK in the Create Table dialog box.
  2. (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.

  1. Select Table Design -> Tools (group) -> Summarize With PivotTable.
Location of the Summarize with PivotTable feature on the Table Design tab in the Tools group.
Location of the Summarize with PivotTable feature on the Table Design tab.

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”.

Create PivotTable dialog box with a table called TSales as source.
Create PivotTable dialog box with a table as source.
  1. 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).

An empty pivot table in the Excel grid and PivotTable Fields pane on the right.
Pivot Table shell and Field List.

The Field List represents the headers from the source data.

Fragment of an Excel table with header row highlighted and an arrow pointing to those header names in a PivotTable Field List.
Source table headers as pivot table fields.

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.

Location of the Field List in the PivotTable Analyze tab.
Location of the Field List toggle in the PivotTable Analyze tab.

To begin answering our questions about customers:

  1. Click and hold the Company Name entry in the Field List and drag it inside the Rows.
  2. Click and hold the Sales USD entry in the Field List and drag it inside the Values.
PivotTable Fields pane with Customer Name selected and added to Rows, and Sales USD selected and added to Values as Sum of Sales USD.
Populating the pivot table using PivotTable Fields.

We are presented with the following report.

Pivot Table with company names as row labels and Sum of Sales USD as values, with Grand Total on the bottom
Pivot Table showing Sales by Company.

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.

Cell selected inside pivot table in the Sum of Sales column and right click menu with "Summarize Values By" highlighted and expanded to show available aggregation options.
Changing default aggregation.

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.

Location of Recommended PivotTables in the Insert tab of the Excel ribbon.
Location of Recommended PivotTables in the Insert tab.

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.

Recommended PivotTables dialog box with several pivot table suggestions.
Recommended PivotTables dialog box.

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

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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.

Pivot Table in the Excel grid with Region as filter in row 1, and Region selected and added to the Filters area in the PivotTable Fields
Pivot table with Region in Filter.

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.

Pivot table filtered by region America.
Pivot table filtered by region.

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.

Pivot Table in the Excel grid with regions as column labels, and Region selected and added to the Columns area in the PivotTable Fields
Pivot table with Region in Columns.

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.

Arrow pointing to the highlighted gear button in the PivotTable Fields pane.
PivotTable 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.

Layout options for the pivot table Field List with "Fields Section and Areas Section Side-By-Side" selected, and "Sort in Data Source Order" selected.
Layout options for the Field List.

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.

Number Format in the Format Cells dialog box.
Number Format in the Format Cells dialog box.

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.

Pivot Table in the Excel grid with customer name and product description as row labels, regions as column labels, and in the PivotTable Fields pane, Product Description selected and added to the Rows area under Customer Name.
Pivot table with Customer Name and Product Description in Rows.

The hierarchy automatically creates subtotals for each parent-level item.

Pivot Table in the Excel grid with customer name and product description as row labels, with subtotals at parent level (company name) highlighted.
Pivot table with Customer Name as “parent” and Product Description as “child” in row hierarchy.

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.

Pivot Table in the Excel grid with customer name and product description as row labels, with hierarchy collapsed in 3 rows ("+" next to company name), and expanded in 1 row ("-" next to company name, product descriptions below).
Hierarchical pivot table with only one row expanded to show “child” details.

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.

Location of the "+/- Buttons" toggle on the PivotTable Analyze tab in the Show group.
Location of the “+/- Buttons” toggle on the PivotTable Analyze tab.

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.

Removing "Product Description" from the Rows area of PivotTable Fields.
Removing a field from PivotTable Rows.

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.

2 views of the same pivot table, with the header "Sum of Sales USD" in one table and just "Sales" in the other.
Renaming pivot table headers.

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.

Pivot table with a value field selected and Sort options highlighted in the right-click menu.
Sorting a pivot table by Value field.

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
Pivot Table Design tab with Layout, Styles and Style Options.
Pivot Table Design tab.

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.

Location of PivotTable Options in the PivotTable Analyze tab.
PivotTable Options in the PivotTable Analyze tab.

In the PivotTable Options dialog box, uncheck Autofit column widths on update”.

PivotTable Options dialog box with an arrow pointing to (highlighted) "Autofit column widths on update".
PivotTable Options dialog box.

Featured Course

Visually Effective Excel Dashboards

Create eye-catching Excel Dashboards with actionable tips you can use right away.
Learn More
Excel dashboards course cover

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.

  1. Add the Sales USD field a second time to the Values area (placed below the original entry).
Pivot Table in Excel grid with "Sales USD" added twice to the Value area in the PivotTable Fields (first instance renamed to "Sales", the second including aggregation type "Sum of Sales USD").
Sales field repeated in the Values area of PivotTable Fields.
  1. Right-click one of the values in the newly-added column and select “Show Values As” -> “% of Grand Total”.
A cell selected in the second Sum of Sales USD, with Show Values As and Percentage of Grand Total highlighted in the right-click menu.
Percentage of Grand Total in the Show Values As menu.

Take some time to examine the “Show Values As” list.  There is a goldmine of mathematical wealth to be found there.

2 views of the same pivot table, with the "Sum of Sales USD" shown as whole numbers in one table and as percentage of grand total in the other.
Sales amount shown in the pivot table as actual values and as percentage of grand total (before and after).

3. Remember to rename the column header afterwards.

Pivot table with Sales (actual values) and Sales % (percentage of grand total) in columns and customers in rows.
Sales amount per customer shown in the pivot table as actual values and as percentage of grand total.

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.

  1. Highlight the original Pivot Table report.
  2. Press CTRL-C on the keyboard.
  3. 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.)
  4. Press CTRL-V to paste a copy of the Pivot Table.
  5. Remove any unneeded field names from the drop zones and add the needed field names.
  6. If needed, sort the new Pivot Table by Values.
Excel grid with 2 pivot tables, one with Sales by Customer, second with Sales by Product.
Copy-pasting pivot tables.

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.

Location of Move PivotTable feature on the PivotTable Analyze tab and Move PivotTable dialog box.
Move PivotTable feature.

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)

Transforming data into meaningful reports can feel like being in an Olympic event. It is hard! Join the course and learn how to use Excel’s Power Pivot and Data Modelling tools to make reporting easy and fast.
Learn More
Power Pivot DAX course cover

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:

  1. Click on any cell in a Pivot Table.
  2. Select PivotTable Analyze (tab) -> Filter (group) -> Insert Slicer.
Location of Insert Slicer on the PivotTable Analyze tab in the Filter group.
Location of Insert Slicer on the PivotTable Analyze tab.
  1. In the Insert Slicer dialog box, select the categories you wish to filter.
Insert Slicers dialog box.
Insert Slicers dialog box.

We are presented with a Slicer(s) for the item(s).

Region Slicer with America and Europe buttons between two pivot tables in Excel grid.
A Region slicer added to our report.

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:

  1. Select the Slicer.
  2. Select Slicer (tab) -> Slicer (group) -> Report Connections.
Location of Report Connections in the Slicer tab.
Report Connections in the Slicer tab.
  1. In the Report Connections dialog box, select as many (or as few) Pivot Tables you need to achieve the desired behavior.
Report Connections dialog box for the Region slicer, with checkmarks next to PivotTable2 and PivotTable3.
Report Connections dialog box.

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.

Excel Download Practice file

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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.