PivotTables

PivotTable showing Total Sales by Region Product SalesPerson

What is a PivotTable?

A PivotTable is an Excel feature that helps you quickly explore, organize, and summarize large sets of data. It’s ideal for turning raw information into meaningful insights — without writing any complex formulas.

In simple terms, a PivotTable works like a smart summary engine. It takes your detailed data and displays it in a clean, organized format. For example, you can find:

  • total sales by region,
  • average salary by department,
  • number of employees by job title, or
  • monthly revenue trends.

Instead of typing formulas, just drag and drop the fields you want to analyze — Excel handles the calculations automatically.

Why is it called “PivotTable”?

The term PivotTable comes from its ability to pivot, or rotate, your data.
This means you can instantly look at your information from different angles.

For instance:

  • start with Sales by Region,
  • then switch to Sales by Product,
  • or combine both for Sales by Region and Product.

All of this takes just a few clicks — no formulas and no duplication required.

Creating a PivotTable – Preparation Step

Before creating a PivotTable, it’s important to make sure your source data is well-structured. A clean dataset ensures that your PivotTable works smoothly and gives accurate results.

Follow these simple rules for best results:

  • Each column must have a unique header.
  • There should be no blank rows or columns within your data range.
  • Use consistent data types — for example, numbers, text, or dates — in each column.
  • Every row should represent one complete record.

Tip: You can keep your data as it is, or better yet, convert it into a Table by going to Insert → Table.

This makes the range dynamic — meaning your PivotTable will automatically include new data added later.

Example:

Here’s how your source data might look before creating a PivotTable:

Example dataset for creating a PivotTable in Excel.
A clean, structured dataset ensures your PivotTable summarizes data correctly.

Creating a PivotTable

Once your data is ready, creating a PivotTable takes just a few clicks.
Follow these simple steps:

  1. Click anywhere inside your data range or table.
  2. Go to the Insert tab on the Ribbon.
  3. Click PivotTable.
  4. In the Create PivotTable dialog, check that your table or range is correct.
  5. Choose where you want your PivotTable to appear:
    • New Worksheet (recommended), or
    • Existing Worksheet.
  6. Finally, click OK.
Steps to create a PivotTable in Excel using the Insert tab.
Select your data, go to Insert → PivotTable, choose the location, and click OK.

Once you click OK, Excel creates a blank PivotTable and displays the PivotTable Fields pane on the right side of your worksheet.

This pane is where you’ll decide how your data is summarized.
It contains two key parts:

  1. Fields List – These are the column names from your original data or table.
  2. Areas – These define how your PivotTable is built.

PivotTable Fields pane showing Rows, Columns, Values, and Filters areas in Excel.

    The PivotTable Fields pane displays your column names and layout areas for building a PivotTable.

Understanding the PivotTable Fields Pane

After creating a PivotTable, Excel opens the PivotTable Fields pane on the right side of your worksheet.
This is where you control what data appears and how it’s summarized in your PivotTable.

The pane is divided into four main areas, each with a specific purpose:

AreaWhat It Does
RowsDisplays data vertically (as row labels or categories).
ColumnsDisplays data horizontally (as column headings).
ValuesPerforms calculations such as Sum, Count, or Average.
FiltersLets you filter the entire PivotTable based on a selected field.

To build your report, simply drag and drop fields (your column headers) from the list above into these areas.
Each combination creates a different view of your data.

PivotTable in Action – Summarizing your Data

Example: Total Sales by Region

Now that your PivotTable is ready, let’s see it in action!
We’ll create a quick summary to find total sales by region.

Follow these steps:

  • In the PivotTable Fields pane, check the box next to Region.
    • Excel automatically places it in the Rows area.
  • Next, check Sales.

    • It goes into the Values area, showing the Sum of Sales by default.

Result:
You’ll instantly see the total sales for each region displayed in your PivotTable — all calculated automatically, without a single formula!

Example of PivotTable summarizing total sales by region in Excel.
Example of PivotTable summarizing total sales by region in Excel.

Viewing and Comparing Data Interactively

Once you’ve added multiple fields to your PivotTable, you can switch between different views to analyze your data from multiple angles.

For example, you can:

  • View Sales by Salesperson and Region,
  • Compare Sales by Product and Region, or
  • Examine Sales by Date and Region.

PivotTables let you instantly switch between views — Sales by Region, Product, or Date — all with simple drag and drop.

Example: Total Sales by Region, Product, and Salesperson

Now, let’s make your PivotTable even more insightful by adding more fields to your analysis.

Here’s how:

  • In the PivotTable Fields pane, drag Product → Columns
  • Then drag Salesperson → Filters
PivotTable showing total sales by region, product, and salesperson in Excel.
Add Product to Columns and Salesperson to Filters to analyze data from multiple perspectives.

Your PivotTable will now show total sales by region and product, with a filter for salesperson at the top.
This allows you to view results for individual salespersons or compare multiple ones easily.

Excel PivotTable views showing sales summarized by different fields like Region, Product, and Date.

Understanding the Multi-Select Filter Issue

From the earlier example, you can see that both Alex and John work in the East region.
To view their combined sales results, you can select multiple items in the Salesperson filter.

Excel PivotTable filter showing multi-select option for salespersons.
Selecting multiple items in a PivotTable filter to view combined results.

However, there’s one small issue.

When you apply a multi-select filter, Excel only displays “(Multiple Items)” in the filter label — not the actual names you’ve selected.
This makes it difficult for anyone reading the PivotTable to know exactly which salespersons are included in the report.

PivotTable showing Multiple Items label after applying multi-select filter.
When multiple items are selected, Excel shows only ‘(Multiple Items)’ — which can be unclear to readers.

Solution:
To make your selections more visible and easier to understand, use Slicers instead of the Filters area.
Slicers provide clear, clickable buttons that instantly show which values are active. We’ll explore this feature in the Slicers section.

Removing a Field from the PivotTable

If you’ve added a field by mistake or simply want to adjust your report, you can easily remove any field from the PivotTable.

There are two quick ways to do this:

  1. Drag the field out of the Areas box back into the Fields List.
  2. Or, simply uncheck the box next to that field in the list.
Removing a field from a PivotTable in Excel by dragging or unchecking.
You can remove a field either by dragging it out of the area or by unchecking its box.

Either method instantly updates your PivotTable layout — no need to refresh or re-create it.

Changing Calculations in PivotTable

By default, Excel uses the Sum function for numbers placed in the Values area of a PivotTable.
For example, it automatically shows Sum of Sales, which is the most common calculation used in reports and analysis.

However, sometimes you might need a different type of calculation — such as Average, Count, Max, or Min.
You can change this easily in two ways.

  1. From the PivotTable Directly
  2. Using Value Field Settings on the field in the Values Area

Method 1: From the PivotTable Directly

  • Right-click on any number in the PivotTable.
  • Choose Summarize Values By from the context menu.
  • Select the function you want — Average, Count, Max, Min, etc.

As soon as you select it, Excel recalculates the entire PivotTable using the chosen function.

Changing calculation type in a PivotTable using Summarize Values By menu in Excel.
Right-click any value → Summarize Values By → choose from Sum, Count, Average, Max, or Min.

Tip: This feature gives you flexibility to perform quick data comparisons or audits — all without writing formulas.

This flexibility makes PivotTables powerful for reports, audits, and data comparisons.

Method 2: Changing Calculations from Values Area

You can also change a calculation directly from the PivotTable Fields pane — without right-clicking inside the PivotTable.

Here’s how:

  • In the Values area of the PivotTable Fields pane, click the field name you want to modify (for example, Sum of Sales).
  • From the menu that appears, choose Value Field Settings.

Accessing Value Field Settings in Excel PivotTable to change calculation type.

Click the field name in the Values area → choose Value Field Settings to change or format calculations.

Using the Value Field Settings Dialog

After you open the Value Field Settings dialog, you can customize how your PivotTable performs calculations and displays results.

Follow these steps:

  • In the Custom Name box, enter a clear name for your calculation — for example, Sales Count.
  • Under Summarize Values By, select the function you want, such as Count.
  • To format the numbers, click Number Format.
  • In the Format Cells dialog, choose Number as the category.
  • Set Decimal places to 0 (so the values appear as whole numbers).
  • Click OK, and then click OK again to close both dialogs.

Your PivotTable will now update instantly with the new name, calculation type, and number format applied.

Excel PivotTable Value Field Settings dialog showing how to rename, change calculation, and apply number format.
In Value Field Settings, rename the field, select Count, and format numbers to show as whole values.

Slice and Dice Data with PivotTable

Once your PivotTable is ready, you can explore your data from different perspectives — all with a few quick drags and drops.

Simply drag fields between the Rows, Columns, and Values areas to instantly change how your data is summarized.

For example, you can easily view:

  • Total Sales by Region and Salesperson
  • Total Sales by Region and Date
  • Total Sales by Salesperson and Date

Each arrangement gives you a fresh view of the same data — helping you uncover patterns, compare performance, or answer specific questions instantly.

Tip: PivotTables are ideal for ad-hoc analysis. You don’t have to write new formulas — just rearrange the fields to get new insights.

Excel PivotTable showing data sliced by Region, Salesperson, and Date.
Rearrange fields to instantly view totals by different dimensions — Region, Salesperson, or Date.

PivotTable Options

PivotTable Options

After you create a PivotTable, Excel automatically adds two new tabs to the Ribbon:

  1. PivotTable Analyze
  2. Design

These tabs give you quick access to tools that help you manage, format, and refresh your PivotTable.

Excel Ribbon showing PivotTable Analyze and Design tabs for customizing PivotTables.
Use the PivotTable Analyze and Design tabs to manage, refresh, and style your PivotTable.

PivotTable Analyze Tab

The PivotTable Analyze tab contains all the options you need to work with your data effectively.

You can use it to:

  • Insert Slicers and Timelines for fast, visual filtering.
  • Refresh your PivotTable when new data is added to the source.
  • Change the data source or link to a different table.
  • Create PivotCharts to visualize your summaries.
  • Show or hide elements such as field lists and buttons.
Excel PivotTable Analyze tab showing options for slicers, refresh, and data management.
Use the Analyze tab to manage data, add slicers, and create PivotCharts.

Design Tab

The Design tab focuses on formatting and presentation.

From here, you can:

  • Apply PivotTable styles for a clean, professional look.
  • Add or remove banded rows and columns for better readability.
  • Control subtotals and grand totals display options.
  • Adjust the report layout to show data in tabular or compact form.

PivotTable Formatting Options

Play around with these options and see the changes in the PivotTable.

Slicers

Slicers are one of the most powerful tools in Excel for filtering PivotTables and charts visually.
They make it easy to click and filter your data without using drop-down menus — perfect for creating interactive reports and dashboards.

You can add a Slicer to a PivotTable in two different ways:

  1. Using Insert Slicer from the PivotTable Analyze tab.
  2. Using Add as Slicer directly from the Fields List.

Before adding a slicer, make sure you click inside your PivotTable so the PivotTable Analyze tab becomes active.

Adding a Slicer from the PivotTable Analyze Tab

  1. Go to the PivotTable Analyze tab on the Ribbon.
  2. Click Insert Slicer
  3. In the Insert Slicers dialog box, select the field you want to use — for example, Salesperson.
  4. Click OK.

Excel will insert a slicer box on your worksheet. You can now use it to filter your PivotTable results interactively.

Adding a slicer to an Excel PivotTable using the Insert Slicer option in the PivotTable Analyze tab.
Use Insert Slicer from the PivotTable Analyze tab to add a visual filter for fields like Salesperson.

Adding Slicer From Fields List of the PivotTable

You can also insert a slicer directly from the PivotTable Fields List — it’s quick and convenient when you’re already working with field placements.

Here’s how:

  1. In the Fields List, locate the field you want to use as a slicer — for example, Salesperson.
  2. Right-click the field name.
  3. From the menu, choose Add as Slicer.

Excel immediately inserts a slicer box for that field. You can then resize or move it anywhere on your sheet.

Adding a slicer to a PivotTable by right-clicking a field in the Fields List
Right-click a field in the Fields List and select Add as Slicer to insert a visual filter instantly.

Using Slicers with PivotTables

After you insert a slicer (using either method), it appears next to your PivotTable with all items selected by default.

Excel PivotTable with slicer showing all salespersons selected.
A slicer starts with all values selected by default — ready for interactive filtering.

To filter the data visually:

  1. Click on a name in the slicer — for example, Alex.
  2. Hold down Ctrl and click another name — for example, John.

Both Alex and John are now highlighted.
The PivotTable instantly updates to show combined totals for the selected salespersons.

Excel slicer showing Alex and John selected with filtered PivotTable results.
Selecting multiple items in a slicer instantly filters the PivotTable and clearly shows which items.

This approach is far more transparent than using the Filters area in the PivotTable.
With slicers, it’s immediately clear which values are selected — no more guessing what “(Multiple Items)” means.

With any of the above two methods, the Slicer appears with all the Values selected.