Related topics
×
PivotTables
Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
PivotTables

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. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

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

    Note:  Your data should be organized in columns with a single header row. See the Data format tips and tricks section for more details.

  2. Select Insert > PivotTable.

    PivotTable Button

  3. This creates a PivotTable based on an existing table or range.
    PivotTable from Range or Table

    Note: Selecting Add this data to the Data Model adds the table or range being used for this PivotTable into the workbook’s Data Model. Learn more.

  4. 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 select where you want the new PivotTable to appear. 

  5. Select OK.

By clicking the down arrow on the button, you can select from other possible sources for your PivotTable.  In addition to using an existing table or range, there are three other sources you can select from to populate your PivotTable.

Select PivotTable Source

Note: Depending on your organization's IT settings you might see your organization's name included in the list. For example, "From Power BI (Microsoft)."

Get from External Data Source

PivotTable from External Source

Get from Data Model

Use this option if your workbook contains a Data Model, and you want to create a PivotTable from multiple tables, enhance the PivotTable with custom measures, or are working with very large datasets. 
PivotTable from Data Table

Get from Power BI

Use this option if your organization uses Power BI and you want to discover and connect to endorsed cloud datasets you have access to. 

PivotTable from Power BI

  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.

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, and then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, then on the ribbon go to PivotTable Analyze > select the arrow under the Refresh button, and then select Refresh All.

Refresh a PivotTable

Summarize Values By

By default, PivotTable fields placed in the Values area are displayed as a SUM. If Excel interprets your data as text, the data is 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 selecting the arrow to the right of the field name, and 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 automatically appends it in the Custom Name section, like "Sum of FieldName", but you can change it. If you select Number Format, you can change the number format for the entire field.

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

Excel Value Field Settings dialog for Summarize Values By options

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 PivotTable example with Values displayed as a percentage of the Grand Total

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

Display a value as both a calculation and percentage.

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

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

  1. Select a table or range of data in your sheet and select Insert > PivotTable to open the Insert PivotTable pane.

  2. You can either manually create your own PivotTable or choose a recommended PivotTable to be created for you. Do one of the following:

  • On the Create your own PivotTable card, select either New sheet or Existing sheet to choose the destination of the PivotTable.

  • On a recommended PivotTable, select either New sheet or Existing sheet to choose the destination of the PivotTable.

Note: Recommended PivotTables are available only to Microsoft 365 subscribers.

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

You can change the data sourcefor the PivotTable data as you are creating it.

  1. In the Insert PivotTable pane, select the text box under Source. While changing the Source, cards in the pane won't be available.

  2. Make a selection of data on the grid or enter a range in the text box. 

  3. Press Enter on your keyboard or the button to confirm your selection. The pane updates with new recommended PivotTables based on the new source of data.

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

Get from Power BI

Use this option if your organization uses Power BI and you want to discover and connect to endorsed cloud datasets you have access to. 

PivotTable from Power BI

In the PivotTable Fields pane, 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, drag it out from the list or uncheck it.

PivotTable Fields in Excel for the web 

Summarize Values By

By default, PivotTable fields in the Values area are displayed as a SUM. If Excel interprets your data as text, it is displayed as a COUNT. This is why it's so important to make sure you don't mix data types for value fields.

Change the default calculation by right-clicking any value in the row and selecting the Summarize Values By option.

Summarize By in Excel for the web

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

Right-click any value in the column you'd like to show the value for. Select Show Values As in the menu. A list of available values displays.

Make your selection from the list.

To show as a % of Parent Total, hover over that item in the list and select the parent field you want to use as the basis of the calculation. 

Show As 

If you add new data to your PivotTable data source, any PivotTables built on that data source must be refreshed. Right-click anywhere in the PivotTable range, and then select Refresh.

Refresh PivotTable 

If you created a PivotTable and decide you no longer want it, select the entire PivotTable range and press Delete. This won't have any effect 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 the sheet is a fast way to remove the PivotTable.

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

  • Tables are a great PivotTable data source, because rows added to a table are included automatically in the PivotTable when you refresh the data, and any new columns are 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.

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 desired results. You can also download our interactive Make your first PivotTable tutorial.

  1. Select 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, as 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 creates a PivotTable on a new sheet and displays the PivotTable Fields list.

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

  2. Go to Insert > PivotTable.

    Data tab, Analysis group

  3. Excel displays the Create PivotTable dialog box 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. Select OK, and Excel creates a blank PivotTable and displays the PivotTable Fields list.

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 changing its appearance so easy.

PivotTable Fields list

Example of the Excel PivotTable Fields list dialog

  • Summarize by

    By default, PivotTable fields placed in the Values area are displayed as a SUM. If Excel interprets your data as text, the data is 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 selecting the arrow to the right of the field name, and then by selecting the Field Settings option.

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

    Tip: Since changing the calculation in the Summarize by section changes the PivotTable field name, it's best not to rename your PivotTable fields until you're finished setting up your PivotTable. One trick is to select Replace (on the Edit menu) >Find what > "Sum of", and 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 box, 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, and 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 must be refreshed. To refresh just one PivotTable you can right-click anywhere in the PivotTable range, and then select Refresh. If you have multiple PivotTables, first select any cell in any PivotTable, and then on the ribbon go to PivotTable Analyze > select the arrow under the Refresh button, and then select Refresh All.

If you created a PivotTable and decide you no longer want it, you can simply select the entire PivotTable range, and then press Delete. This doesn't affect any 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. 

PivotTable on iPad is available to customers running Excel on iPad version 2.82.205.0 and above. To access this feature, please ensure your app is updated to the latest version through the App Store.

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

  2. Go to Insert  > PivotTable

    Insert a PivotTable on iPad.

  3. Choose where you want the PivotTable to be placed. Select Insert on new sheet to place the PivotTable in a new worksheet or  select the cell where you want the new PivotTable placed in the Destination field. 

  4. Select Insert

Typically, 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 changing its appearance so easy. 

Note: If the field list is no longer visible, go to the PivotTable tab, swipe left, and select Field List to display the field list. 

PivotTable on iPad Field list

If you add new data to your PivotTable data source, any PivotTables that were built on that data source must be refreshed. To refresh just one PivotTable you can select and hold on a cell anywhere in the PivotTable range, and then select Refresh. If you have multiple go to PivotTable tab on the ribbon and select Refresh

Screenshot of refreshing pivot table on Excel for iPad

If you created a PivotTable and decide you no longer want it, you can select the rows and columns spanning the entire PivotTable range, and then press Delete.   

Data format tips and tricks

  • Use clean, tabular data for best results.

  • Organize your data in columns, not rows.

  • Make sure all columns have headers, with a single row of unique, non-blank labels for each column. Avoid double rows of headers or merged cells.

    Excel column 2

  • Format your data as an Excel table (select anywhere in your data, and then select Insert > Table from the ribbon).

  • If you have complicated or nested data, use Power Query to transform it (for example, to unpivot your data) so it's organized in columns with a single header row.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

PivotTable Recommendations are a part of the connected experience in Microsoft 365, and analyzes your data with artificial intelligence services. If you choose to opt out of the connected experience in Microsoft 365, your data will not be sent to the artificial intelligence service, and you will not be able to use PivotTable Recommendations. Read the Microsoft privacy statement for more details.

Related articles

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?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×