Change the layout and format of a PivotTable report

After creating a PivotTable report and adding the fields that you want to analyze, you may want to enhance the report layout and format to make the data easier to read and scan for details. To change the layout of a PivotTable report, you can change the PivotTable form and the way that fields, columns, rows, subtotals, empty cells and lines are displayed. To change the format of the PivotTable report, you can apply a predefined style, banded rows, and conditional formatting.

What do you want to do?


Change the layout form of a PivotTable report

To make substantial layout changes to a PivotTable report or its various fields, you can use one of three forms:

  • Compact form    displays items from different row area fields in one column and uses indentation to distinguish between the items from different fields. Row labels take up less space in compact form, which leaves more room for numeric data. Expand and Collapse buttons are displayed so that you can display or hide details in compact form. Compact form is saves space and makes the PivotTable report more readable and is therefore specified as the default layout form for PivotTables.

PivotTable in compact form

  • Tabular form    displays one column per field and provides space for field headers.

PivotTable in tabular form

  • Outline form    is similar to tabular form but it can display subtotals at the top of every group because items in the next column are displayed one row below the current item.

PivotTable in outline form

Change a PivotTable report to compact, outline, or tabular form

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options and a Design tab.

  1. On the Design tab, in the Layout group, click Report Layout, and then do one of the following:
  • To keep related data from spreading horizontally off of the screen and to help minimize scrolling, click Show in Compact Form.

In compact form, fields are contained in one column and indented to show the nested column relationship.

  • To outline the data in the classic PivotTable style, click Show in Outline Form.
  • To see all data in a traditional table format and to easily copy cells to another worksheet, click Show in Tabular Form.

Excel Ribbon Image

Top of Page Top of Page

Change the way item labels are displayed in a layout form

  1. In the PivotTable report, select a row field.

This displays the PivotTable Tools, adding an Options and a Design tab.

 Tip   You can also double-click the row field in outline or tabular form, and continue with step 3.

  1. On the Options tab, in the Active Field group, click Field Settings.

Excel Ribbon Image

  1. In the Field Settings dialog box, click the Layout & Print tab, and then under Layout, do one of the following:
  • To show field items in outline form, click Show item labels in outline form.
  • To display or hide labels from the next field in the same column in compact form, click Show item labels in outline form, and then select Display labels from the next field in the same column (compact form).
  • To show field items in table-like form, click Show item labels in tabular form.

Top of Page Top of Page

Change the field arrangement in a PivotTable report

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 report 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 report 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 Pivot data in a PivotTable or PivotChart report.

Add fields to a PivotTable report

Do one or more of the following:

  • Select the check box next to each field name in the field section. The field is placed in a default area of the layout section, but you can rearrange the fields if you want.

By default, text fields are added to the Row Labels area, numeric fields are added to the Values area, and Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) date and time hierarchies are added to the Column Labels area.

  • Right-click the field name and then select the appropriate command — Add to Report Filter, Add to Column Label, Add to Row Label, or Add to Values — to place the field in a specific area of the layout section.
  • Click and hold a field name, and then drag the field between the field section and an area in the layout section.

Top of Page Top of Page

Copy fields in a PivotTable report

In a PivotTable report that is based on data in an Excel worksheet or external data from a non-OLAP source data (non-OLAP source data: Underlying data for a PivotTable or PivotChart report that comes from a source other than an OLAP database. These sources include relational databases, lists on Excel worksheets, and text file databases.), 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.

  1. Click and hold a field name in the field section, and then drag the field to the Values area in the layout section.
  2. Repeat step 1 as many times as you want to copy the field.
  3. In each copied field, change the summary function or custom calculation the way you want.

 Notes 

  • When you add two or more fields to the Values area, whether they are copies of the same field or different fields, the Field List automatically adds a Values Column label to the Values area. You can use this field to move the field positions up and down within the Values area. You can even move the Values Column label to the Column Labels area or Row Labels areas. However, you can’t move the Values Column label to the Report Filters area.
  • You can add a field only once to either the Report Filter, Row Labels, or Column Labels 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 Row Labels and the Column Labels areas in the layout section — the field is automatically removed from the original area and put in the new area.
  • Another way to add the same field to the Values area is by using a formula (also called a calculated column) that uses that same field in the formula.
  • You cannot add the same field more than once in a PivotTable report that is based on an OLAP data source.

Top of Page Top of Page

Rearrange fields in a PivotTable report

You can rearrange existing fields or reposition those fields by using one of the four areas at the bottom of the layout section:

PivotTable report Description PivotChart Description
Values     Use to display summary numeric data. Values     Use to display summary numeric data.
Row Labels     Use to display fields as rows on the side of the report. A row lower in position is nested within another row immediately above it. Axis Field (Categories)     Use to display fields as an axis in the chart.
Column Labels     Use to display fields as columns at the top of the report. A column lower in position is nested within another column immediately above it. Legend Fields (Series) Labels     Use to display fields in the legend of the chart.
Report Filter     Use to filter the entire report based on the selected item in the report filter. Report Filter     Use to filter the entire report based on the selected item in the report filter.

To rearrange fields, click the field name in one of the areas, and then select one of the following commands:

Select this

   

To

   
Move Up    Move the field up one position in the area.
Move Down    Move the field down position in the area.
Move to Beginning    Move the field to the beginning of the area.
Move to End    Move the field to the end of the area.
Move to Report Filter    Move the field to the Report Filter area.
Move to Row Labels    Move the field to the Row Labels area.
Move to Column Labels    Move the field to the Column Labels area.
Move to Values    Move the field to the Values area.
Value Field Settings, Field Settings    Display the Field Settings or Value Field Settings dialog boxes. For more information about each setting, click the Help button Button image at the top of the dialog box.

 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.

Top of Page Top of Page

Remove fields from a PivotTable report

  1. Click the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. To display the PivotTable Field List, if necessary, on the Options tab, in the Show group, click Field List.

Excel Ribbon Image

  1. To remove a field, in the PivotTable Field List, do one of the following:
  • In the PivotTable Field List, clear the check box next to the field name.

 Note   Clearing a check box in the Field List removes all instances of the field from the report.

  • In a Layout area, click the field name, and then click Remove Field.
  • Click and hold a field name in the layout section, and then drag it outside the PivotTable Field List.

Top of Page Top of Page

Change the layout of columns, rows, and subtotals

To further refine the layout of a PivotTable report, 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

  1. Click the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. To switch between showing and hiding field headers, on the Options tab, in the Show group, click Field Headers.

Excel Ribbon Image

Top of Page Top of Page

Display subtotals above or below their rows

  1. In the PivotTable report, select the row field for which you want to display subtotals.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

 Tip   In outline or tabular form, you can also double-click the row field, and then continue with step 3.

  1. On the Options tab, in the Active Field group, click Field Settings.

Excel Ribbon Image

  1. In the Field Settings dialog box, on the Subtotals & Filters tab, under the Subtotals, click Automatic or Custom.

 Note    If None is selected, subtotals are turned off.

  1. On the Layout & Print tab, under Layout, click Show item labels in outline form, and then do one of the following:
  • To display subtotals above the subtotaled rows, select the Display subtotals at the top of each group check box. This option is selected by default.
  • To display subtotals below the subtotaled rows, clear the Display subtotals at the top of each group check box.

Top of Page Top of Page

Change the order of row or column items

Do any of the following:

  • In the PivotTable report, right-click the row or column label or the item in a label, point to Move, and then use one of the commands on the Move menu to move the item to another location.
  • Select the row or column label item that you want to move, and then point to the bottom border of the cell. When the pointer becomes a four-headed pointer, drag the item to a new position. The following illustration shows how to move a row item by dragging.

Example of moving an item in a PivotTable report

Top of Page Top of Page

Adjust column widths on refresh

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. In the PivotTable Options dialog box, on the Layout & Format tab, under Format, do one of the following:
  • To automatically fit the PivotTable report columns to the size of the widest text or number value, select the Autofit column widths on update check box.
  • To keep the current PivotTable report column width, clear the Autofit column widths on update check box.

Top of Page Top of Page

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 report. 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:

  • Right-click a row field, point to Move <field name>, and then click Move <field name> To Columns.
  • Right-click a column field, and then click Move <field name> to Rows.
  • Drag a row or column field to a different area. The following illustration shows how to move a column field to the row labels area.

Example of changing the layout of a PivotTable report

Callout 1 Click a column field
Callout 2 Drag it to the row area
Callout 3 Sport becomes a row field like Region

Top of Page Top of Page

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.

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. In the PivotTable Options dialog box, click the Layout & Format tab, and then under Layout, select or clear the Merge and center cells with labels check box.

 Note    You cannot use the Merge Cells check box under the Alignment tab in a PivotTable report.

Top of Page Top of Page

Change the display of blank cells, blank lines, and errors

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

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. In the PivotTable Options dialog box, click the Layout & Format tab, and then under Format, do one or more of the following:
  • To change the error display, select the For error values show check box. In the box, type the value that you want to display instead of errors. To display errors as blank cells, delete any characters in the box.
  • To change the display of empty cells, select the For empty cells show check box, and then type the value that you want to display in empty cells in the text box.

 Tip    To display blank cells, delete any characters in the box. To display zeros, clear the check box.

Top of Page Top of Page

Display or hide blank lines after rows or items

Do any of the following:

  • For rows, do the following:
  1. In the PivotTable report, select a row field.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

 Tip   In outline or tabular form, you can also double-click the row field, and then continue with step 3.

  1. On the Options tab, in the Active Field group, click Field Settings.

Excel Ribbon Image

  1. In the Field Settings dialog box, on the Layout & Print tab, under Layout, select or clear the Insert blank line after each item label check box.
  • For items, do the following:
  1. In the PivotTable report, select the item you want.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Design tab, in the Layout group, click Blank Rows, and then select the Insert Blank Line after Each Item Label or Remove Blank Line after Each Item Label check box.

Excel Ribbon Image

 Note   You can apply character and cell formatting to the blank lines, but you cannot enter data in them.

Top of Page Top of Page

Change how items and labels with no data are shown

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. On the Display tab, under Display, do one or more of the following:
  • To show items with no data on rows, select or clear the Show items with no data on rows check box to display or hide row items that have no values.

 Note   This setting is only available for an Online Analytical Processing (OLAP) (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) data source.

  • To show items with no data on columns, select or clear the Show items with no data on columns check box to display or hide column items that have no values.

 Note   This setting is only available for an OLAP data source.

  • To display item labels when no fields are in the values area, select or clear the Display item labels when no fields are in the values area check box to display or hide item labels when there are no fields in the value area.

 Note   This check box only applies to PivotTable reports that were created by using versions of Microsoft Office Excel earlier than Office Excel 2007.

Top of Page Top of Page

Change or remove formatting

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 report

You can quickly change the look and format of a PivotTable report by using one of numerous predefined PivotTable styles (or quick styles).

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Design tab, in the PivotTable Styles group, do any of the following:
  • Click a visible PivotTable style or scroll through the gallery to see additional styles.
  • To see all of the available styles, click the More button at the bottom of the scroll bar.

Excel Ribbon Image

If you want to create your own custom PivotTable report style, click New PivotTable Style at the bottom of the gallery to display the New PivotTable Style dialog box.

Top of Page Top of Page

Apply banding to change the format of a PivotTable report

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Design tab, in the PivotTable Style Options group, do one of the following:
  • To alternate each row with a lighter and darker color format, click Banded Rows.
  • To alternate each column with a lighter and darker color format, click Banded Columns.
  • To include row headers in the banding style, click Row Headers.
  • To include column headers in the banding style, click Column Headers.

Excel Ribbon Image

Top of Page Top of Page

Remove a style or banding format from a PivotTable report

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Design tab, in the PivotTable Styles group, click the More button at the bottom of the scroll bar to see all of the available styles, and then click Clear at the bottom of the gallery.

Excel Ribbon Image

Top of Page Top of Page

Conditionally format data in a PivotTable report

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 report:

  • If you change the layout of the PivotTable report by filtering, hiding levels, collapsing and expanding levels, or moving a field, the conditional format is maintained as long as the fields in the underlying data are not removed.
  • The scope of the conditional format for fields in the Values area can be based on the data hierarchy and is determined by all the visible children (the next lower level in a hierarchy) of a parent (the next higher level in a hierarchy) on rows for one or more columns, or columns for one or more rows.

 Note   In the data hierarchy, children do not inherit conditional formatting from the parent, and the parent does not inherit conditional formatting from the children.

  • There are three methods for scoping the conditional format of fields in the Values area: by selection, by corresponding field, and by value field.

For more information, see Add, change, find, or clear conditional formats.

Top of Page Top of Page

Change the number format for a field

  1. In the PivotTable report, select the field of interest.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Options tab in the Active Field group, click Field Settings.

Excel Ribbon Image

The Field Settings dialog box displays labels and report filters; the Values Field Settings dialog box displays values.

  1. Click Number Format at the bottom of the dialog box.
  2. In the Format Cells dialog box, in the Category list, click the number format that you want to use.
  3. Select the options that you prefer, and then click OK twice.

 Tip   You can also right-click a value field, and then click Number Format.

Top of Page Top of Page

Include OLAP server formatting

If you are connected to a Microsoft SQL Server Analysis Services Online Analytical Processing (OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.)) database, you can specify what OLAP server formats to retrieve and display with the data.

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Options tab, in the Data group, click Change Data Source, and then click Connection Properties.

Excel Ribbon Image

  1. In the Connection Properties dialog box, on the Usage tab, and then under the OLAP Server Formatting section, do one of the following:
  • To enable or disable number formatting, such as currency, dates, and times, select or clear the Number Format check box.
  • To enable or disable font styles, such as bold, italics, underline, and strikethrough, select or clear the Font Style check box.
  • To enable or disable fill colors, select or clear the Fill Color check box.
  • To enable or disable text colors, select or clear the Text Color check box.

Top of Page Top of Page

Preserve or discard formatting

  1. Click anywhere in the PivotTable report.

This displays the PivotTable Tools, adding an Options tab and a Design tab.

  1. On the Options tab, in the PivotTable group, click Options.

Excel Ribbon Image

  1. On the Layout & Format tab, under Format, do one of the following:
  • To save the PivotTable report layout and format so that it is used each time that you perform an operation on the PivotTable report, select the Preserve cell formatting on update check box.
  • To discard the PivotTable report layout and format and resort to the default layout and format each time that you perform an operation on the PivotTable, clear the Preserve cell formatting on update check box.

 Note   While this option also affects the PivotChart report formatting, trendlines (trendline: A graphic representation of trends in data series, such as a line sloping upward to represent increased sales over a period of months. Trendlines are used for the study of problems of prediction, also called regression analysis.), data labels (data label: A label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell.), error bars (error bars: Usually used in statistical or scientific data, error bars show potential error or degree of uncertainty relative to each data marker in a series.), and other changes to specific data series are not preserved.

Top of Page Top of Page

 
 
Applies to:
Excel 2010