The Ribbon “Options”

All these buttons in “Options” tab will be discussed when we use these in a lsson.
Only for the button in the “Options” tab I will elaborate.

When we click the drop-down menu for “Pivot table Options”, a dialog box appears with five tabs.
The first tab is “Layout and format”.
“Merge and center cells with labels”:
Some fields in the outer column or row of our “PivotTable” may have the same label name. For example, “Month” or “Quarter”.
Now if this box is checked, these cells will be merged and the labels are centered.

Indent Row Labels …
Here you specify the indentation of row labels when the “PivotTable” is shown in Compact View.

Fields in report filter area:
It determines how fields are displayed in the Report Filter area. Choose “Down, then Over” to add new field to an existing column before you switch to a new column. Select “Over, then down” to add new fields to an existing row before you switch to a new row.

“Report filter fields per column”:
It determines the number of fields in a row or a column, before Excel moves to a new row or column.

To display error values:
When this is checked, you can enter a message in the adjacent box which is displayed instead of an error.

To display empty cells:
When this option is checked, here also you can enter a message to display when this cell is empty. (See lesson 57)

AutoFit Column Widths on update:
When this option is checked, width of the columns is automatically adjusted to the broadest data from this column on every update of Excel PivotTable . If this is not checked, the original width of the column will be retained.

Preserve cell formatting on update:
When this option is selected, the format of the cell and the design of the “PivotTable” is preserved when updating.
The second tab is the “Totals and filters”tab.
The first two options seem to be clear.When this option is checked this one gives the totals for rows or columns.

The third option is “Subtotals of filtered entries”.
When this is checked, includes the subtotals of table values that were not shown because of filter.

The fourth option “Allow multiple filters per field”. Select this when you want subtotals and totals to also include items that were hidden by a filter.

The last option “Use custom Lists when sorting”. Select when you want to sort data in custom lists.
The third tab is the “Display” tab.
The first option “Show expand/collapse buttons” seems to be clear. These are the + and – buttons that you find at the end of row or column labels.

The second option “Context-sensitive tooltips” also seems to be clear. For those who do not know, when this option is selected, Excel will display information when you move the mouse pointer over an element in the “PivotTable”.

The third option “Show properties in tooltips” seems to be clear. But remember, this option is available only when the properties of the data source supports this.

The fourth option is “Display Field captions and filter drop-down lists”. When this is selected the field captions and filter drop-down lists (these are the downward-pointing arrows) appear. When this option is unchecked, both are hidden.

The fifth option “Classic PivotTable layout”, select this if you prefer to work with the layout of previous versions of Excel when you create a “PivotTable”.

The two following options “Show Items with no data in rows’ and “Show items with no data in columns” These displays the items with no values in rows or columns.

The next option “Display Item labels when no fields are in the value areas” applies only when the PivotTable is created in the traditional way. You select this option when you want to display item labels when no field is specified in the value field.

The next two options, “Sort A to Z” and “Sort in data source order” are obvious.
The fourth tab, “Printing”
In this tab, all options are clear.
The fifth tab “Data”
The first option “Source Data in File” is only relevant when the source data is based on an external database. For example, a query from an “Access” database.

The second option “Enable Show Details On” shows details in a PivotTable report.

The third option is clear.

The last option “Number of items to retain per field”, It is also based on pivot tables which inturn based on data from an external source. It specifies the number of items that are included in the workbook for each field.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats