In Excel, you can pivot data in a PivotTable or PivotChart by changing the field layout of the data. By using the PivotTable Field List, you can add, rearrange, or remove fields to show data in a PivotTable or PivotChart exactly the way that you want. Show
By default, changes you make in the PivotTable Field List are automatically updated in the report layout. To improve performance when you are accessing a large amount of external data, you can temporarily switch to manual updating. When you create a PivotTable, Excel displays the PivotTable Field List so that you can add fields to the
PivotTable, rearrange and reposition them as needed, or remove them from the PivotTable. By default, the PivotTable Field List displays two sections: A field section at the top for adding fields to and removing fields from the PivotTable A layout section at the bottom for rearranging and repositioning fields You can dock the PivotTable Field List to either side of the Excel window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally. If you don't see the PivotTable Field List, make sure that you click anywhere in the PivotTable. If you close the PivotTable Field List, you can display it again. Right-click the PivotTable, and then click Show Field List. You can also click Field List on the Ribbon (PivotTable Tools, Options tab, Show group for a PivotTable; PivotChart Tools, Analyze tab, Show/Hide group for a PivotChart). If you don't see the fields that you want to use in the PivotTable Field List, refresh the PivotTable or PivotChart to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation (PivotTable Tools, Options tab, Data group). It's important to understand how the PivotTable field List works and the ways that you can arrange different types of fields so that you can achieve the results that you want when you create the field layout of a PivotTable or PivotChart.
1. An external data source contains structured data organized as one or more fields (also called columns) that are displayed in the Field List. 2. Move a field to the Report Filter area in the Field List, which simultaneously moves the field to the Report Filter area in the PivotTable. 3. Move a field to the Column Label area in the Field List, which simultaneously moves the field to the Column Label area in the PivotTable. 4. Move a field to the Row Label area in the Field List, which simultaneously moves the field to the Row Label area in the PivotTable. 5. Move a field to the Values area in the Field List, which simultaneously moves the field to the Values area in the PivotTable. To create a field layout, use the following guidelines for moving Value, Name, and Dimension fields from the field section to the four report areas in the layout section.
The PivotTable Field List has five different views that are designed and optimized for different types of PivotTable tasks.
Tip In the Fields Section and Areas Section Stacked and Fields Section and Areas Section Side-By-Side views, you can adjust the width and height of each section by resting the pointer on the section divider until the pointer changes to a vertical double arrow or horizontal double arrow , by dragging the double arrow up or down or left or right to where you want it, and then either clicking the double arrow or pressing ENTER.After you create a PivotTable or PivotChart, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area. If the PivotTable is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report. In the PivotTable Field List, you can add fields to the PivotTable or PivotChart by doing one or more of the following:
In a PivotTable that is based on data in an Excel worksheet or external data from a non-Online Analytical Processing (OLAP) data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.
Notes
In the PivotTable Field List, you can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:
To rearrange fields in a PivotTable, click the field name in one of the areas, and then select one of the following commands:
Tip You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas. To remove a field, in the PivotTable Field List, do one of the following:
By default, changes you make in the PivotTable Field List are automatically updated in the report layout. To improve performance when you are accessing a large amount of external data, you can temporarily switch to manual updating mode. Manual updating mode allows you to quickly add, move, and remove fields in the PivotTable Field List. However, you cannot use the report until you switch back to automatic updating mode.
Note A PivotTable starts with automatic updating each time that you open the workbook. When you create a PivotTable, Excel displays the PivotTable Field List so that you can add fields to the PivotTable, rearrange and reposition them as needed, or remove
them from the PivotTable. By default, the PivotTable Field List displays two sections: A field section at the top for adding fields to and removing fields from the PivotTable A layout section at the bottom for rearranging and repositioning fields You can dock the PivotTable Field List to either side of the Excel window and horizontally resize it. You can also undock the PivotTable Field List, in which case, you can resize it both vertically and horizontally. If you don't see the PivotTable Field List, make sure that you click anywhere in the PivotTable. If you close the PivotTable Field List, you can display it again. Control-click the PivotTable, and then click Show Field List. You can also click Field List on the PivotTable Analyze tab. If you don't see the fields that you want to use in the PivotTable Field List, refresh the PivotTable to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation: PivotTable Analyze > Refresh. To create a field layout, use the following guidelines for moving Value, Name, and Dimension fields from the field section to the four report areas in the layout section.
After you create a PivotTable or PivotChart, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area. If the PivotTable is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report. In the PivotTable Field List, you can add fields to the PivotTable or PivotChart by doing one or more of the following:
In a PivotTable that is based on data in an Excel worksheet or external data from a non-Online Analytical Processing (OLAP) data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.
Notes
To remove a field, in the PivotTable Field List, do one of the following:
When you create a PivotTable, Excel displays the PivotTable Field List so that you can add fields to the PivotTable, rearrange and reposition them as needed, or remove them from the PivotTable. By default, the PivotTable Field List displays two sections: A field section at the top for adding fields to and removing fields from the PivotTable A layout section at the bottom for rearranging and repositioning fields To open the PivotTable Field list, right-click the PivotTable, and then click Show Field List. If you don't see the fields that you want to use in the PivotTable Field List, refresh the PivotTable or PivotChart to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation. To do that, right-click the PivotTable and select Refresh. To create a field layout, use the following guidelines for moving Value, Name, and Dimension fields from the field section to the four report areas in the layout section. Values If you select a check box only for a numeric field, by default, it is moved to the VALUES
area. Rows and Columns You can add a field only once to either the FILTERS, ROWS, or COLUMNS areas, whether the data type is numeric or non-numeric. If you try to add the same field more than once — for example, to the ROWS and the COLUMNS areas in the layout section — the field is automatically removed from
the original area and put in the new area. After you create a PivotTable or PivotChart, you are ready to add the fields that contain the data you want to display in the report. You typically select one field for each area in the layout section. However, to see different values for a specific field, you can also add multiple copies of a field to the Values area. If the PivotTable is connected to an external data source that contains a lot of data, you can filter one or more fields before you add fields to the report, which can help reduce the time it takes to update the report. In the PivotTable Field List, you can add fields to the PivotTable or PivotChart by doing one or more of the following:
In a PivotTable that is based on data in an Excel worksheet or external data from a non-Online Analytical Processing (OLAP) data source, you may want to add the same field more than once to the Values area. You can do this whether the data type is numeric or non-numeric. For example, you may want to compare calculations side-by-side, such as gross and net profit margins, minimum and maximum sales, or customer counts and percentage of total customers.
Notes:
In the PivotTable Fields or PivotChart Fields list, you can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:
To rearrange fields in a PivotTable, click the field name in one of the areas, and then select one of the following commands:
Tip: You can also click and hold a field name, and then drag the field between the field and layout sections, and between the different areas. To remove a field, in the Field List, do one of the following:
Need more help?You can always ask an expert in the Excel Tech Community or get support in the Answers community. See AlsoCreate a PivotTable Use the Field List to arrange fields in a PivotTable Create a PivotChart Use slicers to filter data Create a PivotTable timeline to filter dates What are column labels in the areas of a PivotTable?The fields that are put in COLUMNS area appear as columns in the PivotTable, with the Column Labels being the values of the selected fields.
When you create a PivotTable report each column of your source data becomes a that you can use in the report?When you create a Pivot Table report, each column of your source data becomes a field that you can use in the report. Fields summarize multiple rows of information from the source data. The names of the fields for the report come from the column titles in your source data.
What are the four columns in PivotTable?In it are four areas (Filters, Columns, Rows, and Values) where various field names can be placed to create a PivotTable. The task pane also includes a checklist area of the fields from which to choose from the data.
What is the area in the layout section of the PivotTable field list where data is summarized?Values area
An area in the layout section of the PivotTable Field List in which you can position fields that contain data that is summarized in a PivotTable report or PivotChart report; the data placed here is usually numeric or financial in nature and the data is summarized—summed.
|