Help content & Excel PivotTable training Create a PivotTable to analyze worksheet data

Create a PivotTable to analyze worksheet data Articles Use Field Lists to sort fields in a PivotTable Article Group or ungroup data in a PivotTable Article Filter data in a PivotTable Article Create a PivotChart Article

Next: Share & co-author

Create PivotTable to analyze sheet data

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2019 Excel 2016 Excel 2019 for Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 More… Less

PivotTable is a powerful tool for calculating, summarizing and analyzing data that allows you to see comparisons, patterns and trends in your data.

Pivottable works a little differently depending on the platform you are using to run Excel.

Windows macOS Web

Create PivotTable

Select the cells you want to use to create the PivotTable.

Note: Your data cannot have any blank rows or columns. Data can only have one row heading.

Choose Insert > PivotTable.

*

Under Select the data you want to analyze, select Select a table or range of cells.

*

In Table/Range, verify the range.

Below select where you want to place the pivottable report, select new sheet to place the pivottable in the new or existing worksheet, and then select where you want the pivottable to appear.

Select OK.

Build Your PivotTable

To add fields to your PivotTable, select the field names check box in the PivotTable Fields pane.

Note: Selected fields are added to the default area: non-numeric fields are added to Rows, date and time hierarchies are added to Columns, and numeric fields are added to Values.< /p>

*

To move the field from one area to another, drag the field to the destination area.

Before you start:

You should arrange your data in a tabular format and not contain any blank rows or columns. Ideally, you can use an Excel Table like in the example above.

Tables are a great source of PivotTable data because rows added to the table are automatically included in the PivotTable when you refresh the data, and any new columns are included in the PivotTable Field List. Otherwise, you need to change the source data for the PivotTable, or use a dynamically named range formula.

The data type in the column must be the same. For example, you should not combine dates and text in the same column.

PivotTable works instantaneously with your data, it’s called caching so your actual data isn’t changed in any way.

Create PivotTable

If you don’t have much experience with PivotTables or aren’t sure how to get started, we recommend using the recommended PivotTable. When you use this feature, Excel determines a meaningful layout by matching the data to the most relevant areas in the PivotTable. This feature will give you a starting point for further testing. After creating the suggested PivotTable, you can explore different directions and rearrange the fields to achieve your specific results.

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

Recommended PivotTable

Create PivotTable Manually

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

READ MORE  Office Vinhomes Hai Phong

Go to Insert > Recommended PivotTable.

*

Excel will analyze your data and show you several options, such as in this example using household cost data.

*

Select the PivotTable that seems to work best for you and click OK. Excel will create the PivotTable on a new sheet and display the PivotTable Field List.

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

Go to Insert > PivotTable.

If you’re using Excel for Mac 2011 or earlier, the PivotTable button will be on the Data tab in the Analysis group.

*

Excel displays the Create PivotTable dialog box with the range or table name selected. In In this case we are using a table named “tbl_HouseholdExpenses”.

In the Choose where you want to place the PivotTable report, select New Sheet or Existing Sheet. For an existing Sheet, select the cell where you want the PivotTable to be placed.

Click OK and Excel will create a blank PivotTable and display a list of PivotTable Fields.

In the Field Name area at the top, select the check box that corresponds to any field that you want to add to the 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 existing item into any PivotTable field, or if you don’t want an item in the PivotTable anymore, just drag the item out of the Fields list or deselect it. The ability to rearrange Field items is one of the PivotTable features that makes it easy to change the look of a PivotTable on the fly.

PivotTable Field List

Correct fields in PivotTable

*

*

Summary by

By default, PivotTable fields placed in the Values ​​area are displayed as SUM. If Excel interprets your data as text, it will be displayed as a QUANTITY. This is why you need to make sure not to mix data types for value fields. You can change the default calculation by clicking the arrow to the right of the field name and then selecting the Field settings option.

Next, change the calculation in the Summary by section. Note that when you change the calculation method, Excel automatically appends that method to the Custom Name section, like “Sum of field_names”, but you can change that. If you click the Number… button, you can change the number format for the entire field.

Tip: Since changing the calculation in the Summary By section changes the PivotTable field name, it’s best not to rename the PivotTable fields until you’ve finished setting up your PivotTable.

Viewing: What is a Pivot Table

See also: What is a contract?

See also: What is Dividend – How to Divide Cash Dividend

One trick is to click Replace (on the Edit menu) >Find what > ​​”Sum of”, then Replace with > leave it blank to replace all at once instead of manually re-entering it.

Display data as

Instead of using a calculation to summarize the data, you can also display the data as a percentage of the field. In the example below, we’ve changed the number of family expenses to show as % Total instead of total values.

*

Once you have opened the Field Settings dialog box, you can make your selection from the Show Data As tab.

READ MORE  What Is White Pollution? What is the Harm of Plastic Waste?

Display a value both as a calculation and as a percentage.

Just drag the item to the Values ​​section twice, right-click the value and select Field Settings, and then set the Summary by and Show data as options for each item.

If you add new data to a PivotTable data source, any PivotTables built on top of that data source will need to be refreshed. To refresh only 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 Analysis > click the arrow below the Refresh button, and then select Refresh All.

If you’ve created a PivotTable and decide you don’t want to use it anymore, simply select the entire PivotTable range and click Delete. This won’t have any effect on the other data or the surrounding PivotTable or chart. If your PivotTable is on a separate sheet that contains no other data that you want to keep, deleting that sheet is the fastest way to get rid of the PivotTable.

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

Insert pivottable pane Shows the data source and destination from which the pivottable will be inserted and provides some suggested pivottables.

*

Do one of the following:

To create a PivotTable manually, choose to create your own PivotTable.

To use the suggested PivotTable, scroll until you see the option you want, and then select + Insert. The PivotTable appears in the destination and prevents the PivotTable field from appearing.

Change source or target cell data

If you want, you can change the source for the pivottable data and the target cell where you want to place the pivottable. The destination cell will be outside the source table or range.

In the Insert PivotTable pane, select the link next to the source or the link next to the destination. PivotTable Changes Pane:

*

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 the new or existing worksheet below select a destination for the pivottable, and then follow the steps that appear.

Note: You can also select a blank worksheet where you want the PivotTable to appear (the destination). In this case, or if you select an invalid source, when you choose 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 that corresponds to any field that you want to add to the PivotTable. By default, non-numeric fields will be added to the Rows area, date and time fields will be added to the Columns area, and numeric fields will be added to the Values ​​area. You can also manually drag and drop any existing item into any PivotTable field, or if you don’t want an item in the PivotTable anymore, just drag the item out of the Fields list or deselect it. The ability to rearrange Field items is one of the PivotTable features that makes it easy to change the look of a PivotTable on the fly.

READ MORE  What is Flintkote Waterproofing

PivotTable Field List

Correct fields in PivotTable

*

*

According Value Summary

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

*

Next, change the calculation in Summary By Value. Note that when you change the calculation method, Excel automatically assigns it to a Custom Name section, like “Total Field Names,” but you can change that. If you click the Number Format button, you can change the number format for the entire field.

*

Tip: Since changing the calculation in the Summary Value By section changes the PivotTable field name, it’s best not to rename the PivotTable fields until you’ve finished setting up your PivotTable. One trick is to use Find & Replace (Ctrl+H) >Find > “Sum of”, and then Replace with > leave blank to replace all at once instead of re-entering it manually.

Show Value As

Instead of using a calculation to summarize the data, you can also display the data as a percentage of the field. In the example below, we’ve changed the number of family expenses to show as % Total instead of total values.

*

Once you have opened the Value Field Settings dialog box, you can make your selection from the Show Values ​​As tab.

*

Displays a value both as a calculation and as a percentage.

Just drag the item to the Values ​​section twice, and then set the Summary Value By and Show Value As options for each item.

If you’ve created a PivotTable and decide you don’t want to use it anymore, simply select the entire PivotTable range and click Delete. This won’t have any effect on the other data or the surrounding PivotTable or chart. If your PivotTable is on a separate sheet that contains no other data that you want to keep, deleting that sheet is the fastest way to get rid of the PivotTable.

Need more help?

You can always ask an expert in the Excel Technical Community, find support in the Solutions Community, or suggest a new feature or improvement in Excel User Voice.

Read more

Create PivotChart

Use slicers to filter PivotTable data

Create a PivotTable Timeline to filter the date

Create a PivotTable using Data Models to analyze data in multiple tables

Create a PivotTable connected to a Power BI datasheet

Use the Field List to sort the fields in a PivotTable

Change source data for PivotTable

Calculate values ​​in a PivotTable

Delete PivotTable