Excel compare list:Pivot table properties and options

Excel compare list: YOUR OBJECTIVE

When working with pivot tables, you can make a number of settings that affect views, printing and calculations. One such setting, for example, can hide the field captions of the row and column labels as well as the filter drop-down icons.

This learning module “Excel compare list” shows you how to make settings that control various pivot-table options.

Excel compare list: HOW DOES IT WORK?

To access the pivot-table options, click the button in the PivotTable group, on the Options tab, under PivotTable Tools. Using the options that are available on the PivotTable Options dialog box’s tabs, you can make settings that affect, among other things, pivot-table displays, printing and calculating. The following sections describe how to use options on each of each of the tabs.

Excel compare list:Layout & Format tab 

Excel compare list:Merge and center cells with labels: Using this option, you can often improve the appearance of your pivot table, i.e., make it easier to read and understand. Activate this option to center-align labels within their cells. If a cell with a label is adjacent to empty cells, the cell with the label and those adjacent empty cells are merged and the label centered within the merged cell.

Excel compare list:When in compact form indent row labels: To indent subitems in the groupings on the row labels, specify an indent level between 0 and 127.

Excel compare list:Display fields in Report Filter area: If you are working with several report filters, you can use this option to specify how the report filter fields will appear in your pivot table. To display fields in the Report Filter area one above the other, select the Down, Then Over option. To display fields in the report filter area side by side, select the Over, Then Down option. Shown below is Down, then over, followed by Over, then down.

Report filter fields per column or Report filter fields per row: In conjunction with the Display fields in report filter area setting, this option specifies how many fields to display above one another before beginning another column, or how many fields to display in a row before beginning another row.

For error values show: In a pivot table report cell, an error condition can occur when, for example, a text entry somehow gets into a numeric cell in the source table. In the default case, Excel’s standard error message (#VALUE!) will appear in the pivot table report area. Here, however, you can enter text of your own (e.g., Check sales table) that would appear instead of the standard error message. If you want to do this, remember to activate the option.

For empty cells show: If a pivot-table cell has no underlying data, it will by default simply remain empty. You can, however, specify here a text of your own that will display in an empty cell. Remember to activate the option.

Autofit column widths on update: Select this option to cause Excel to adjust the widths of the pivot-table columns automatically to fit the widest entry.

Preserve cell formatting on update: Activate this option to save the current report layout and PivotTable style each time you close the Excel file. Then, when you reopen the file, these settings will be restored. Should this option be disabled, the next time you open the workbook, the default layout and style will be used.

Excel compare list: Totals & Filters tab 

  1. Show grand totals for rows: Activating or deactivating this option lets you enable or disable the display of the Grand Total (last) column.
  2. Show grand totals for columns: Activating or deactivation this option lets you enable or disable the display of a Grand Total row at the bottom the pivot table.
  3. Use Custom Lists when sorting: Select this option to be able to use Custom Lists to sort the pivot table 

Excel compare list:CAUTION

Some options, not explained here, are only available when using OLAP data sources.

Excel compare list: Display tab 

Show expand/collapse buttons: This option is selected by default. Uncheck this checkbox to hide the plus and minus icons for expanding and collapsing row or column labels in the pivot table.

Show contextual tooltips: Select this option to allow tooltips to appear when you hover your mouse pointer over a cell in the values, row or column areas.
Display field captions and filter drop downs: If you want to hide the row or column labels and the drop-down arrows for filtering, deactivate this option. See next illustration:
Classic PivotTable layout: Select this option to enable the display of individual pivot-table areas in a way with which you may be familair from previous Excel versions. The classic layout allows you to drag fields from one area to another or from the PivotTable Field List directly into a pivot-table area. Classic layout shown below:

Sort A to Z: Select this option to sort the fields in the PivotTable Field List in ascending alphabetical order. Sort in data source order: Select this option to display the fields in the PivotTable Field List in the same order as they appear in the original data source. This option overrides the Sort A to Z option.

Excel compare list: CAUTION

Some options, not explained here, are only available when using OLAP data sources.

Excel compare list: Printing tab 

Print expand/collapse buttons when displayed on PivotTable: If you want to see the expand/collapse buttons on a pivot-table report printout, activate this option. If the Display field captions and filter drop downs option on the Display tab is deactivated, this option will not be available.

Repeat row labels on each printed page: Select this option to repeat row labels on all pages of the printed pivot-table report. Set print titles: Select this option to repeat row and column field headers and column labels on all pages of the printed pivot-table report.

Excel compare list: Data tab 

Save source data with file: This option concerns only external data sources. When activated, it causes that source data to be saved together with the Excel workbook.

Enable show details: Activate or deactivate this option to allow or disallow drilling down on summary data cells (values) in the pivot-table report to display the underlying source data. Refresh data when opening the file: Select this option, if you want the pivot table to be automatically refreshed each time you open the Excel workbook.

Number of items to retain per field: This option is used to specify the number of items per field to be retained with the workbook. Enable cell editing in the values area: Selecting this option allows you to edit the cells directly in the values area, when you perform a what-if analysis.

Excel compare list: Alt Text tab

Here you can enter an alternative title and description to make it easier for people with impaired vision to work with the pivot table.

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