PivotTables

Create a PivotTable to analyze worksheet data

Create a PivotTable to analyze worksheet data

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

PivotTables work a little bit differently depending on what platform you are using to run Excel.

Your browser does not support video.

Create a PivotTable

  1. Select the cells you want to create a PivotTable from.

    Note:  Your data shouldn't have any empty rows or columns. It must have only a single-row heading.

  2. Select Insert > PivotTable.

           Pivot Table

  3. Under Choose the data that you want to analyze, select Select a table or range

           Pivot Table

  4. In Table/Range, verify the cell range.

  5. Under Choose where you want the PivotTable report to be placed, select New worksheet to place the PivotTable in a new worksheet or Existing worksheet and then select the location you want the PivotTable to appear.

  6. Select OK.

Building out your PivotTable

  1. To add a field to your PivotTable, select the field name checkbox in the PivotTables Fields pane.

    Note: Selected fields are added to their default areas: non-numeric fields are added to Rows, date and              time hierarchies are added to Columns, and numeric fields are added to Values.

    Pivot Table

  2. To move a field from one area to another, drag the field to the target area.

Before you get started:

  • Your data should be organized in a tabular format, and not have any blank rows or columns. Ideally, you can use an Excel table like in our example above.

  • Tables are a great PivotTable data source, because rows added to a table are automatically included in the PivotTable when you refresh the data, and any new columns will be included in the PivotTable Fields List. Otherwise, you need to either Change the source data for a PivotTable, or use a dynamic named range formula.

  • Data types in columns should be the same. For example, you shouldn't mix dates and text in the same column.

  • PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way.

Create a PivotTable

If you have limited experience with PivotTables, or are not sure how to get started, a Recommended PivotTable is a good choice. When you use this feature, Excel determines a meaningful layout by matching the data with the most suitable areas in the PivotTable. This helps give you a starting point for additional experimentation. After a recommended PivotTable is created, you can explore different orientations and rearrange fields to achieve your specific results.

You can also download our interactive Make your first PivotTable tutorial.

Recommended PivotTable

Manually create a PivotTable

  1. Click a cell in the source data or table range.

  2. Go to Insert > Recommended PivotTable.

    Go to Insert > Recommended PivotTables to have Excel create a PivotTable for you

  3. Excel analyzes your data and presents you with several options, like in this example using the household expense data.

    Excel Recommended PivotTables dialog
  4. Select the PivotTable that looks best to you and press OK. Excel will create a PivotTable on a new sheet, and display the PivotTable Fields List.

  1. Click a cell in the source data or table range.

  2. Go to Insert > PivotTable.

    If you're using Excel for Mac 2011 and earlier, the PivotTable button is on the Data tab in the Analysis group.

    Data tab, Analysis group

  3. Excel will display the Create PivotTable dialog with your range or table name selected. In this case, we're using a table called "tbl_HouseholdExpenses".

  4. In the Choose where you want the PivotTable report to be placed section, select New Worksheet, or Existing Worksheet. For Existing Worksheet, select the cell where you want the PivotTable placed.

  5. Click OK, and Excel will create a blank PivotTable, and display the PivotTable Fields list.

In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.

PivotTable Fields list

Corresponding fields in a PivotTable

Example of the Excel PivotTable Fields list dialog



Example of a PivotTable and how the Fields correlate to the Fields list.

  • Summarize by

    By default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Field Settings option.

    Next, change the calculation in the Summarize by section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like "Sum of FieldName", but you can change it. If you click the Number... button, you can change the number format for the entire field.

    Tip: Since the changing the calculation in the Summarize by section will change the PivotTable field name, it's best not to rename your PivotTable fields until you're done setting up your PivotTable. One trick is to click Replace (on the Edit menu) >Find what > "Sum of", then Replace with > leave blank to replace everything at once instead of manually retyping.

  • Show data as

    Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.

    PivotTable example with Values displayed as a percentage of the Grand Total

    Once you've opened the Field Settings dialog, you can make your selections from the Show data as tab.

  • Display a value as both a calculation and percentage.

    Simply drag the item into the Values section twice, right-click the value and select Field Settings, then set the Summarize by and Show data as options for each one.

If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh just one PivotTable you can right-click anywhere in the PivotTable range, then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the Ribbon go to PivotTable Analyze > click the arrow under the Refresh button and select Refresh All.

If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won't have any affect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable.

Your browser does not support video.

Note: We're constantly working to improve PivotTables in Excel for the web. What you see in the video above may differ from what you actually see as we refine the experience.

  1. Select a table or range in your spreadsheet, and then select Insert > PivotTable.


    The Insert PivotTable pane displays the data Source and the Destination where the PivotTable will be inserted, and offers some recommended PivotTables.

    The Insert PivotTable pane lets you set the source, destination, and other aspects of the PivotTable.

  2. Do one of the following:

  • To create the PivotTable manually, select Create your own PivotTable.

  • To use a Recommend PivotTable, scroll until you see the option you want, and then select + Insert.

    The PivotTable appears at the Destination, and the PivotTable Fields pane appears.

Change the Source data or Destination cell

If you want, you can change the Source for the PivotTable data and the Destination cell where you want the PivotTable placed. The Destination cell should be outside the Source table or range.

  1. In the Insert PivotTable pane, select the link next to Source or the link next to Destination. The Insert PivotTable pane changes:

    Insert PivotTable pane asking for a table or range to use as a Source and allowing you to change the Destination.

  2. Do one of the following:

  • To change the Source, select Select a table or range to analyze: and follow the steps that appear.

  • To change the Destination, select New Worksheet or Existing Worksheet under Choose a destination for the PivotTable, and then follow the steps that appear.

Note: You can also select an empty sheet where you want the PivotTable to appear (the Destination). In this case, or if you select an invalid Source, when you select Insert > PivotTable, the Insert PivotTable pane appears and asks you to specify the Source

In the PivotTable Fields area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance.

PivotTable Fields list

Corresponding fields in a PivotTable

Use the PivotTable Fields pane to select which fields go where in the PivotTable.


A PivotTable with its parts labeled (columns, rows, values).

  • Summarize Values By

    By default, PivotTable fields that are placed in the Values area will be displayed as a SUM. If Excel interprets your data as text, it will be displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields. You can change the default calculation by first clicking on the arrow to the right of the field name, then select the Value Field Settings option.

    Excel Value Field Settings dialog

    Next, change the calculation in the Summarize Values By section. Note that when you change the calculation method, Excel will automatically append it in the Custom Name section, like "Sum of FieldName", but you can change it. If you click the Number Format button, you can change the number format for the entire field.

    Excel Value Field Settings dialog for Summarize Values By options

    Tip: Since the changing the calculation in the Summarize Values By section will change the PivotTable field name, it's best not to rename your PivotTable fields until you're done setting up your PivotTable. One trick is to use Find & Replace (Ctrl+H) >Find what > "Sum of", then Replace with > leave blank to replace everything at once instead of manually retyping.

  • Show Values As

    Instead of using a calculation to summarize the data, you can also display it as a percentage of a field. In the following example, we changed our household expense amounts to display as a % of Grand Total instead of the sum of the values.

    PivotTable example with Values displayed as a percentage of the Grand Total

    Once you've opened the Value Field Setting dialog, you can make your selections from the Show Values As tab.

    PivotTable Value Field Settings > Show Values As dialog
  • Display a value as both a calculation and percentage.

    Simply drag the item into the Values section twice, then set the Summarize Values By and Show Values As options for each one.

If you add new data to your PivotTable data source, any PivotTables that were built on that data source need to be refreshed. To refresh the PivotTable, you can right-click anywhere in the PivotTable range, then select Refresh.

Refresh PivotTable

If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, then press Delete. It won't have any affect on other data or PivotTables or charts around it. If your PivotTable is on a separate sheet that has no other data you want to keep, deleting that sheet is a fast way to remove the PivotTable.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Create a PivotChart

Use slicers to filter PivotTable data 

Create a PivotTable timeline to filter dates

Create a PivotTable with the Data Model to analyze data in multiple tables

Create a PivotTable connected to Power BI Datasets

Use the Field List to arrange fields in a PivotTable

Change the source data for a PivotTable

Calculate values in a PivotTable

Delete a PivotTable

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×