The “Options” tab

All of these options will be discussed as we go through this lesson. We start with the “Options” button on the far left.

When we click on it, a drop-down menu appears. Choose “Options”. This dialog box comes with six tabs.

The first tab is “Layout and format”. Here we have the “Merge and center cells with labels” checkbox – It happens sometimes that in a “Pivot Table”, in the outer column or row, fields have the same label name. For example, “Month” or “Quarterly”. If this box remains unchecked, these cells will be merged and the label will be centered.

“Indent Row Labels” – Here you specify the indentation of row labels when the “Pivot Table” is shown in “Compact Form”.

“Display Fields in report filter area” – Determines how different fields are displayed in the “Report Filter” area. Choose “Down, then over” to add new fields 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” – Determines how many fields there are shown in a row or a column before “Excel” moves to a new row or column.

Format “For error values” – When this box is checked, you may enter a message in the adjacent box that is displayed instead of an error.

Format “For empty cells show:” – When this option is checked, you can enter a message to be displayed when this cell is empty.

“AutoFit Column Widths on update” – When this option is checked, for every update of the “Pivot Table”, “Excel” automatically adjusts the width of the columns to accommodate the widest data in this column. 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 “Pivot Table” is preserved when updates happen.
The second tab is the “Totals and filters” tab.
The first two options are clear. Check this one to give the totals for rows or columns.

The third option is “Subtotals filtered page items.”
When this is checked, the subtotals of table values which were not shown because of an applied filter was will be displayed.

The fourth option “Allow multiple filters per field”, when selected, gives subtotals and totals contained in items that were hidden by a filter.

The last option is “Use Custom Lists when sorting”. When you select this option you can use custom lists when sorting data.

 

The third tab is the “Display” tab.

The first option “Show expand/collapse buttons” – checking these the buttons allows the row or column labels to be expanded or collapsed.

The second option “Show contextual tooltips”  – when this option is selected, “Excel” will display information when the mouse points to an element in the “Pivot Table” .

The third option “Show properties in tooltips” – this option is available only when the data source properties support it.

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

The fifth option “Classic Pivot Table layout …” . Select if you prefer to work with the layout of previous versions of “Excel” where you created a “Pivot Table”.

The two following options “Show Items with no data on rows” and “Show Items with no data on rows” do what they say. When these are selected, they display the items with no values in rows or columns.

The next option “Item labels displayed when the value field contains no fields” applies only when the “Pivot Table” 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 Z to A” are obvious.

 

The fourth tab is “Printing” .

In this tab, all options are self explanatory

 

The fifth tab is “Data”.

The first option “Save Source Data with File”

is only relevant when the source data is based on external data. For example, a query from an “Access” database.

The second option “Enable show Details”. Check it if you want to show details in a “Pivot Table” report.

The third option is clear.

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

 

The sixth tab is “Alt text”

It is possible to give a description in “Excel 2010” when we add “Tables”, “PivotTables”, images, shapes, and more.
With this alternative text, people can read more information on screen about the content of images, tables, pivot tables etc.

 

To check whether certain accessibility problems arise in your file, navigate to the “Backstage” view.
You choose “Info”, click the “Check for problems” button and choose “Check accessibility”.
This opens the “Access Control” panel in which potential problems are displayed.
Select the problem, and “Excel” will automatically detect the problem in the “Workbook”.

 

Featured Courses

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