What is a Pivot Table in Excel?

A Pivot Table is an interactive way to quickly summarize large amounts of data. It shows patterns, trends, and insights by organizing your data in a way that’s easy to understand.

You can arrange your data in rows or columns, and switch (or pivot) between these layouts. Excel automatically calculates and displays summaries for you.

You decide whether you want to see the total, average, or another type of summary of your data. It’s like taking a big, messy box of puzzle pieces and sorting them out to see a clear picture.

What is a Pivot Table in Excel?

A Pivot Table is helpful if you have questions about your data or if you want to look for patterns in it.

Think about this example. Here is a common type of data table.

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.

Using a Pivot Table makes getting answers easy and quick.

How to Create a Pivot Table in Excel

Let’s find out how to use Pivot Tables. To start, we’ll need to check that our data is organized the right way. This step is super important to get the results we want.

Prepare your Source Data

Before creating a Pivot Table, make sure your data is set up correctly:

  • The data needs to be in a table format; columns are categories and rows are transactions (records).
  • Make sure there are no empty rows or columns.
  • Avoid including subtotal or total rows in your data.
  • Each column needs a unique title, and these titles should be in one cell each.

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.
  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 range as source.

The problem is, if you add new data rows to your table, the Pivot Table might miss them. This happens if the rows are below the last row it was set to check, such as row 108.

Making the Data Source Dynamic

To let the Pivot Table include new data rows, you should turn your original table into an official Excel Table. This way, the Pivot Table will focus on the columns for data, instead of just a fixed number of rows.

Cancel the Create PivotTable dialog box and let’s back up a couple of steps.

  • Convert the data to an Excel Table by pressing CTRL+T and click OK in the Create Table dialog box.
  • Give the table a proper name by selecting Table Design -> Properties -> 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.

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 as source.
  1. In the Create PivotTable dialog box, click OK.

Adding Fields to a Pivot Table

We see an empty Pivot Table on the left and a drag-and-drop tool called the Field List on the right, which we use to fill in the report.

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.

💡 To show the Ribbon tabs and the Field List related to the Pivot Table, your cursor needs to be inside the Pivot Table area. You can also show or hide the Field List by clicking on the PivotTable Analyze tab, then going to Show and selecting 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.

By default, the Pivot Table adds up the sales. If you prefer to count, average, or do something else, right-click on any sales number in the report, choose “Summarize Values by…” and select the method you want.

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 another way to make a pivot table in Excel that’s great if you’re not sure how to begin or just want to look through your data. Click on your data, then go to the Insert tab > Tables > Recommended PivotTables.

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

Excel will look at your data and suggest several table designs. These suggestions aim to address common questions you might have about your data.

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

These suggestions might give you the exact table you need. If not, they can help you get started more quickly towards your goal.

If none of the suggested tables work for you, you can choose Blank PivotTable in the lower-left corner. This lets you start from scratch and add 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

You can put your fields in 4 different areas to change how your pivot table looks.

Filters

When you add a field to the Filters area, it makes a dropdown menu appear in the upper-left corner above the pivot table. This menu lets you filter the table by the unique categories in that 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.

Columns

Putting fields in the Columns area makes vertical columns. These columns use the unique items from the field as their headers.

For example, if you want to show each Region as its own column without filtering by Region, move the Region field from the Filters area to the Columns area. Then, you’ll see column labels for 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.

Rows

When you add fields to the Rows area, you create horizontal rows. These rows have unique items from the field as their headers.

Value

The Value area is for fields you want to summarize, like numbers – think amounts, quantities, prices, salaries. The default way to summarize is by adding them up, but you can change this. You can also add text fields here to count how often they appear.

Pro Tip: Customizing Field Settings

You can customize the Field List by clicking the gear button to open the field settings.

Arrow pointing to the highlighted gear button in the PivotTable Fields pane.
PivotTable Field settings.

You can change how the drag-and-drop zones are arranged, and also sort and search through your field names. Using a side-by-side layout usually gives you more space than the default stacked setup.

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 might notice the numbers in the pivot table don’t look right. If you want to show your numbers in a special way, like as currency or a percentage, right-click on any sales number and choose “Number Format…”. This takes you straight to the number formatting options.

❗Do NOT pick “Format Cells” from the menu. That only changes the cells you selected, not the whole report. Using “Number Format” means your chosen style applies to new data as it’s added, which doesn’t happen with “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 want to make a report that shows a “parent” item broken down into “child” items, put the required field in the same zone as another field.

💡The order of fields in the zone decides which is the “parent” and which are the “child” items.

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

Creating a parent/child relationship between fields adds “plus/minus” buttons next to 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 buttons enable you to show detailed information for child items by clicking “+”, and hide it by clicking “-“, leaving just the summary for parent-level items visible.

You can turn these buttons on or off by going to PivotTable Analyze > Show > +/- 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 decide you don’t want a field in a Field List zone anymore, you can drag it back to the main field list or just uncheck that field’s name in the main 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.

How to Sort a Pivot Table

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

How to Add Percent of Total in Pivot Table

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).
Add Percent of Total in Pivot Table
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”.
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.

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.

How to Copy 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.

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

How to Refresh a Pivot Table

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.

Looking to take your Pivot Tables skills in Excel to the next level? We’ve got you covered! Dive into our easy-to-follow article for some expert tips and tricks. Click here to learn more.

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.