Pivot table options

Experienced users are eager to find out where are the Pivot table options, in order to make adjustments and adapt the software to their needs! Each Pivot table has many options that affect its work. With them we can change the look and way of displaying the values in the report, the method of calculating subtotals, use of filters, properties of connecting with external data sources, printing options…

The Pivot table options are accessed using the Options menu located on the Analyze Toolbar. It consists of three options within a menu: the first is the Pivot table options, the other serves to generate worksheets based on the filters, and the third one serves to automatically generate the GETPIVOTDATA function. Generating a worksheet based on a filter (Show Report Filter Pages) is used if we have a defined filter (reporting dimension in the Filters field). After starting, a new worksheet is created for each individual value of the filter. Generate GetPivotData is an option that can be selected or not. In case it is selected we can use it to automatically generate a formula that indicates a value in the Pivot table. Just click on the cell where you want the formula to be found, enter the equality sign (“=”) to start the formula, and when you click on the position in the Pivot table, the GETPIVOTDATA function will automatically be generated by taking the result from the Pivot table and saving it in the cell.

By clicking Options within Analyze ribbon we’ll open the PivotTable Options dialog box. It consists of numerous options grouped within 6 tabs. Follows the description of most commonly used features.


The Layout & Format tab contains the options that are used to set up the work environment and display the cells. Autofit column widths on update and Preserve cell formatting on update are often checked. If the first option is checked, each time you add a new column to the table, the width of all columns will be automatically adjusted. If we want the column width to remain unchanged, this option should be unchecked. The second option is used to keep the existing formatting after each change of cell. The Totals & Filters tab contains options for adjusting the totlas and filters. In addition to the usual, intuitively clear, it also has the option Allow multiple filters per field. If we check this option, we will allow multiple filtering. The Display tab contains options related to displaying data in the Pivot table. The Print tab contains options for printing Pivot tables, and the Data tab contains the options essential for working with the data. Here is one useful option called Save source data with file. If it is checked, it takes the data in the Pivot table to be stored together with the workbook. This can be useful if, for example, we create an SQL query based on which we retrieve business enterprise data, so we want to keep them so that we can analyze them at home or elsewhere where we do not have an active connection to the database. The last Alt Text tab contains only two options, and we can use them to describe the Pivot table.

The Actions group of options, on the Analyze ribbon, contains additional options to work with the Pivot table. First, the Clear group of options can be used to remove dimensions from the Pivot table (Clear All) or to remove the currently selected filters (Clear Filters). A Select  group is used to select individual parts or the entire Pivot table. Selecting it is necessary to delete the Pivot table, if necessary. The Move Pivot table option can be used to move the Pivot table to another position in a worksheet or to a new worksheet.