Show All

Hide All

After you create a PivotTable report and have added the fields that you want, you often want to enhance the layout and format of the report to improve readability and to make it more attractive. This article discusses various ways you can work with the layout and format of a report.

 Important   Refer to the See Also section for useful demos and training that provide important background information about PivotTable reports, in addition to other ways to change the format of a report.

• Change the form layout and field arrangement

• Change the PivotTable report form: compact, outline, or tabular
• Add, copy, rearrange, and remove fields

• Change the layout of columns, rows, and subtotals

• Turn column and row field headers on or off
• Display subtotals above or below their rows
• Change the order of row or column items
• Adjust column widths on refresh
• Move a column to the row labels area or a row to the column labels area
• Merge or unmerge cells for outer row and column items

• Change how blank cells, blank lines, and errors are displayed

• Change how errors and empty cells are displayed
• Display or hide blank lines
• Change how items and labels with no data are shown

• Change or remove formatting

• Change the PivotTable format style
• Conditionally format data in a PivotTable report
• Change the number format for a field
• Include OLAP server formatting
• Preserve or discard formatting
• Remove all formatting from a report

Change the form layout and field arrangement

To make substantial changes to the layout and format of the report, you can organize the entire report into three forms — compact, outline, or tabular. You can also add, rearrange, and remove fields to get the final results that you want.

 Top of Page

Change the PivotTable report form: compact, outline, or tabular

You can change the form, whether compact, outline or tabular, for a PivotTable report and any of its various fields.

PivotTable report  

1. Click the PivotTable report.
2. On the Design tab, in the Layout group, click Report Layout, and then do one of the following:
   • Show in Compact Form  Use to keep related data from spreading horizontally off of the screen and to help minimize scrolling. Beginning fields on the side are contained in one column and are indented to show the nested column relationship.
   • Show in Outline Form  Use to outline the data in the classic PivotTable style.
   • Show in Tabular Form  Use to see all data in a traditional table format and to easily copy cells to another worksheet.

Fields  

• Select a row field, and then on the Options tab, in the Active Field group, click Field Settings.

  The Field Settings dialog box opens.

   Tip   You can also double-click the row field in outline or tabular form.

• Click the Layout & Print tab, and then under the Layout section, 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

Add, copy, rearrange, and remove fields

You use the PivotTable Field List to add, copy, rearrange, and remove fields.

For more information about working with the PivotTable Field List, see Create and change the field layout in a PivotTable or PivotChart report.

  Notes  

• If you don't see the PivotTable Field List, make sure that the PivotTable or PivotChart report is selected. If you still don't see the PivotTable Field List, for a PivotTable report, on the Options tab, in the Show/Hide group, click Field List, and for a PivotChart report, on the Analyze tab, in the Data group, click Field List.
• If you don't see the fields in the Field List that you want to use, refresh the PivotTable or PivotChart report to display any new fields, calculated fields, measures, calculated measures, or dimensions that you have added since the last operation.

Add fields

• To add fields to the 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, non-numeric fields are added to the Row Labels area, numeric fields are added to the Values area, and OLAP 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.

   Tip   You can also 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

Copy fields

In a PivotTable report that is based on data in an Excel worksheet or external data from a non-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.

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

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

  Move Up	Moves the field up one position in the area.
  Move Down	Moves the field down position in the area.
  Move to Beginning	Moves the field to the beginning of the area.
  Move to End	Moves the field to the end of the area.
  Move to Report Filter	Moves the field to the Report Filter area.
  Move to Row Labels	Moves the field to the Row Labels area.
  Move to Column Labels	Moves the field to the Column Labels area.
  Move to Values	Moves the field to the Values area.
  Value Field Settings, Field Settings	Displays the Field Settings or Value Field Settings dialog boxes. For more information about each setting, click the Help button 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

Remove fields

• To remove a field, click the field name in one of the layout areas, and then click Remove Field, or clear the check box next to each field name in the field section.

   Tip   You can also click and hold a field name in the layout section, and then drag it outside the PivotTable Field List.

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

1. Click the PivotTable report.
2. If necessary, on the Options tab, in the Show/Hide group, click Field List, and for a PivotChart report, on the Analyze tab, in the Show/Hide group, click Field List.

 Top of Page

Change the layout of columns, rows, and subtotals

To further refine the layout of the 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.
2. To switch between showing and hiding field headers, on the Options tab, in the Show/Hide group, click Field Headers.

 Top of Page

Display subtotals above or below their rows

1. Select the row field, and then on the Options tab, in the Active Field group, click Field Settings.

   The Field Settings dialog box opens.

    Tip   You can also double-click the row field in outline or tabular form.

2. If subtotals are not turned on (the option None is selected), click the Subtotals & Filters tab, and then under the Subtotals & Filters section, click Automatic or Custom.
3. Click the Layout & Print tab, and then under the Layout section, click Show item labels in outline form.
4. Do one of the following:
   • To display subtotals above the subtotaled rows, select the Display subtotals at the top of each group check box.
   • To display subtotals below the subtotaled rows , clear the Display subtotals at the top of each group check box.

 Top of Page

Change the order of row or column items

• Right-click the row and column label or item in a label, point to Move, and then use one of the commands on the Move menu to move the item.

You can also select the row or column label item, and then point to the bottom border of the cell. When the pointer becomes an arrow, drag the item to a new position. The following illustration shows how to select a row item.

 Top of Page

Adjust column widths on refresh

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.

   The PivotTable Options dialog box appears.

3. Click the Layout & Format tab, and then under the Display section, do one of the following:
   • To adjust the PivotTable report columns to automatically fit to the size of the widest text or number value, select Autofit column widths on update.
   • To keep the current PivotTable report column width, clear Autofit column widths on update.

 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.

• Right-click the row field, point to Move <field name>, and then click Move <field name> To Columns, or right-click the column field, and then click Move <field name> to Rows.

  You can also drag a field. The following illustration shows how to move a column field to the row labels area.

  ---

  

   Click a column field
   Drag it to the row area
   Sport becomes a row field like Region

  ---

 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 the PivotTable report.
2. the Options tab, the PivotTable group, click Options.

   The PivotTable Options dialog box appears.

3. To merge or unmerge cells for outer row and column items (item: A subcategory of a field in PivotTable and PivotChart reports. For instance, the field "Month" could have items such as "January," "February," and so on.), click the Layout & Format tab, and then under the Layout section, 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

Change how blank cells, blank lines, and errors are displayed

There may be times when your data contains blank cells, blank lines, or errors, and you want to adjust the default behavior of a report.

Change how errors and empty cells are displayed

1. Click the PivotTable report.
2. the Options tab, in the PivotTable group, click Options.

   The PivotTable Options dialog box appears.

3. Click the Layout & Format tab, and then under the Format section, do one or more of the following:

   Change 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.

   Change empty cell display  Select the For empty cells show check box. In the box, type the value that you want to display in empty cells. To display blank cells, delete any characters in the box. To display zeros, clear the check box.

 Top of Page

Display or hide blank lines

You can display or hide blank lines after a row or item.

Rows  

1. Select the row field, and then on the Options tab, in the Active Field group, click Field Settings.

   The Field Settings dialog box opens.

    Tip   You can also double-click the row field in outline or tabular form.

2. To add or remove the blank rows, click the Layout & Print tab, and then under the Layout section, select or clear Insert blank line after each item label.

Items  

1. Select the item in a PivotTable report.
2. On the Design tab, in the Layout group, click Blank Rows, and then select Insert Blank Line after Each Item Label or Remove Blank Line after Each Item Label.

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

 Top of Page

Change how items and labels with no data are shown

1. Click the PivotTable report.
2. Click the Display tab, and then under the Display section, do one or more of the following:
   • Show items with no data on rows   Select or clear 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.

   • Show items with no data on columns  Select or clear to display or hide column items that have no values.

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

   • Display item labels when no fields are in the values area  Select or clear 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

Change or remove formatting

You can choose from a wide variety of 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.

Change the PivotTable report format style

You can easily change the style of a PivotTable report by using a gallery of styles. Office Excel 2007 provides numerous predefined table styles (or quick styles) that you can use to quickly format a PivotTable 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 styles

1. Click the PivotTable report.
2. On the Design tab, in the PivotTable Styles group, do the following:
   • Click a visible style, scroll through the gallery, or to see all of the available styles, click the More button at the bottom of the scroll bar.
   • Optionally, if you have displayed all of the available styles and 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.

      Note    Although you can delete only a custom PivotTable report style, you can remove any PivotTable report style so that it is no longer applied to the data.

Apply banding

1. Click the PivotTable report.
2. 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.

 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, or clear conditional formats.

 Top of Page

Change the number format for a field

1. In the PivotTable report, select the field of interest.
2. On the Options tab in the Active Field group, click Field Settings.

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

3. Click Number Format at the bottom of the dialog box.

   The Format Cells dialog box opens.

4. In the Category list, click the format category of interest.
5. Select the options that you prefer, and then click OK twice.

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

 Top of Page

Include OLAP server formatting

If you are connected to a Microsoft SQL Server Analysis Services OLAP database, you can specify what OLAP server formats to retrieve and display with the data.

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

   The Connection Properties dialog box appears.

3. Click the Usage tab, and then under the OLAP Server Formatting section, do one of the following:
   • Number Format Select or clear to enable or disable number formatting, such as currency, dates, and times.
   • Font Style Select or clear this check box to enable or disable font styles, such as bold, italics, underline, and strikethrough.
   • Fill Color Select or clear this check box to enable or disable fill colors.
   • Text Color Select or clear this check box to enable or disable text colors.

 Top of Page

Preserve or discard formatting

1. Click the PivotTable report.
2. On the Options tab, in the PivotTable group, click Options.

   The PivotTable Options dialog box appears.

3. Click the Layout & Format tab, and then under the Format section, 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

Remove all formatting from a report

1. Click the PivotTable report.
2. 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.

 Top of Page