📊 Mastering PivotTable Options

In our previous article, we explored how to insert PivotTables to quickly summarise and analyse large datasets. But creating a PivotTable is just the beginning.

To truly unlock the power of PivotTables, you need to explore the PivotTable Options, a set of customisation tools that control formatting, refresh behaviour, display settings, and more.

🎯Why This Matters

While default PivotTables may work for quick analysis, professional reports demand precision, clarity, and performance. PivotTable Options help you:

• Keep formatting intact during updates

• Auto-refresh data on file open

• Replace errors and blanks with cleaner values

• Improve file size and performance

• Create user-friendly, export-ready dashboards

By fine-tuning these options, your PivotTables go from basic to brilliant—without a single formula.

Step by Step Procedure to display the pivot table options window are :

• Right-click anywhere inside the PivotTable.

• Select "PivotTable Options..." from the context menu.

• A dialogue box appears with several tabs:

• Adjust settings as needed. Click OK to apply.

A typical dialogue box appears for the setting with 6 layout tabs

🧱Layout and Format

The Layout & Format tab allows you to control how your PivotTable looks and behaves when updated. This tab is especially useful for improving the readability, consistency, and professional appearance of your reports.

The Layout & Format tab controls how your PivotTable looks and how it behaves during refreshes and data updates. These settings are especially helpful when you want your PivotTables to look polished, stay consistent, and behave reliably across refreshes or user edits.

1. Merge and centre cells with labels—This option merges label cells in the row area, resulting in a cleaner and more compact layout. It’s useful when you want your PivotTable to look tidy in printed reports, but you may want to disable it if you need to export or flatten the data into rows for further analysis.

2. Repeat item labels—When this is turned on, item labels in row fields appear only once instead of being repeated in each row. This is particularly helpful when exporting to PDF or Excel for readability or when the table is used for data extraction.

3. Autofit column widths on update – Excel normally resizes columns every time you refresh the PivotTable. If you have custom column widths, disable this option to prevent Excel from overwriting them.

4. Preserve cell formatting on update—This setting retains your manual formatting, such as fonts, number formats, colours, borders, and alignments, even after the PivotTable is refreshed. It’s essential for professional reports and dashboards where appearance matters.

5. For empty cells show : This option lets you display something like “0,” “—,” or “N/A” instead of leaving cells blank when no data exists. This improves clarity and avoids confusion for report viewers.

6. Show error values as – If your data has formulas that might result in errors (like #DIV/0!), you can replace those errors with a more user-friendly message like “N/A” or “Check”.

💡 Pro Tips:

• Use "Repeat item labels" when creating flat tables for further analysis or export.

• Turn off "Autofit column widths" if your formatting keeps getting overwritten.

• Use "Preserve cell formatting" if you’ve applied bold headers, borders, or color coding.

🧱Printing

The Printing tab in PivotTable Options helps you control how your PivotTable appears when printed. These settings ensure that your printed reports are readable, complete, and professional-looking, especially important when distributing hard copies or PDFs to clients, managers, or stakeholders.

1. Print Expand/Collapse buttons when displayed – If your PivotTable includes groupings with expand/collapse buttons (+ / -), enabling this option will print those buttons as well. This makes it visually clear which data is grouped or can be expanded. Disable this if you want a cleaner printout without UI elements.

2. Repeat row labels on each printed page – This option ensures that the row labels (like Region, Product, or Category) are repeated at the top of each new printed page. It’s extremely helpful when your PivotTable spans multiple pages, making sure each page remains understandable without scrolling back.

3. Set print titles – While not directly controlled from this tab, enabling row label repetition works similarly to Excel's "Print Titles" feature. It enhances navigation and readability in printed reports.

💡 When to Use:

• For client-facing reports, ensure row labels are repeated on each page for clarity.

• Disable printing of expand/collapse buttons for cleaner and more professional layouts.

• Use in combination with Page Layout View to preview how your PivotTable will appear before printing.

🔢Totals and Filter

The Totals & Filters tab in PivotTable Options gives you control over how totals are calculated and displayed, and how filters behave within the PivotTable. These settings are essential for ensuring your data summaries are both meaningful and user-friendly—especially in multi-level reports or when sharing files with others.

1. Show grand totals for rows – Enabling this displays a total row at the end of each outer row grouping. It’s useful when you want a quick summary across all categories in the row area.

2. Show grand totals for columns – Similar to the above, this option adds a total column to the right of the PivotTable that sums all column groupings. It provides a quick overall view of totals across all columns.

3. Show subtotal at top of group—By default, Subtotals appear below each group, but enabling this setting places them at the top. This can improve visibility and is preferred in some regional or reporting formats.

4. Allow multiple filters per field – This powerful option lets users apply multiple selections within a single field (e.g., filter for both “North” and “East” regions simultaneously). It enhances filtering flexibility, especially for interactive dashboards or exploratory analysis.

5. Use custom lists when sorting – Custom lists like months (“Jan, Feb, Mar…”) or priority levels (“High, Medium, Low”) can be preserved while sorting fields in the PivotTable. This ensures that items appear in a meaningful sequence rather than alphabetically.

6. Sort data in the order of the custom list – When used with the above setting, this option enforces the sort order defined in your custom list instead of default sorting.

💡 When to Use:

• Enable grand totals for quick roll-up summaries at a glance.

• Position subtotals at the top for readability or regional formatting preferences.

• Allow multiple filters when your audience needs more flexible drilldowns.

• Use custom sorting for logical, business-relevant item sequences.

📊 Data

The Data tab in PivotTable Options controls how your PivotTable manages its underlying data, refreshes, and caching behavior. These settings are crucial for ensuring data accuracy, improving performance, and managing file size—especially when working with large or frequently updated datasets.

1. Refresh data when opening the file – Enabling this option ensures your PivotTable automatically updates its data every time the workbook is opened. This is ideal for dashboards or reports linked to dynamic data sources that change regularly.

2. Number of items to retain per field – This controls how many old (deleted or changed) items Excel remembers in the PivotCache. Setting this to “None” reduces file size and prevents outdated items from showing up in filter drop-downs, which keeps your data cleaner and more accurate.

3. Save source data with file – If enabled, the source data used to create the PivotTable is stored within the workbook. This allows the PivotTable to work offline without needing to reconnect to the original data source. Disabling this can reduce file size but requires the source data to be available for refreshing.

4. Enable show details—This option allows users to double-click a value cell in the PivotTable to drill down and see the underlying source data rows. It is great for analysis but can be disabled to prevent data exposure or accidental changes.

5. Enable background refresh (for external data connections) – When using data connections (like from SQL Server or OLAP cubes), this option lets Excel refresh the PivotTable data in the background without freezing your interface. Useful for large or slow data sources.

💡 When to Use:

• Enable refresh on open for real-time reporting and dashboards.

• Set number of items to retain to “None” to keep filters clean and file size minimal.

• Disable save source data to reduce file size if source data is large and always accessible.

• Disable enable show details to protect sensitive data or control user access.

• Use background refresh when working with large external databases to improve usability.

👁️ Display

The Display tab in PivotTable Options lets you customize how your PivotTable appears on-screen, including interactive features and visual cues. These settings improve usability, navigation, and readability—helping users interact more efficiently with the data.

1. Show expand/collapse buttons – Displays plus (+) and minus (–) buttons next to grouped items, allowing users to expand or collapse detail levels. Enable this for interactive reports where users explore data hierarchies; disable for simplified views or printed reports.

2. Show contextual tooltips – Shows helpful tooltips when hovering over fields or values, providing quick insights or explanations without cluttering the layout. Useful for training users or complex reports.

3. Show properties in tooltips – Displays additional metadata or field properties in the tooltips, giving users more detailed context about the data source or calculations.

4. Show insert page breaks – Automatically inserts page breaks in the PivotTable when printing or in Page Layout view. Helps ensure clean and readable printed reports.

5. Show items with no data on rows/columns – Displays items even if there’s no data available for them, maintaining consistent row and column layouts. This is helpful for completeness in reports and dashboards, ensuring all categories or time periods appear even if empty.

6. Display field captions and filter drop-downs—This controls the visibility of field headers and filter arrows at the top of PivotTable fields, enabling easy filtering and sorting. It is usually enabled for user interactivity, but can be disabled for cleaner reports.

💡 When to Use:

• Enable expand/collapse buttons to make reports interactive and user-friendly.

• Use contextual tooltips and properties in tooltips for complex data to assist end-users.

• Show items with no data to maintain report structure and avoid confusion.

• Disable filter drop downs and field captions for finalized, static reports.

📝 Tab: Alt Text

The Alt Text tab allows you to add descriptive alternative text to your PivotTable. Alt text is essential for making your reports accessible to users who rely on screen readers or assistive technologies. It also improves document clarity and helps with compliance to accessibility standards.

1. Title—Enter a concise, descriptive title for the PivotTable. This should summarize what it represents, helping users quickly understand its purpose.

2. Description – Provide a more detailed explanation of the PivotTable’s contents, such as what data it analyzes, key insights, or how it should be interpreted. This helps users who cannot visually see the table to get context.

💡 Why Alt Text Matters:

• Accessibility: Makes your reports usable for people with visual impairments.

• Compliance: Meets legal and organisational standards for accessible content.

• Clarity: Helps all users understand the purpose and content of your PivotTable without ambiguity.

• Professionalism: Demonstrates attention to detail and inclusive design.

Conclusion ✅

Mastering PivotTable Options is the key to transforming a basic data summary into a powerful, professional report tailored to your needs. By customising settings across tabs like Layout & Format, Data, Display, Printing, Totals & Filters, and Alt Text, you gain full control over your PivotTable’s appearance, behaviour, and accessibility. These options not only enhance clarity and usability but also boost efficiency and ensure your reports meet both business and compliance standards.

Take the time to explore and adjust these settings—your PivotTables will thank you with improved performance, readability, and impact.