|
<< 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 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: Shows or hides grand totals for rows/columns.
▪Indent row labels...characters: 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 field of the lower-level is displayed indented below it. Here you can now define 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 fits to the text of the columns so that the text is not cut off by the next column. This has advantages, but the downside is that manually made column width adjustments may be discarded when changes are made to the fields. Disable this option to keep the column width you set manually.
▪Preserve cell formatting: If this option is enabled, the formatting you made manually in the pivot table (e.g. a different font, shading, borders) 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: The number formatting needs to be set using the Number format button (in the Value settings on the Show values as tab) so that it is permanently retained.
▪Classic pivot table format: If you are confused about the headers "Row labels" and "Column labels" in the pivot table report and prefer to see the real names of the fields instead, enable this option for a classic appearance (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 value row is displayed only if there are at least two fields in the values area 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 select 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. To sort the elements alphabetically 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. |
You can adjust 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 preferences.
Enabling the option Banded rows/Banded columns causes alternating background colors of the rows/columns, enabling the option Row headers/Column headers emphasizes 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 for finally applying it.
▪Special values section:
| For error values show – Cells with incorrect calculations return the known error values also in the pivot table report (for example, #N/A; see the section Error values). Enable this option and enter an individual text in the right input field to display self-defined output values representing the error values. |
| For empty cells show – 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 the 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 the Refresh data button (see Updating pivot tables and changing data range) to initialize the pivot cache for the pivot table with the source data. For external source data, if they no longer exist, your pivot table can also not be updated. |
| 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 (see Updating pivot tables and changing data range). |
Please note: Updating the data with the Refresh when opening the file option or with the Refresh data button will replace the pivot cache with updated source data, overwriting the previous data in your pivot table.
In 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 (Pivot table 1, Pivot table 2, etc.) and is shown as a heading in the blank pivot table report. An entry is also created in Named ranges, but it cannot be renamed there. Moreover, the assigned Name cannot be used to identify the pivot table from the GETPIVOTDATA function; therefore, 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 effectively appear in the pivot table, please read the section Working with the field list: Fields section.