|
<< Click to Display Table of Contents >> Pivot table settings |
In the field list below the areas section with the 4 areas, you will find the Pivot table settings button. You can also access the command via the context menu by right-clicking directly in the pivot table report.
You can make further adjustments to the structure of your pivot table here, which you cannot find in the field settings or value settings, since pivot table settings are of a general nature. Unlike the field settings and value settings, the changed options in the pivot table settings will remain in effect when you remove the fields from the areas section.
▪Show totals for rows/columns: This shows or hides grand totals for rows/columns.
▪Indent row labels...character(s): If you have arranged several fields in the Row labels area and the outline form with compact format is set in the Field settings, the lower-level field is displayed indented below it. Here, you can now specify the extent of the indentation from the left edge of the column (default is 1 character).
▪Autofit column width: If this option is enabled, the column width of the pivot table automatically adjusts to the text of the columns so that the text is not truncated by the next column. This has its advantages, but the disadvantage is that manual adjustments to the column width may be discarded when changes are made to the fields. Disable this option to retain the column width that you set manually.
▪Preserve cell formatting: If this option is enabled, the formatting you made manually in the pivot table (for example, using a different font, shading, border) will be retained. If the option is disabled, such formatting changes will be discarded as soon as you update the pivot table or reopen the workbook.
Note: Number formatting must be set using the Number format button (in the Value settings on the Show values as tab) in order to be permanently retained.
▪Classic pivot table format: If you are confused by the headers "Row labels" and "Column labels" in the pivot table report and would prefer to display the real names of the fields instead, enable this option for a classic representation (known from older versions of Microsoft Excel up to 2003).
▪Show the values row: Enable the checkbox to insert an additional row with the heading "Values" above the pivot table report. Note: The values row is displayed only if there are at least two fields in the Values area (at the bottom of the field list) and cannot be enabled for the classic pivot table format.
▪Report filter section: You can combine the following options Display fields and Fields per row to rearrange the placement of report filters in the pivot table report.
| Display fields – If there are at least two fields in the report filter area, you use this option to specify how the fields in the report filter area are arranged in the pivot table report. You can choose between First down, then right or First right, then down. |
| Fields per row – Here, you can also specify for the report filters of the pivot table report how many fields are displayed per column before a new column starts. |
▪Field list section:
| Sort A-Z/Sort in data source order – The field names in the field list are sorted according to the order of the columns from your source data. You can also display the fields in alphabetical order to find specific field names in the fields section more quickly. |
Note: The order specified here does not affect the display in the pivot table report. For information on the alphabetical sorting of elements directly in the pivot table report, see Sorting and filtering pivot tables.
▪Display section
| Show field captions and filter dropdowns – The field names and filter symbols for row labels and column labels are displayed/hidden in the pivot table report. |
▪Use fill lists when sorting:
| If this option is enabled, the field items of the pivot table are sorted using the lists defined for automatic filling. On the one hand, these are the lists predefined in PlanMaker (for example Mon;Tue;Wed;... for the sequence of weekdays) and, on the other hand, lists you create yourself. For more information on how to create such lists, see Editing lists for automatic filling. |
| If, based on our example table, you would like the items of the Article field to always be displayed sorted in a certain order Tea;Cream;Snacks;Coffee;Sweetener, first create a corresponding custom fill list. Then click on the Refresh data button located below the areas section of the field list. |
| Note: You can only use text for fill lists but not numbers. |
| If the option is disabled, the lists will not be used for automatic filling and the data will be sorted alphabetically (thus, Coffee;Cream;Snacks;Sweetener;Tea in this example). |
You can customize the visual appearance of the pivot table report in various ways, just as you do with normal table styles. Select a style source (Excel 2007, Excel 2003, Excel 97) from the upper left list in the Style section, and choose one of the pivot styles with different formatting from the list below. For the Excel 2007 style templates, the selection light/dark/medium is also available in the upper right list.
The options Banded rows, Banded columns, Row headers and Column headers are displayed automatically adapted to the style template. For the Excel 2007 style templates, you can manually modify these options to your liking.
Enabling the option Banded rows/Banded columns causes alternating background colors of the rows/columns, while enabling the option Row headers/Column headers highlights the first row/column of the pivot table.
The change of the design is displayed directly in the pivot table report as a preview and has to be confirmed with OK in order to be finally adopted.
▪Special values section:
| Show for error values – Cells with incorrect calculations return the known error values also in the pivot table report (for example, #N/A). For more information, see Error values). Enable this option and enter an individual text in the right input field to display self-defined output values that represent the error values. |
| Show for empty cells – If no data can be assigned to an item (this usually occurs with combinations of row and column labels for cross-classified tables), an empty cell is displayed in the pivot table report. If you disable the checkbox, a 0 is displayed instead of empty cells. If the checkbox is enabled, you can also enter an individual text in the input field next to it to display a self-defined output value instead of an empty cell or 0. |
▪Pivot table data section:
| Save source data with file – If this option is enabled, the source data is stored in an internal cache and is available from the cache for the pivot table independently of your source data. In a way, a copy of the source table is created. This is particularly useful for external source data: If it no longer exists, you can still work on your pivot table with the data from the pivot cache. |
| If you disable this option, your data will not be stored in the pivot cache. When you re-open the file, you have to click on the Refresh data button (see Updating pivot tables and changing data area) to initialize the pivot cache for the pivot table with the source data. In the case of external source data, the following applies: If it no longer exists, your pivot table cannot be updated either. |
| Refresh when opening the file – If this option is enabled, the pivot table data is automatically updated to current source data when you open the file. If you disable this option, you can continue working with your old pivot data until you trigger the Refresh data button. For more information, see Updating pivot tables and changing data area. |
Please note: Updating the data with the option Refresh when opening the file or with the Refresh data button will replace the pivot cache with updated source data, thus overwriting the previous data in your pivot table.
On the Text tab, you can enter a custom Name, a Title and a Description for your pivot table. The name is automatically generated by PlanMaker in sequence when a pivot table is created (Pivot table 1, Pivot table 2, etc.) and is shown as a title in the blank pivot table report. An entry is also created in Named ranges, but it cannot be renamed there. Furthermore, the assigned Name cannot be used to identify the pivot table from the GETPIVOTDATA function; thus, you must explicitly create a new entry for the pivot table in the Named ranges.
Apart from that, the entries from the Text tab do not appear anywhere in the pivot table. Title and Description can be useful as internal information. To rename the fields that actually appear in the pivot table, see Working with the field list: Fields section.