Excel PivotTables

What a PivotTable Does

A regular table shows every row of raw data - one line per sale, per order, or per employee. A PivotTable rearranges and totals that same data on the fly: instead of scrolling through a thousand sales rows, you can see total revenue by Region and Product in a compact grid. The source data should be a clean table with a single header row, no blank rows or columns inside it, and one consistent type of value in each column.

A Sample Data Table

Creating a PivotTable

1. Click any cell inside the data table.
2. Insert tab > PivotTable.
3. Confirm the data range and choose whether to place the PivotTable in a New Worksheet or an Existing Worksheet.
4. Click OK - an empty PivotTable and the PivotTable Fields pane appear.

The Four Areas: Rows, Columns, Values, Filters

The PivotTable Fields pane lists every column from your source table as a field you can drag into one of four boxes. Using the sample table above, dragging Region into Rows and Product into Columns lays out a grid; dragging Amount into Values fills that grid with totals; and dragging Salesperson into Filters adds a dropdown above the table that narrows the whole report down to one salesperson at a time.

PivotTable Fields:
Rows:    Region
Columns: Product
Values:  Sum of Amount
Filters: Salesperson

Summarizing and Grouping Values

By default, a numeric field dropped into Values is summed, but clicking 'Value Field Settings' on that field lets you switch to Count, Average, Max, Min, and more - useful when you want 'number of orders' instead of 'total revenue'. If a field holds dates, right-clicking any date in the Rows or Columns area and choosing Group lets you roll individual dates up into Months, Quarters, or Years automatically.