How to Change Pivot Table Settings in Excel

PivotTables are one of Excel’s most powerful features, allowing you to summarize huge datasets in seconds. But the default setup isn’t always what you need. This guide will walk you through how to change PivotTable settings to fully customize your reports, control how your data is displayed and calculated, and make your analysis much clearer.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First Things First: Where to Find PivotTable Options

Almost all of the core settings for your PivotTable live in a single convenient dialog box: PivotTable Options. Getting there is easy. Simply right-click anywhere inside your PivotTable and select "PivotTable Options..." from the menu.

Alternatively, you can click on your PivotTable to bring up the PivotTable Analyze tab in the Excel ribbon. On the far left, you'll see a button for "Options." Clicking this will also open the PivotTable Options window.

This little window is command central for tweaking your report’s behavior. Let's break down the most useful settings tab by tab.

Customizing Layout and Formatting

The "Layout & Format" tab in PivotTable Options is where you control the look and feel of your table. Spending a minute here can dramatically improve the readability of your reports.

Handling Blank Cells and Errors

By default, if a category in your data has no value, the PivotTable shows a blank cell. This can sometimes be confusing. You can change this to display something more specific.

• For error values show: If your calculation results in an error (like #DIV/0!), you can check this box and enter a default value like "N/A" or "0" to make the report cleaner.
• For empty cells show: This is incredibly useful. Instead of leaving a blank space for combinations with no data, you can check this box and type in a "0" or "No Data". This makes it clear that the value is zero, not just missing.

Imagine you have a sales report by region and product. If you didn't sell any 'Product A' in the 'West' region, the cell would be blank. By setting empty cells to show "0", your team immediately knows the sales for that item were zero, removing any ambiguity.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Managing Column Widths and Formatting

Have you ever spent time perfectly adjusting your column widths, only to have them all snap back to a default size the moment you refresh the data? This is a common frustration, but it’s an easy fix.

• Autofit column widths on update: This setting is checked by default. Uncheck this box to stop Excel from resizing your columns every time you refresh or change the PivotTable. Your custom widths will now stick.
• Preserve cell formatting on update: Just like with column widths, Excel sometimes resets custom cell formatting (like font colors or background fills) on refresh. Checking this box tells the PivotTable to keep your manual formatting changes, which is great for highlighting specific results.

Changing the Report Layout

Beyond the Options dialog box, the Design tab (which appears when you click on your PivotTable) offers powerful layout choices. The "Report Layout" button is your key to transforming the structure of your table.

• Compact Form (Default): This layout saves space by putting multiple row fields into a single column, using indentation to show hierarchy. It's fine for simple tables, but it can get messy with lots of data.
• Outline Form: Displays each row field in its own separate column. This is a big improvement for readability and makes the data structure clearer. Subtotals appear at the top of each group.
• Tabular Form: This is a favorite for many analysts. Like Outline Form, it puts each field in its own column. The key difference is that subtotals are shown at the bottom of each group, similar to a traditional report or spreadsheet table. It also makes your data easier to copy and paste for other uses.

You can also choose to "Repeat All Item Labels" from this menu. This option fills in the blank cells in outer row fields, making each row a complete record. This is a must if you ever plan to use the PivotTable data as a source for another table or chart.

Controlling Totals and Filters

Fine-tuning how your PivotTable calculates summaries and handles filtering is essential for creating insightful reports. These settings are found in the "Totals & Filters" tab within the PivotTable Options dialog box.

Showing or Hiding Grand Totals and Subtotals

• Grand Totals: You have separate checkboxes for "Show grand totals for rows" and "Show grand totals for columns." Unchecking these removes the final sum at the bottom or far right of your table. This is useful when the grand total isn't relevant to your analysis, such as when you’re looking at averages or percentages.
• Subtotals: While you can turn all subtotals off in the "Design" tab, you can customize them at the field level. Right-click any item in a field (like "North Region" in a 'Region' field) and select "Field Settings...". In the "Subtotals & Filters" tab here, you can choose "None" to hide subtotals just for that specific field, while leaving them on for others.

For example, you might want to see subtotals for each Sales Rep but not for the Product Category they sold, giving you a cleaner, more focused report.

Useful Filtering Options

The "Totals & Filters" tab also gives you more control over how filters behave.

• Allow multiple filters per field: This is checked by default and is what allows you to select multiple items from a filter drop-down (e.g., showing data for both the 'East' and 'West' regions). In some rare cases, you might want to restrict users to a single selection, and you could uncheck this.
• Use Custom Lists when sorting: Excel has built-in lists (like Jan, Feb, Mar...) and lets you create your own sorting lists (like High, Medium, Low). Checking this option ensures your PivotTable respects these custom sort orders.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Refreshing and Managing Your Source Data

A PivotTable is only as good as the data behind it. The "Data" tab in PivotTable Options helps you manage the link to your source data.

Refreshing Data Automatically

Typically, you must manually refresh a PivotTable (right-click > Refresh) to see updates from your source data. However, you can make this happen automatically.

In PivotTable Options > Data, check the box for "Refresh data when opening the file." This is a fantastic time-saver. Every time someone opens the workbook, the PivotTable will automatically pull in the latest information, ensuring everyone is looking at the most current numbers.

Changing the Data Source

What if you add new rows or columns to your source data? A simple "Refresh" won't pick them up because they are outside the original range. To include them, you need to change the data source.

1. Click on your PivotTable.
2. Go to the PivotTable Analyze tab.
3. Click "Change Data Source."
4. In the dialog that appears, simply re-select the entire range of your data, including the new rows or columns, and click OK.

Pro Tip: Convert your source data into an official Excel Table (select your data > Insert > Table) before creating your PivotTable. Tables automatically expand to include new rows and columns, so when you add data, a simple "Refresh" is all you'll ever need. No more manually changing the data source!

Customizing Calculations with Value Field Settings

Perhaps the most powerful customizations happen not in the main options menu, but in the Value Field Settings. This is where you tell Excel how to calculate your data.

To access this, right-click on any number inside the "Values" area of your PivotTable and select "Value Field Settings..." Alternatively, you can click the dropdown on the field in the 'Values' box of the PivotTable Fields pane and choose it there.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Change the Summary Calculation

By default, Excel sums numbers and counts text. The "Summarize Values By" tab lets you change this. Instead of a Sum of sales, you can instantly switch to:

• Count: How many transactions occurred?
• Average: What was the average sale price?
• Max / Min: What was the highest or lowest sale amount?
• StdDev: Analyze the variation in your data.

Show Values as a Percentage or Variance

The real magic lies in the "Show Values As" tab. This allows you to reframe your data to answer more complex questions without writing a single formula.

In the "Show Values As" dropdown, you'll find incredible options like:

• % of Grand Total: Shows each value as a percentage of the entire table's total. Perfect for seeing which region contributes the most to overall sales.
• % of Column Total: Shows each value as a percentage of its column total. Great for comparing the product mix within each region.
• % of Parent Row Total: Excellent for multi-level reports. For example, if you have Sales Reps nested under Regions, this can show what percentage of a region's sales came from each rep.
• Difference From / % Difference From: Allows you to compare one period to another. You could set this up to automatically show the month-over-month growth or decline in traffic.
• Running Total In: Creates a cumulative total, perfect for tracking progress towards a goal over time.

You can even drag the same field (e.g., 'Revenue') into the Values area multiple times and apply different settings to each. This lets you show the Sum of Revenue in one column and the % of Grand Total right next to it in another.

Final Thoughts

Mastering PivotTable settings transforms them from simple summary tools into dynamic and highly customized analytical dashboards right inside Excel. By learning how to control formatting, calculations, and data refreshes, you can build reports that are not only powerful but also perfectly clear for you and your team.

While perfecting pivot tables is a huge step up from manual spreadsheet work, it often still relies on downloading CSVs and wrangling them every week. To eliminate that recurring task, we built tools like Graphed. It connects directly to your data sources like Google Analytics, Shopify, and Salesforce, allowing you to create live, real-time dashboards by simply describing what you want to see - no more "Refresh," no more "Change Data Source," just up-to-date answers.