Excel report: Applying report layouts and styles

Excel report: YOUR OBJECTIVE

To present the information in your pivot table most effectively, i.e., to make it easier to read and understand or to give it greater visual impact, you can change its appearance by using a pre-defined layout and/or style.

This learning module “excel report” will show you how to use pre-defined PivotTable layouts and styles.

Excel report: HOW DOES IT WORK?

To help make your Excel pivot table most effective for your intended purpose, you can choose any of three different pivot-table layouts. By default, your pivot table will first be displayed in the Compact Form layout. The other two layouts are the Outline Form and the Tabular Form. All three are described below:

Excel report: Compact Form report layout

This format is generally used when the pivot-table rows contain two fields. In this case, the the second-level field will be indented and can by expanded or collapsed by clicking the plus or minus   icon that appears just to the left of the first-level field label. As you can see in the next illustration, the values listed in the secondary-level fields are sub-totaled in each primary-level row, in the columns to the right of the primary-level field label.

If you want to use this format, but it did not appear by default, you can activate it in the following manner: Having clicked somewhere on the pivot table, the PivotTable Tools context tab, along with its subordinated Options and Designtabs, appears. Now, on the Design tab, under PivotTable Tools, click the  button in the Layout group. In the selection catalog that appears, click the command.

Excel report: HINT  (1)

If you want to collapse or expand all field-label levels, select any first-level field label, and then on the Options tab, underPivotTable Tools, click the Collapse Entire Field  or   Excel report: Expand Entire Field button in the Active Field group .

Excel report: CAUTION

If the((Bild3)) and((Bild2)) icons are not currently displayed in your pivot table, click the button in the Show group (Optionstab, under PivotTable Tools).

Excel report: Outline Form report layout

Select this layout by clicking the button in the Layout group (Designtab, under PivotTable Tools). Then, in the selection catalog that appears, click the
command.

The Outline Form layout is very similar to the Compact Form, but the secondary-level field lables are displayed in a column of their own, just to the right of the primary-level field labels. Here, as in the Compact Form, all values subordinated to a primary-level field are sub-totaled in the primary-level row.

Excel report: Tabular Form report layout

Select this layout by clicking the button in the Layout group (Designtab, under PivotTable Tools). In the selection catalog that appears, click the
command.

In the Tabular Form layout, just as in the Outline Form, primary field labels are displayed in one column and secondary field labels are displayed in the next column. In this form, however, the first of the secondary-level field names appears in the same row as the primary-level field name, not one row below it. Here, the sub-totals for secondary levels appear in a row by themselves, just below the last secondary-field row (for each primary group).

Excel report: HINT (2)

Choose whether or not to display the primary-level field labels in every row or only in the first row by clicking the
or  option.

Excel report: Pivot-Table styles

To change the accent colors used in your pivot table, apply a different pivot-table style. To choose a style for your pivot table, first select any cell within your pivot table. Then, in the PivotTable Styles group (Designtab, under PivotTable Tools), select the style you want from the selection catalog shown. If you need more choices, click the More button at the lower-right corner of the selection catalog.

Excel report: HINT (3)

If you hover your mouse pointer over any of the various styles, the corresponding style will be displayed in a live preview without your actually applying the style. This allows you to try out various styles before making up your mind.

Other ways to modify your pivot-table style are found in the PivotTable Style Options group on the Design tab, under PivotTable Tools.

Here you can activate or deactivate the row-or-column header accents by clicking the or button respectively.

If you want to optically distinguish alternate rows, activate the option. This may improve the readability of the table. Below, you see an example of the effect of using this option:

 

If you want to optically distinguish alternate columns, select the button.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen