How to Create Pivot Tables in Excel

Pivot tables are Excel's most powerful tool for summarizing and analyzing data. If you have a list of sales records, survey responses, or any tabular data, a pivot table lets you slice and dice it in seconds — no formulas required.

What Is a Pivot Table?

A pivot table is an interactive summary report built from a flat data table. It groups, counts, sums, or averages your data dynamically. You drag fields into position and Excel recalculates instantly.

When to use a pivot table:

Summarizing hundreds or thousands of rows into category totals
Comparing values across multiple dimensions (e.g., product by region by month)
Finding top/bottom performers without sorting the raw data
Creating a quick cross-tabulation (crosstab) report

If you find yourself writing long SUMIF chains to replicate the same summary in different ways, a pivot table is the answer.

Step-by-Step: Create Your First Pivot Table

1. Prepare Your Data

Your source data must be a clean, flat table:

One header row at the top (no merged cells)

No blank rows or columns inside the data

Each column represents one type of information (Date, Product, Region, Sales, etc.)

2. Click Inside Your Data

Click any cell inside your data range. Excel will auto-detect the full range.

3. Insert the Pivot Table

Go to Insert tab → PivotTable → From Table/Range.

A dialog appears:

Table/Range: Confirm the range looks correct

New Worksheet is usually the best choice — keeps the pivot separate from raw data

Click OK

You'll land on a blank pivot table with the PivotTable Fields pane on the right.

Adding Rows, Columns, and Values

The field pane has four drop zones at the bottom:

Zone	Purpose
Rows	Groups your data vertically (e.g., by Product)
Columns	Spreads groups across columns (e.g., by Region)
Values	The numbers to calculate (e.g., Sum of Sales)
Filters	A top-level filter applied to the whole pivot table

Example: Sales by Product and Region

Drag Product to Rows
Drag Region to Columns
Drag Sales to Values

Excel builds a cross-tab showing total sales for each product-region combination, with grand totals on the right and bottom.

Changing the Value Calculation

By default, Excel uses Sum for numeric fields and Count for text fields. To change it:

Click the dropdown arrow on the field in the Values zone
Select Value Field Settings
Choose: Sum, Count, Average, Max, Min, etc.
Click OK

Sorting and Filtering Your Pivot Table

Sorting

Click any value in a Row or Column label, then use Data → Sort — or right-click → Sort. You can sort:

Alphabetically (A→Z or Z→A)

By a value field (e.g., sort products by highest total sales)

For value-based sorting, right-click a number in the Values area → Sort → Sort Largest to Smallest.

Filtering

Label Filters: Right-click a row label → Filter → filter by text, date range, or value threshold. Report Filters (Slicer alternative): Drag a field into the Filters zone. A dropdown appears above the pivot table for whole-report filtering. Slicers (recommended): Go to PivotTable Analyze tab → Insert Slicer. Select the fields you want. Slicers are visual filter buttons — much more user-friendly than dropdown filters, especially for dashboards.

Refreshing a Pivot Table

Pivot tables do not update automatically when your source data changes.

To refresh:

Right-click anywhere in the pivot table → Refresh

Or: PivotTable Analyze tab → Refresh

Keyboard shortcut: Alt + F5

If you added new rows to the source data beyond the original range, you may need to update the data source: PivotTable Analyze → Change Data Source.

Pro tip: Convert your source data to an Excel Table (Ctrl+T) before building the pivot table. Tables expand automatically, so the pivot table always includes new rows on refresh.

Grouping Dates

If your data has a date column in Rows, Excel 2016+ automatically groups dates by Year, Quarter, and Month. You'll see a hierarchy in the row labels.

To control grouping manually:

Right-click any date in the row labels

Select Group

Choose: Seconds, Minutes, Hours, Days, Months, Quarters, Years

Set a Start and End date if needed

Click OK

To ungroup: Right-click → Ungroup.

Common use case: You have daily transaction data. Group by Month and Year to see monthly revenue trends.

Calculated Fields

A calculated field is a custom formula that runs inside the pivot table using other value fields.

Example: You have Sales and Cost columns. Add a Profit field:

Click inside the pivot table
Go to PivotTable Analyze → Fields, Items & Sets → Calculated Field
Name it Profit
In the Formula box: = Sales - Cost
Click Add → OK

The Profit column now appears in the Values area and updates automatically when you filter or refresh. Limitations of calculated fields:

They operate on the aggregated totals, not row-by-row on the source data
Division can produce unexpected results at subtotal rows — always sanity-check

Formatting Your Pivot Table

Number Formatting

Click a value in the Values area → Value Field Settings → Number Format button. Apply currency, percentage, comma separator, etc. This formatting survives a refresh.

Layout Options

PivotTable Design tab → Report Layout:

Compact (default): All row fields in one column
Outline: Each row field in its own column, with subtotals above each group
Tabular: Each row field in its own column, no subtotals above — best for copying data out

Subtotals: PivotTable Design → Subtotals → Show at top, bottom, or turn off entirely. Grand Totals: PivotTable Design → Grand Totals → on/off for rows and columns independently.

Visual Styles

PivotTable Design tab → PivotTable Styles. Dozens of pre-built styles. Check Banded Rows or Banded Columns for easier reading.

Pivot Charts

A Pivot Chart is a chart linked to a pivot table — it filters with the pivot table automatically.

Click inside the pivot table
PivotTable Analyze → PivotChart
Choose chart type → OK

The chart and pivot table stay synchronized. Use slicers to filter both at the same time — this is the foundation of a simple Excel dashboard.

Practice These Skills

Ready to apply what you've learned? Work through hands-on exercises at LogicExcel Practice Exercises to reinforce pivot table concepts with real data sets.

Frequently Asked Questions

Can I create a pivot table from multiple sheets?

Not directly from the standard Insert > PivotTable dialog. You have two options: (1) Use Power Query to combine the sheets first, then build a pivot table on the merged result. (2) Use the legacy PivotTable and PivotChart Wizard (add it to your Quick Access Toolbar via Alt+D+P) which has a "Multiple Consolidation Ranges" option.

Why does my pivot table show "Count of Sales" instead of "Sum of Sales"?

This happens when the Sales column contains any blank cells or text values mixed with numbers. Excel defaults to Count when it detects non-numeric data. Fix the source data (fill blanks with 0, convert text-numbers to real numbers with Data → Text to Columns), then refresh the pivot table and change the field setting to Sum.

How do I show pivot table values as percentages?

Click the field in the Values zone → Value Field Settings → Show Values As tab. Choose: % of Grand Total, % of Row Total, % of Column Total, % of Parent Total, or Running Total In. This is separate from the calculation type — you can show Sum as a percentage.

Can I sort a pivot table by a specific column?

Yes. Click any cell in the column you want to sort by, then right-click → Sort → Sort Largest to Smallest (or Smallest to Largest). This sorts the entire row labels based on that column's values. It persists through refreshes.

How do I stop pivot tables from auto-grouping dates?

Go to File → Options → Data tab → uncheck "Disable automatic grouping of Date/Time columns in PivotTables". Note: this is a global setting. Alternatively, store dates as text in the source data (not recommended) or manually ungroup after creation via right-click → Ungroup.