How to Create a Pivot Table in Excel
Learn how to create a pivot table in Excel, prepare data, choose fields, summarize values, refresh results, avoid mistakes, and read FAQs.
Quick Answer
To create a pivot table in Excel, organize your data with headers, click any cell in the data range, choose Insert > PivotTable, select where to place it, then drag fields into Rows, Columns, Values, and Filters.
What You Need
- Excel workbook
- Structured data with headers
- No completely blank header rows
- Column fields to summarize
- Basic idea of the question you want answered
Safety Precautions
- Save a copy before changing important data.
- Make sure each column has a clear header.
- Check source data for blanks or mixed formats.
- Refresh the pivot table after source data changes.
- Verify summaries before using them for decisions.
Step-by-Step Instructions
- Step 1
Prepare the data
Use one header row and consistent columns.
- Step 2
Click in the data
Select any cell inside the range.
- Step 3
Insert pivot table
Go to Insert > PivotTable.
- Step 4
Choose location
Place it in a new worksheet or existing worksheet.
- Step 5
Add fields
Drag fields into Rows, Columns, Values, and Filters.
- Step 6
Adjust summary
Change Values from count to sum or another calculation if needed.
- Step 7
Refresh later
Right-click and refresh after source data changes.
Practical Example
Example: You summarize sales data by dragging Region to Rows, Product to Columns, and Revenue to Values.
Common Mistakes
- Using data without headers
- Including blank rows in the range
- Forgetting to refresh
- Leaving numbers stored as text
- Using Count when Sum is needed
- Not checking source data accuracy
Troubleshooting
Excel counts instead of sums
Check that source numbers are numeric and change Value Field Settings.
New data is missing
Expand the source range or use an Excel table, then refresh.
Field list is confusing
Rename source headers clearly.
Blank appears in results
Clean blank cells or filter them out.
Helpful Internal Links
FAQs
What is a pivot table?
It is a tool that summarizes and rearranges data without changing the source.
Do I need formulas for pivot tables?
No, basic pivot tables use drag-and-drop fields.
Why is my pivot table not updating?
Refresh it after source data changes.
Should I use an Excel table as source?
Yes, tables expand more cleanly when new rows are added.
Related Guides
How to Add Drop Down List in Excel
Learn how to add a drop down list in Excel using Data Validation, source lists, error alerts, common mistakes, troubleshooting, and FAQs.
How to Move Columns in Excel
Learn how to move columns in Excel by drag-and-drop, cut and insert, table tips, formula checks, common mistakes, troubleshooting, and FAQs.
How to Apply Conditional Formatting in Excel
Highlight values, duplicates, dates or formula-based conditions in Excel and manage rule order and cell references.
Help us improve this guide
Tell us about an outdated instruction, unclear step, broken link or safety concern. Do not include passwords, account numbers, medical records or other sensitive information.