How do I design the layout and format of a PivotTable report?

ANSWER:

After you have added the fields, displayed the appropriate level of details, created calculations, and sorted, filtered, and grouped data the way that you want in a PivotTable report, you often want to enhance the layout and format of the report to improve readability and to make it more attractive. There are a number of ways to change the layout and format of a PivotTable report as described in the following sections.

 Notes 

  • You can manually format a cell or cell range in PivotTable report by right-clicking the cell or cell range, by clicking Format Cells, and by using the Format Cells dialog box. However, you cannot use the Merge Cells check box under the Alignment tab in a PivotTable report.
  • You can also conditionally format a PivotTable report.

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

You can change the form, whether compact, outline or tabular, for a PivotTable report and for individual fields in the report.

PivotTable report  

  1. Click the PivotTable report.
  2. On the Design tab, in the Layout group, click Report Layout, and 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 is displayed.

    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.

Merge or unmerge cells for outer row and column items

You can merge cells for row and column items so that you can center the items horizontally and vertically, or unmerge cells to left-justify items in outer row and column fields at the top of the item group.

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

    The PivotTable Options dialog box is displayed.

  3. To merge or unmerge cells for outer row and column items , click the Layout & Format tab, and then under the Layout section, select or clear the Merge and center cells with labels check box.

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 is displayed.

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

  2. If subtotals aren't 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.

Move a column field to the row labels area, or move a row field 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.

Change how errors and empty cells are displayed

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

    The PivotTable Options dialog box is displayed.

  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.

Change the order of row and column items

  • Right-click the row and column label or item in a label, point to Move on the shortcut menu, 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.

Add, rearrange, and remove fields

To add, rearrange, and remove fields, use the PivotTable Field List.

  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.

For more information, see Create and change the field layout in a PivotTable or PivotChart report.

Tip  You can quickly delete a field from a PivotTable report by right-clicking the field, and then clicking Remove.

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 is displayed.

  3. Click the Layout & Format tab, and then under the Display section, do one of the following:
    • To adjust the PivotTable 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 column width, clear Autofit column widths on update.

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 OLAP 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 created prior to Office Excel 2007.

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.

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 is displayed.

    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 can't enter data in them.

Change the PivotTable format style

You can easily change the style of a PivotTable by using a gallery of styles. Excel provides numerous predefined table styles (or quick styles) that you can use to quickly format a PivotTable. You can also add or remove banding of rows and columns. Banding can make it easier to read and scan data.

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 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 style, you can remove any PivotTable style so that it is no longer applied to the data.

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.

Change the number format for a field

  1. In the PivotTable report, select the field for which you want to change the number format.
  2. On the Options tab in the Active Field group, click Field Settings.

    The Field Settings dialog box is displayed for labels and report filters, and the Values Field Settings dialog box is displayed for values.

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

    The Format Cells dialog box is displayed.

  4. In the Category list, click the format category that you want.
  5. Select the options that you want for the format, and then click OK twice.

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

Include OLAP Server formatting

If you are connected to a Microsoft SQL Server Analysis Services OLAP database, you can control whether the following OLAP server formats are retrieved from the server and are displayed 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 is displayed.

  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.

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 is displayed.

  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, 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    PivotChart formatting is also affected by this option. However, trendlines, data labels, error bars, and other changes to data series are not preserved.

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.
Was this answer helpful?YesNo

Technology Help & FAQ's

Topic Information
  • Topic #: 8035-6353
  • Date Created: 8/15/2007
  • Last Modified Since: 9/19/2014
  • Viewed: 2
Welcome Guest