No Ticket Needed

Appearance

4 min fix 4 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Click anywhere in your data, go to the Insert tab, click PivotTable, and drag your fields into the Rows and Values areas.

How to Create a PivotTable in Excel ​

Applies to: Microsoft 365 (Excel desktop, Excel for the Web)
Article Type: How-To
Last Updated: 2026-03-09

Summary ​

A PivotTable summarizes large amounts of data into an interactive table, so you can spot patterns, compare totals, and drill into details. This article explains how to create, customize, and refresh a PivotTable.

Prerequisites ​

  • Microsoft Excel (desktop or web version).
  • Your data must be in a structured table or range with column headers in the first row and no blank rows or columns within the data.

Instructions ​

1. Insert a PivotTable ​

Create a PivotTable from your data range.

  1. Click any cell inside your data range.
  2. Go to Insert > PivotTable.
  3. Excel automatically selects the data range. Verify it is correct in the Table/Range field.
  4. Choose where to place the PivotTable:
    • New Worksheet (recommended for most use cases).
    • Existing Worksheet — Click the cell where you want the PivotTable to start.
  5. Click OK. A blank PivotTable and the PivotTable Fields pane appear.

2. Add Fields to the PivotTable ​

Drag fields to build your summary view.

  1. In the PivotTable Fields pane on the right, you will see a list of your column headers.
  2. Drag fields to one of four areas:
    • Rows — Categories displayed as rows (e.g., Product Name, Department).
    • Columns — Categories displayed as columns (e.g., Quarter, Region).
    • Values — The numbers to summarize (e.g., Sum of Sales, Count of Orders).
    • Filters — Add a dropdown filter to the top of the PivotTable.
  3. The PivotTable updates in real time as you add or remove fields.

Tip: By default, Excel uses Sum for numeric fields and Count for text fields. To change the calculation, click the dropdown arrow on the field in the Values area and select Value Field Settings.

3. Filter and Sort the PivotTable ​

Narrow down results to the data you need.

  1. Click the dropdown arrow on any Row or Column heading in the PivotTable.
  2. Uncheck items you want to hide, or use the Search box to find specific values.
  3. To sort, click the dropdown arrow and choose Sort A to Z, Sort Z to A, or More Sort Options for custom sorting.
  4. To use a top-level filter, drag a field to the Filters area and use the dropdown that appears above the PivotTable.

4. Refresh the PivotTable ​

Update the PivotTable when underlying data changes.

  1. Click anywhere in the PivotTable.
  2. Go to PivotTable Analyze > Refresh (or right-click the PivotTable and select Refresh).
  3. If you added new rows to your source data, you may need to update the data range:
    • Click the PivotTable, go to PivotTable Analyze > Change Data Source, and adjust the range.

Note: If a refresh does not bring in your new rows, or the report shows #REF! after a sheet was renamed, work through How to Refresh and Fix a PivotTable When Data Changes — it covers making the source grow on its own, clearing deleted names out of the filter dropdowns, and stopping formatting resetting on every refresh.

5. Format the PivotTable ​

Apply a polished look.

  1. Click anywhere in the PivotTable.
  2. Go to the Design tab.
  3. Choose a style from the PivotTable Styles gallery.
  4. Toggle Banded Rows, Banded Columns, or Row/Column Headers under PivotTable Style Options.

Troubleshooting ​

INFO

PivotTables do not auto-refresh when source data changes. Always click Refresh after updating your data, or set up auto-refresh on file open via PivotTable Analyze > Options > Data > Refresh data when opening the file.

Symptom / ErrorPotential CauseSolution
PivotTable shows incorrect totalsSource data changedClick Refresh on the PivotTable Analyze tab. If rows were added, update the data source range.
Field list pane not visibleClicked outside PivotTableClick any cell inside the PivotTable to bring back the Fields pane. Or go to PivotTable Analyze > Field List.
Blank rows appearing in PivotTableEmpty rows in source dataRemove blank rows from your source data and refresh the PivotTable.