Pivot Table Options

Basic Description

Use the PivotTable Options dialog box to control various settings for a PivotTable report.Name: Displays the PivotTable name. To change the name, click the text in the box and edit the name.

Layout & Format
Layout section

Merge and center cells with labels: Select to merge cells for outer row and column items so that you can center the items horizontally and vertically. Clear to left-justify items in outer row and column fields at the top of the item group.

When in compact form indent row labels: To indent rows in the row labels area when the PivotTable report is in compact format, select an indentation level of 0 to 127.

Display fields in report filter area: Select Down, Then Over to first display fields in the report filter area from the top to the bottom, as fields are added to it, before taking up another column. Select Over, Then Down to first display fields in the report filter area from left to right, as fields are added to it, before taking up another row.

Report filter fields per column: Type or select the number of fields to display before taking up another column or row based on the setting of Display fields in report filter area.

 

Format section

For error values show: Select this check box, and then type text, such as “Invalid”, that you want to display in the cell instead of an error message. Clear this check box to display the error message.
For empty cells show: Select this check box, and then type text, such as “Empty”, that you want to display in the cell instead of a blank cell.
Autofit column widths on update: Select to adjust the PivotTable columns to automatically fit to the size of the widest text or number value. Clear to keep the current PivotTable column width.
Preserve cell formatting on update: Select to save the PivotTable report layout and format so that it is used each time that you perform an operation on the PivotTable. Clear to not save the PivotTable report layout and format and resort to the default layout and format each time that you perform an operation on the PivotTable.

 

 

Totals & Filters

Grand Totals section

Show grand totals for rows: Select or clear to display or hide the Grand Total column next to the last column.
Show grand totals for columns: Select or clear to display or hide the Grand Total row at the bottom of the PivotTable report.

 

Filters section

Subtotal filtered page items: Select or clear to include or exclude report-filtered items in subtotals.

Note: The OLAP data source must support the MDX expression subselect syntax.

Mark totals with *: Select or clear to display or hide an asterisk next to totals. The asterisk indicates that the visible values that are displayed and that are used when Excel calculates the total are not the only values that are used in the calculation.

Note: This option is only available if the OLAP data source does not support the MDX expression subselect syntax.

Allow multiple filters per field: Select to include all values, including those hidden by filtering, when Microsoft Office Excel calculates subtotals and the grand total. Clear to include only displayed items when Excel calculates subtotals and the grand total.

Note: This setting is only available for a non-OLAP data source.

 

Sorting section

Use Custom Lists when sorting: Select or clear to enable or disable the use of custom lists when Excel sorts lists. Clearing this check box may also improve performance when you sort large amounts of data.

 

Display

Display section

Show expand/collapse buttons: Select to display the plus or minus buttons that you use to expand or collapse row or column labels. Clear to hide the plus or minus buttons that you use to expand or collapse row or column labels. You might want to hide the plus or minus buttons when you print a PivotTable report or when you display a PivotTable report just for viewing.

Show contextual tooltips: Select to display tooltips that show value, row, or column information for a field or data value. Clear to hide tooltips that show value, row, or column information for a field or data value.

Show properties in tooltips:  Select or clear to display or hide tooltips that show property information for an item.

Note: This setting is only available for an OLAP data source.

Display field captions and filter drop downs: Select or clear to display or hide PivotTable captions at the top of the PivotTable report and filter drop-down arrows on column and row labels.

Classic PivotTable layout: Select or clear to enable or disable dragging fields on and off of the PivotTable report.

Note: This option does not enable compatibility with previous versions of PivotTable reports. For more information, see Working with different PivotTable formats in Office Excel.

Show items with no data on rows: Select or clear to display or hide row items that have no values.

Note: This setting is only available for an OLAP data source.

Show items with no data on columns: Select or clear to display or hide column items that have no values.

Note: This setting is only available for an OLAP data source.

Display item labels when no fields are in the values area: Select or clear to display or hide item labels when there are no fields in the value area.

Note: This check box only applies to PivotTable reports created prior to Office Excel 2007.

Show calculated members from OLAP server:  Select or clear to display or hide calculated members in a dimension. This check box does not affect calculated measures.

Note: This setting is only available for an OLAP data source.

 

Field List section

The following two options are mutually exclusive.

Sort A to Z: Select to sort the fields in the PivotTable field list in ascending alphabetical sort order.

Note: This setting is not available for an OLAP data source.

Sort in data source order: Select to sort the fields in the PivotTable field list in the order that is specified by the external data source.

Note: This setting is not available for an OLAP data source.

 

Printing

Print expand/collapse buttons when displayed on PivotTable: Select or clear to display or hide expand and collapse buttons when you print a PivotTable report. This check box is not available if the Show drill buttons check box is cleared in the Display tab of this dialog box.

Repeat row labels on each printed page: Select or clear to repeat the current item labels of the row label area on each page of a printed PivotTable report.

Set print titles: Select or clear to enable or disable the repeating of row and column field headers and column item labels on each printed page of a PivotTable report.

Note: To actually print the labels, you must still enter values in the Rows to repeat at the top or Columns to repeat at left boxes under the Print titles section in the Sheet tab of the Page Setup dialog box (On the Page Layout tab, in the Page Setup group, click Print Titles.

 

Data

PivotTable Data section

Save source data with file: Select or clear to save or not save the data from the external data source with the workbook.

Note: This setting is not available for an OLAP data source.

Enable expand to detail: Select or clear to enable drilling down to detail data from the data source, and then displaying the data on a new worksheet.

Note: This setting is not available for an OLAP data source.

Refresh data when opening the file: Select or clear to refresh or not refresh the data when you open the Excel workbook that contains this PivotTable report.

Note: This setting is not available for an OLAP data source.

 

Retain items deleted from the data source section

Number of items to return per field: To specify the number of items for each field to temporarily cache with the workbook, select one of the following:

  • Automatic     The default number of unique items for each field.
  • None     No unique items for each field.
  • Max     The maximum number of unique items for each field. You can specify up to 1,048,576 items.

Note: This setting is not available for an OLAP data source.

 

 

Manually Hide or Show Subtotals

To manually hide subtotals for a field:

Double-click the field button, to open the PivotTable field dialog box.

For Subtotals, select ‘None’

Click OK

To manually show subtotals for a field:

Double-click the field button, to open the PivotTable field dialog box.

For Subtotals, select ‘Custom’

Select one of the functions from the list, e.g. ‘Average‘

Click OK

 

Quickly Remove a Pivot Field

After you create a pivot table, you might want to remove a field from the layout. You don’t need to go to the field list, find that field and remove its check mark, nor do you need to drag the pivot field out of the Row Labels area in the field list.

To quickly remove a pivot field from the layout:

Right-click on an item in the pivot field. In this example, a cell in the Product field was right-clicked.

Click Remove [field name]

 

Add All Remaining Fields to Layout

In the Pivot Table Field List, you can check a field name to add it to the pivot table layout. You have to do these one at a time though — there isn’t a “Select All” checkbox.

 

Show Items with No Data

When you create a Pivot Table, it only shows the items for which there is data. In the pivot table shown below, not all colours were sold to each customer. You can change a pivot table setting, to see all the items for each customer, even the items with no data.

Show all the data in Excel 2007 / 2010

Make the following change for each field in which you want to see all the data:

Right-click an item in the pivot table field, and click Field Settings

In the Field Settings dialog box, click the Layout & Print tab.

Check the ‘Show items with no data’ check box.

Click OK

 

Missing Data in Pivot Table

To see items with no data in a pivot table, you can change the pivot table settings, as described in the section above.

However, that setting only displays items that are included in the source data at least once. For example, if you recently started selling a new product, but have no sales yet, it won’t appear in your pivot table if you turn on the “Show Items With No Data” setting.

To show missing data, such as new products, you can add one or more dummy records to the pivot table, to force the items to appear.

For example, to include a new product — Paper — in the pivot table, even if it has not yet been sold:

In the source data, add a record with Paper as the product, and 0 as the quantity

Refresh the pivot table, to update it with the new data

Right-click a cell in the Product field, and click Field Settings.

On the Layout & Print tab, add a check mark in the ‘Show items with no data’ box.

Click OK

 

Show Top Items Only

Instead of showing all the items in a field, you can restrict the Pivot Table to show only the top (or bottom) items.

Double-click the field button, to open the PivotTable field dialog box.

Click the Advanced button

Under ‘Top 10 AutoShow’, select On.

For ‘Show’, select Top or Bottom

Click the Scroll buttons, or type, to enter the number of items to show.

Click OK, click OK

 

Include New Items in Manual Filter

If you click on the arrow in a pivot table heading, you can filter the field’s items, by using the check boxes. In the screen shot below, only two of the technician names have been selected, and the others will be hidden.

However, if you add new records in the source data, or update the existing records, new names might be added to the source data.

Then, if you refresh the pivot table, those new names can appear in the filtered pivot table, even though they were not originally selected. Here, Smith appears in the pivot table, after that name was added to the source data.

To prevent new items from appearing after a manual filter has been applied, you can change a setting for the pivot field:

Right-click one of the items in the pivot field, and click Field Settings

On the Subtotals and Filters tab, in the Filter section, remove the check mark for ‘Include New Items in Manual Filter’

Click OK

NOTE: If any new items were included before you change the setting, go back to the manual filter and remove the check marks for those items.

 

Repeat Item Labels

In Excel 2010, and later versions, you change a field setting so that the item labels are repeated in each row.

This feature does not work if the pivot table is in Compact Layout, so change to Outline form or Tabular form, if necessary, before following the rest of the steps.

To change the report layout:

Select a cell in the pivot table

On the Ribbon, click the Design tab, and click Report Layout

Click Show in Outline Form, or click Show in Tablular Form

To show the item labels in every row, for all pivot fields:

Select a cell in the pivot table

On the Ribbon, click the Design tab, and click Report Layout

Click Repeat All Item Labels

To show the item labels in every row, for a specific pivot field:

Right-click an item in the pivot field

In the Field Settings dialog box, click the Layout & Print tab

Add a check mark to Repeat item labels, then click OK

 

 

3 thoughts on “Pivot Table Options

  1. Very nice blog..look my vwebsite now..

  2. Nice blog guys,now think for one moment what the information in this will do for your business or earning money

  3. nice…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

code

clearPost Comment