To make substantial layout changes to a PivotTable or its various fields, you can use one of three forms:
To get the final layout results that you want, you can add, rearrange, and remove fields by using the PivotTable Field List. If you don't see the PivotTable Field List, make sure that the PivotTable is selected. If you still don't see the PivotTable Field List, on the Options tab, in the Show/Hide group, click Field List. If you don't see the fields that you want to use in the PivotTable Field List, you may need to refresh the PivotTable to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation. On the Options tab, in the Data group, click Refresh. For more information about working with the PivotTable Field List, see Use the Field List to arrange fields in a PivotTable.
Do one or more of the following:
In a PivotTable that is based on data in an Excel worksheet or external data from a non-OLAP source data, you may want to add the same field more than once to the Values area so that you can display different calculations by using the Show Values As feature. 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. For more information, see Show different calculations in PivotTable value fields.
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, click the field name in one of the areas, and then select one of the following commands:
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 further refine the layout of a PivotTable, you can make changes that affect the layout of columns, rows, and subtotals, such as displaying subtotals above rows or turning column headers off. You can also rearrange individual items within a row or column. Turn column and row field headers on or off
Display subtotals above or below their rows
Change the order of row or column items Do any of the following:
Adjust column widths on refresh
Move a column to the row labels area or a row to the column labels area You might want to move a column field to the row labels area or a row field to the column labels area to optimize the layout and readability of the PivotTable. When you move a column to a row or a row to a column, you are transposing the vertical or horizontal orientation of the field. This operation is also called "pivoting" a row or column. Use a right click command Do any of the following:
Use drag and drop
Merge or unmerge cells for outer row and column items You can merge cells for row and column items in order to center the items horizontally and vertically, or to unmerge cells in order to left-justify items in the outer row and column fields at the top of the item group.
Note: You cannot use the Merge Cells check box under the Alignment tab in a PivotTable.
There may be times when your PivotTable data contains blank cells, blank lines, or errors, and you want to change the way they are displayed. Change how errors and empty cells are displayed
Display or hide blank lines after rows or items For rows, do the following:
For items, do the following:
Change how items and labels with no data are shown
You can choose from a wide variety of PivotTable styles in the gallery. In addition, you can control the banding behavior of a report. Changing the number format of a field is a quick way to apply a consistent format throughout a report. You can also add or remove banding (alternating a darker and lighter background) of rows and columns. Banding can make it easier to read and scan data. Apply a style to format a PivotTable You can quickly change the look and format of a PivotTable by using one of numerous predefined PivotTable styles (or quick styles).
Apply banding to change the format of a PivotTable
Remove a style or banding format from a PivotTable
Conditionally format data in a PivotTable Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. Conditional formatting helps you answer specific questions about your data. There are important differences to understand when you use conditional formatting on a PivotTable:
For more information, see Apply conditional formatting. Change the number format for a field
Include OLAP server formatting If you are connected to a Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP) database, you can specify what OLAP server formats to retrieve and display with the data.
Preserve or discard formatting
Use the PivotTable Settings pane to make changes to your PivotTable's layout and formatting.
Note: The Slicer section only appears if the there is a slicer connected to your PivotTable.
To show grand totals
To show subtotals
To place fields from Rows area Select Separate columns to provide individual filters for each Rows field, or Single column to combine the Rows fields in one filter.
To show or hide item labels Select Repeat or Don't repeat to choose whether item labels appear for each item or just once per item label value.
To add a blank line after each item Select Show or Don't Show.
To autofit columns widths on refresh Select to automatically resize the columns to fit the data whenever the PivotTable is refreshed. To show expand/collapse buttons Select to show the expand/collapse buttons for groups of columns with the same value. For example, if your PivotTable has annual sales data for a set of products, you might have a group of columns for each value of Year. To show error values Select to display the value in the text box for cells containing errors. To show empty cells Select to display the value in the text box for cells with empty values. Otherwise, Excel displays a default value.
To save source data with file Select to include the PivotTable's source data in the Excel file when you save. Note that this could result in a fairly large file. To refresh data on file open Select to have Excel refresh PivotTable data each time the file is opened.
To add a title Provide a brief title to help people who use screen readers know what is depicted by your PivotTable. To add a description Provide several sentences with more details about the PivotTable contents or data source to help people who use screen readers understand the purpose of your PivotTable.
To make substantial layout changes to a PivotTable or its various fields, you can use one of three forms:
To get the final layout results that you want, you can add, rearrange, and remove fields by using the PivotTable Field List. If you don't see the PivotTable Field List, make sure that the PivotTable is selected. If you still don't see the PivotTable Field List, on the Options tab, in the Show/Hide group, click Field List. If you don't see the fields that you want to use in the PivotTable Field List, you may need to refresh the PivotTable to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation. On the Options tab, in the Data group, click Refresh. For more information about working with the PivotTable Field List, see Use the Field List to arrange fields in a PivotTable.
Do one or more of the following:
In a PivotTable that is based on data in an Excel worksheet or external data from a non-OLAP source data, you may want to add the same field more than once to the Values area so that you can display different calculations by using the Show Values As feature. 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. For more information, see Show different calculations in PivotTable value fields.
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, click the field name in one of the areas, and then select one of the following commands:
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 further refine the layout of a PivotTable, you can make changes that affect the layout of columns, rows, and subtotals, such as displaying subtotals above rows or turning column headers off. You can also rearrange individual items within a row or column. Turn column and row field headers on or off
Display subtotals above or below their rows
Note: If None is selected, subtotals are turned off. On the Layout & Print tab, under Layout, click Show item labels in outline form, and then do one of the following:
Change the order of row or column items Do any of the following:
Adjust column widths on refresh
Move a column to the row labels area or a row to the column labels area You might want to move a column field to the row labels area or a row field to the column labels area to optimize the layout and readability of the PivotTable. When you move a column to a row or a row to a column, you are transposing the vertical or horizontal orientation of the field. This operation is also called "pivoting" a row or column. Do any of the following:
Merge or unmerge cells for outer row and column items You can merge cells for row and column items in order to center the items horizontally and vertically, or to unmerge cells in order to left-justify items in the outer row and column fields at the top of the item group.
Note: You cannot use the Merge Cells check box under the Alignment tab in a PivotTable.
There may be times when your PivotTable data contains blank cells, blank lines, or errors, and you want to change the way they are displayed. Change how errors and empty cells are displayed
Change how items and labels with no data are shown
You can choose from a wide variety of PivotTable styles in the gallery. In addition, you can control the banding behavior of a report. Changing the number format of a field is a quick way to apply a consistent format throughout a report. You can also add or remove banding (alternating a darker and lighter background) of rows and columns. Banding can make it easier to read and scan data. Apply a style to format a PivotTable You can quickly change the look and format of a PivotTable by using one of numerous predefined PivotTable styles (or quick styles).
Apply banding to change the format of a PivotTable
Remove a style or banding format from a PivotTable
Conditionally format data in a PivotTable Use a conditional format to help you visually explore and analyze data, detect critical issues, and identify patterns and trends. Conditional formatting helps you answer specific questions about your data. There are important differences to understand when you use conditional formatting on a PivotTable:
For more information, see Apply conditional formatting. Include OLAP server formatting If you are connected to a Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP) database, you can specify what OLAP server formats to retrieve and display with the data.
Preserve or discard formatting
|