Excel PivotTables for Beginners: A Practical Guide

PivotTables are probably Excel’s most powerful data analysis tool, and also one of the most intimidating for beginners. Yet creating a PivotTable is easier than it looks: in a few clicks you can turn thousands of rows of sales into a summary by month, by salesperson or by product. This guide teaches you the basics step by step.

What a PivotTable is

A PivotTable is an interactive summary of a data table. It lets you group, add up, count and average information by dragging fields, without writing formulas. For example, from a list of 10,000 sales you can get in seconds:

• Total sales by month.
• Sales by salesperson and region.
• The number of orders per customer.
• The best-selling product in each quarter.

And if you change your mind, you just move the fields to see a different summary.

Step 1: prepare the source data

A PivotTable is only as good as the data behind it. Before creating one, make sure that:

• There is a single header row and every column has a unique name.
• There are no blank rows or columns inside the data.
• There are no merged cells.
• There are no subtotals or totals inside the table.
• Each column holds one data type: dates are dates and amounts are numbers.
• Values are normalized: “Boston” and “boston” would be counted as different cities.

If your data comes from several files, merge them first with the Merge Excel tool. If there are repeated records, remove them with our duplicate remover. Both are on our Excel tools page.

Tip: turn the range into a table with Ctrl+T. That way, when you add new rows, the PivotTable will include them when refreshed.

Step 2: create the PivotTable

1. Click any cell in your data.
2. Go to Insert > PivotTable.
3. Check the range is correct and choose to place it on a new worksheet.
4. Click OK.

An empty table appears on the left and the PivotTable Fields pane on the right.

Step 3: understand the four areas

The pane has four zones where you can drag fields:

Area	What it does	Example
Rows	Groups data into rows	Salesperson
Columns	Groups data into columns	Month
Values	What gets calculated	Sum of amount
Filters	Filters the whole report	Region

Step 4: your first summary

Suppose you have columns for Date, Salesperson, Region, Product and Amount.

1. Drag Salesperson to Rows.
2. Drag Amount to Values.

You now have total sales by salesperson. Next:

1. Drag Date to Columns. Excel will automatically group by years, quarters or months.
2. Drag Region to Filters to see a specific region.

Step 5: change the calculation

By default Excel adds up numbers and counts text. To change it, click the field in Values > Value Field Settings and choose:

• Sum: total sales.
• Count: number of orders.
• Average: average order value.
• Max and Min: highest and lowest sale.

In the Show Values As tab you can display percentages of the total, differences from the previous month and much more.

Step 6: refresh the data

A PivotTable does not update by itself when the source data changes. Right-click it and choose Refresh, or use Data > Refresh All. It is one of the most common oversights and the reason behind many reports with outdated figures.

Common mistakes

1. Sums showing as “Count of”: the amount column contains text or blank cells. Fix the data and refresh.
2. Dates that will not group: some date is stored as text.
3. Duplicate items: the same customer appears twice because of spaces or different capitalization.
4. New data not showing: the source range does not include the added rows. Use a table (Ctrl+T) as the source.
5. Inflated totals: there were subtotal rows in the source data.

Share the result

If you want to send the summary to someone who does not need the detail, copy the PivotTable, paste it as values into a new workbook and convert it with our Excel to PDF tool. That way you share only the summary, not the source data.

Frequently asked questions

Conclusion

PivotTables let you go from thousands of rows to a clear summary in seconds. The key is to prepare the source data well, understand the four areas and remember to refresh when the data changes. Start with a simple summary and keep adding fields: it will soon be your favorite tool. And to prepare your data, our free tools help you merge and clean it.