Excel views: Special pivot table


You created a pivot table that summarizes your company’s sales, but now you want to change its appearance to stress or de-emphasize certain information, to make it easier to read and understand.

This learning module” excel views” will show you several ways to help you meet your goal: You will be shown how to hide certain information, how to separate out individual reports from a complex pivot table, how to insert blank rows, etc.

Excel views: HOW DOES IT WORK?

Sometimes you will want to depict the information in your pivot table with greater clarity and/or present it with more impact. Excel gives you various methods for accomplishing this: You can, for example, make visible the underlying data that Excel summarizes in a pivot-table report value. Or you can modify your pivot table by hiding or unhiding groupings. You can even display pivot-table variants, corresponding to the report filter items, each in its own worksheet All these excel views can be attained quickly and efficiently. The following sections provide detailed directions:

Excel views: Hiding or unhiding grouped values

Grouped values are easily recognized by the small – and + buttons, so-called drill-down indicators, that appear in the pivot table next to the group names. To collapse a group, thus hiding all subordinated values, just use your mouse to click the minus icon. Whenever a group has been collapsed, the minus icon changes to a plus icon. Clicking the plus icon explands the underlying values again. Note that values hidden in this fashion are not deleted; they are merely out of sight and still participate in any pivot-table calculations, just as when the group is expanded.

If you want to hide or unhide multiple groupings at once, look at the Active Field group (Options tab, under PivotTable Tools). There you will find the Collapse Entire Field and the Expand Entire Field buttons that you can use to carry out the corresponding actions.

Excel views: CAUTION

The Collapse Entire Field and Expand Entire Field  buttons remain unenabled (button labels appear in halftone) until you position your mouse pointer on row or column labels.

Excel views: Displaying the underlying source data of a value field

Because pivot tables use an arithmetic functions to summarize source data, you might imagine that you would have to revisit the source data table to find the data that comprises a summary number. That is, however, not necessary. Tthere is another, much quicker way to display the underlying data. You can visualize the data behind a pivot-table-report summary number by drilling down on the value.

To obtain this useful view, double-click the number in the values area of the pivot-table report. Excel automatically creates a new spreadsheet with just the corresponding rows of data extracted from the source spreadsheet. The following screenshots illustrate this drilling down.

Excel views: Displaying report-filter items individually

If you use a Report Filter in your pivot table, you can easily create multiple spreadsheets, each of which contains a pivot-table corresponding to one of the report-filter items. In the current example, the report filter was set up with the Customer Group field, which consists of three items: A, B and C.

To create such multiple spreadsheets, first activate your pivot table by clicking on any cell in it. Then go to the Options tab under the PivotTable Tools contextual tab. There, in the PivotTable group, click the small black arrow on the  button. In the selection list that appears, choose the command. The Show Report Filter Pages dialog box will open. Now select the item with the field name of interest. Then, when you click  , Excel will create the desired reports, each on its own spreadsheet (see new spreadsheet tabs). In this example, because there are three Customer Group items, three new spreadsheets have been created and named accordingly.

Excel views: Inserting blank rows

Sometimes it will help you attain your goal of creating an easy-to-read and understand pivot-table report, if you can insert a blank row after each group of items in the row fields. This can only be done, if the pivot table consist of at least two row groupings.

To do this, click in one of the grouped items. Then go to the Options tab of the PivotTable Tools. Now click the button in the Active Field group. This opens the Field Settings dialog box. Select the Layout & Print tab in this dialog box.

Activate the Insert blank line after each item label option, and then click. As you can see below, a blank row will be inserted after each grouping.

Excel views: HINT

To accomplish the same thing, you can also use the  button in the Layout group (Design tab, under PivotTable Tools), with which you can add or remove blank rows after each grouped item.

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

Fatal error: Call to undefined function stc_get_connect_button() in /usr/www/users/soluzi/en_excelhelfer/wp-content/plugins/simple-twitter-connect/stc-comments.php on line 231