Excel Create a PivotTable

Create a PivotTable

A PivotTable summarizes a large table in a few clicks.

You do not write any formulas. You pick the fields you want to see, and Excel does the math.

In this chapter, you will turn 2,740 rows of Poke Mart sales into a short summary of sales by category.

New to PivotTables? Read the PivotTable Introduction first.

The Example Data

The file has one sheet, Sales, with one row for each order.

The data is in the range A1:J2741: one header row and 2,740 orders from 2024 and 2025.

Here are the first rows:

The columns are:

Prepare the Data

A PivotTable needs data in a simple table shape:

• One header row, with a unique name at the top of each column
• No blank rows or blank columns inside the data
• One kind of data in each column, for example only dates in the Date column
• No merged cells, and no subtotal rows mixed in with the data

The Poke Mart data already follows these rules.

Tip: Format the Data as a Table

This step is optional, but it helps later.

Click any cell in the data and press Ctrl+T, then click OK. You can read more in Excel Tables.

A table grows by itself when you add new rows below it.

A PivotTable that uses a table picks up the new rows the next time you refresh it.

A PivotTable that uses a fixed range, like A1:J2741, does not. You would have to change the range yourself with PivotTable Analyze > Change Data Source.

Insert a PivotTable

Follow these steps:

1. Click any cell in the data, for example A1
2. Click the Insert tab
3. Click the PivotTable arrow, then From Table/Range

The PivotTable from table or range dialog opens.

4. Check the Table/Range box. Excel fills in the whole data range for you: Sales!$A$1:$J$2741. If you made a table, it shows the table name instead, like Table1
5. Choose New Worksheet
6. Click OK

Excel adds a new sheet with an empty PivotTable. The PivotTable Fields pane opens on the right.

The PivotTable Fields Pane

The top of the pane lists every column header from your data. In a PivotTable, the columns are called fields.

The bottom of the pane has four areas:

• Filters: filter the whole PivotTable by a field
• Columns: show each value of a field as a column
• Rows: show each value of a field as a row
• Values: the numbers to calculate, such as a sum or a count

Drag a field from the list down to an area to use it.

You can also check the box next to a field. Excel then puts text fields in Rows and number fields in Values.

To remove a field, uncheck its box, or drag it out of the area.

Your First PivotTable

Let's find the sales for each product category.

1. Drag Category to the Rows area
2. Drag Sales to the Values area

The PivotTable now looks like this:

Excel adds up the Sales for each category. It calls the result Sum of Sales.

The Grand Total is the sum of all 2,740 orders: 9,416,800.

Excel places a new PivotTable in cell A3. Rows 1 and 2 stay free for the Filters area, which you will use in the Filter chapter.

The numbers have no thousands separator yet. You will format them in the Values chapter.

Good job! 2,740 rows of data became a summary of three lines.

Add a Field to Columns

Now split each category by region.

1. Drag Region to the Columns area

Each cell shows the sales of one category in one region. For example, Medicine sold 1,594,000 in Kanto (cell D6).

The Grand Total column on the right adds up each row. The Grand Total row at the bottom adds up each column.

Try dragging Region from Columns to Rows. The numbers stay the same, but the layout changes.

Moving fields between areas to look at the data from a new angle is called pivoting.

Refresh a PivotTable

A PivotTable does not update on its own when the source data changes.

After you change the data, refresh the PivotTable:

1. Click any cell in the PivotTable
2. Click the PivotTable Analyze tab
3. Click Refresh

You can also right-click the PivotTable and choose Refresh.

If you add new rows below a fixed range, Refresh does not include them. That is why a table is the better source (see the tip above).

Recommended PivotTables

Not sure where to start? Click Insert > Recommended PivotTables, and Excel suggests ready-made PivotTables for your data that you can pick and then change.