Create a PivotTable in Excel: step-by-step

Start with clean tabular data that has a header row, then use Insert > PivotTable to build the summary on a new or existing sheet. Drag fields into Rows, Columns, Values and Filters to shape the output, and apply filters, sorts and formatting to answer your question.

Prepare the data before you start

PivotTables work best when the source is a simple table: one header row, consistent data types in each column, and no total rows or blank rows inside the range. If your data is messy, clean it first so the PivotTable does not misinterpret values.

Check headers: each column needs a unique, descriptive header.
Remove blank rows or subtotals within the data.
Ensure each column has consistent types: dates in one column, numbers in another, text in another.
Convert the range to an Excel Table (Insert > Table) so the PivotTable updates as you add rows.

For a practical guide on preparing data, see Prepare Excel data for PivotTables.

Step-by-step: create the PivotTable

Select a cell inside your data range or click on the Excel Table you created.
Go to the Insert tab and choose PivotTable. Excel will propose the table or range and let you place the PivotTable on a new worksheet or an existing one.
Choose whether to analyze the data model or add the PivotTable to a new sheet; click OK to create the blank PivotTable layout and the PivotTable Fields pane.
In the PivotTable Fields pane, drag field names into four areas: Rows, Columns, Values and Filters.
Change the summary type in Values by clicking the field and choosing Value Field Settings (Sum, Count, Average, etc.).
Use the filter dropdowns on Row or Column labels to sort or filter items, or add a field to the Filters area for page-level filtering.

That basic flow gives you a working summary; the next sections explain how to use each area effectively.

Pivot fields and values: how to think about them

Understanding the four layout areas helps you design the table to answer a question.

Rows: items listed down the left; good for categorical breakdowns like Product or Region.
Columns: items across the top; useful for side-by-side comparisons such as Years or Quarters.
Values: numeric measures you want to aggregate, such as Sales, Quantity or Hours.
Filters: let you slice the whole view by a field without changing the layout.

To change how values are calculated, open Value Field Settings and pick Sum, Count, Average, Max, Min or a custom calculation. You can also add the same field multiple times to show different summaries, for example Sum and Average side by side.

Sorting and grouping

Right-click a Row or Column label to sort ascending or descending. For date fields you can group items (select one date in the PivotTable, right-click and choose Group) to roll daily data into months, quarters or years.

Filters, slicers and how users interact

PivotTable Filters give you a simple dropdown for one or more fields. Slicers provide clickable, persistent buttons that are easier for non-technical users to operate. Timelines are a special slicer for date fields that show a date range slider.

To add a slicer: click inside the PivotTable, go to the Insert tab and choose Slicer, then select the field. For more details about using slicers with PivotTables, see Use slicers with PivotTables.

Create charts from PivotTables

PivotCharts connect directly to a PivotTable so chart elements update when you change the Pivot layout or filters. To create one, select the PivotTable and choose Insert > PivotChart, then pick a chart type. Use the chart filters and format the series like any other Excel chart.

For step-by-step guidance on visuals, see Create charts from PivotTables.

Worked example: summarize monthly sales by region and product

Imagine a sheet with columns: Date, Region, Product, Sales. You want total Sales by Region with Product broken out in columns and a Month filter.

Confirm headers exist and convert the range to a Table.
Insert > PivotTable and place it on a new sheet.
Drag Region to Rows; drag Product to Columns; drag Sales to Values (set to Sum).
Drag Date to Filters, then click its dropdown and use Group to group by Months if you want month-level filtering inside the Filter area.
Optional: add a Slicer for Product for quick selection.

Result: a compact grid showing regional totals in the first column, product columns across the top, and sums at intersections. Changing the Date filter updates the numbers immediately.

Common mistakes and troubleshooting

Most problems come from data issues or confusion about how PivotTables read source ranges. Before spending time reformatting the PivotTable, verify the source data.

Missing or merged headers: PivotTables need single-row headers; unmerge cells and provide names.
Blank or inconsistent data types in a column: convert text numbers to numeric values, and ensure dates are real Excel dates.
Accidentally including totals inside the source range: remove any subtotal rows from the base data.
PivotTable not updating after source changes: either refresh the PivotTable or, if you added rows outside an ordinary range, convert the source to a Table and refresh.

If you hit specific errors or odd results, see Troubleshoot PivotTable errors for targeted fixes.

Quick checklist before sharing

Headers are clear and unique.
Data types are consistent in each column.
Source is an Excel Table or the range has been correctly selected.
Values use the intended aggregation (Sum, Count, Average).
Slicers or filters are added for interactive needs and tested.

Closing: use PivotTables to test questions, not just build reports

PivotTables are flexible tools for rapid exploration as well as repeatable reporting. Start with a clear question, prepare the data, then build a simple Pivot and iterate: add fields, change aggregations, and apply slicers until the table answers your question. When you need visual summaries, create a PivotChart from the same PivotTable so the chart stays in sync with your analysis.