When you have a lot of data, it can be hard to analyze everything in your worksheet. A PivotTable helps by summarizing your data. It also lets you organize and view the data in different ways.

Download our Practice Book.

Using PivotTables to Answer Questions

Imagine you have sales data for different salespeople. If you want to find out how much each salesperson sold, adding up their sales manually would take a long time. You could use the Subtotal command, but the data would still be difficult to read.


A PivotTable can calculate and summarize the data instantly. It presents the information in a clear way. For example, after creating a PivotTable, you might see a table that shows total sales for each salesperson.


You can also adjust the PivotTable to answer other questions. For example, you could check total sales for each month instead of by salesperson.


How to Create a PivotTable

  1. Select the table or cells you want to include. Make sure to include the column headers.

  2. Go to the Insert tab and click PivotTable.

  3. The Create PivotTable dialog box will appear. Choose your settings and click OK.

  4. A blank PivotTable and a Field List will appear on a new worksheet.

Adding Fields to a PivotTable

A field is a column from your data. To add fields to your PivotTable:

  1. In the PivotTable Field List, check the boxes for the fields you want.
  2. The selected fields will be added to different areas:

    • Rows area (for categories like Salesperson or Region)
    • Values area (for numerical data like Order Amount)
  3. The PivotTable will now show a summary of your data.

Sorting and Formatting PivotTables

  • Use the Sort & Filter command on the Home tab to organize the data.
  • You can apply number formatting, like Currency, to make values easier to read.
  • If you change the data in your original worksheet, the PivotTable will not update automatically. Click Refresh in the Analyze tab to update it.

Pivoting Data

One of the best features of a PivotTable is that you can reorganize it easily. This lets you explore different patterns in your data.

Adding Columns

So far, the PivotTable shows only one column of data. You can add more columns by:

  1. Dragging a field into the Columns area in the Field List.

  2. The PivotTable will now include multiple columns.

For example, you could add a Month column to see each salesperson’s sales per month.

Changing Rows and Columns

To change how your data is displayed:

  1. Remove a field by dragging it out of its area or unchecking it in the Field List.

  2. Drag a new field into the Rows or Columns area.

For example, you could replace the Salesperson field with the Region field. The PivotTable will now show total sales by region instead.