Excel PivotTable options let you control how a summary is arranged, calculated, filtered, refreshed, and displayed. In Microsoft 365 for Windows, select a PivotTable and use the PivotTable Analyze and Design tabs for common controls; right-click inside it and choose PivotTable Options for additional behavior and layout settings.
What PivotTable options control
A PivotTable summarizes records without changing the source data. You assign fields to Rows, Columns, Values, and Filters, then choose how Excel groups and presents the results. For example, a sales list with Date, Region, Product, and Amount fields can become a report of total sales by region and product, with dates grouped by month.
The available options fall into several practical groups:
- Structure: field placement, report layout, subtotals, and grand totals.
- Calculation: sum, count, average, and other summaries, plus ways to display values such as percentages of a total.
- Exploration: filters, sorting, grouping, slicers, and timelines.
- Maintenance and appearance: refresh behavior, number formats, blank-cell display, and whether formatting survives an update.
These controls affect the report, not the underlying records. A PivotTable is therefore useful for exploring different views of the same data, but it does not replace cleaning or correcting the source table.
Arrange fields and choose a report layout
Click inside the PivotTable to display the field list. Drag a field into Rows or Columns to define the categories, into Values to summarize it, or into Filters to add a report-level filter. In the sales example, Region in Rows and Amount in Values gives a total for each region. Adding Product to Columns breaks each regional total into product columns.
Text fields placed in Values are usually counted, while numeric fields are commonly summed. If the result is not what you intend, open the menu for that value in the field list and choose Value Field Settings. The Summarize Values By tab offers available calculations such as Sum, Count, Average, Max, and Min. The Show Values As tab can display results as a percentage of a row, column, or grand total, among other choices. The exact choices depend on the type of source and field.
On the Design tab, Report Layout offers Compact Form, Outline Form, and Tabular Form. Compact Form nests multiple row fields in one column and is space-efficient. Outline Form gives row fields separate columns with a stepped appearance. Tabular Form also separates fields into columns and is often easier to read or copy as a flat report. Use Repeat All Item Labels when you want each row to show its category labels rather than leaving repeated labels blank.
The Design tab also includes controls for subtotals, grand totals, and blank rows. Subtotals can appear above or below groups, or be turned off. Grand totals can be shown for rows, columns, both, or neither. A blank row between item labels can make groups easier to scan, though it adds space and may be inconvenient when exporting the report.
Change calculations and number formats
Value Field Settings determines what a field calculates; number formatting determines how that result looks. These are separate choices. For example, a value may correctly calculate an average but appear with too many decimal places. In Value Field Settings, choose Number Format to set a format for that value field. This is more reliable than formatting individual PivotTable cells, because a refresh or rearrangement can otherwise alter cell-level formatting.
Use Sum for additive measures such as revenue, Count when you need to count nonblank entries, and Average for a mean. Be cautious when summing quantities that should not be added across categories, such as a percentage or a snapshot balance. A percentage of total is often better represented through Show Values As than by summing the original percentages. Check the source and the meaning of the measure before selecting a calculation.
For more specialized calculations, Excel has calculated fields in some conventional PivotTables, but these are not the same as formulas in the source data. Data Model or OLAP-based PivotTables have different calculation capabilities and may not offer the same calculated-field controls. If a calculation is central to the report, confirm that the PivotTable’s source type supports the method you need.
Filter, sort, group, and drill into results
Row and column field drop-downs provide sorting and filtering. Depending on the field, you can use label, value, or date filters—for example, show products whose summed sales exceed a threshold, or dates within a selected period. A field in the Filters area adds a report filter above the PivotTable. When readers need to change filters quickly, select the PivotTable and use PivotTable Analyze > Insert Slicer. For date fields, PivotTable Analyze > Insert Timeline can provide a visual date-range control when the source and PivotTable support it.
Grouping can turn individual dates into months, quarters, or years, or combine numeric items into intervals. Select a date or number in the PivotTable and use right-click > Group. Date grouping requires usable date values; text that looks like a date, blanks, or invalid entries can prevent grouping. To make custom groups, select the relevant items before grouping. Ungroup is available from the same context menu for grouped fields.
Double-clicking a summarized value usually opens a new worksheet containing the source rows behind that result. This is called drilling through or showing details. It is a convenient way to investigate a surprising total, but the extracted sheet is a snapshot, not a live detail report; it does not automatically update when the PivotTable refreshes.
Refresh settings and source data
A PivotTable does not necessarily update the moment source cells change. Select it and choose PivotTable Analyze > Refresh, or use Data > Refresh All to refresh workbook connections and PivotTables. Refresh All is useful when a report depends on more than one source, but it may take longer than refreshing one table.
For a PivotTable built from a normal cell range, newly added rows may fall outside its original source. Converting the source to an Excel table first helps: select a source cell and choose Insert > Table, confirm the range and headers, then build the PivotTable from that table. When rows are added to the table, refresh the PivotTable to include them. For an existing PivotTable, use PivotTable Analyze > Change Data Source to inspect or change the range or table used.
To refresh a PivotTable when its workbook opens, right-click inside it, choose PivotTable Options, open the Data tab, and select Refresh data when opening the file. In the same dialog, Layout & Format settings include options such as preserving cell formatting on update and autofitting column widths on update. Preserving formatting is usually helpful for a polished report; autofitting can be helpful when labels change, but may make carefully sized columns wider after a refresh.
Format and display options
The Design tab’s PivotTable Styles provide coordinated colors and borders. Banded rows or columns, row headers, and column headers can improve readability, but styling should not substitute for clear labels and sensible number formats. The PivotTable Options dialog has additional display controls, including whether to show empty cells as a chosen value and whether to show error values as specified text. A blank display can make a report cleaner, while a label such as 0 may be more informative when zero is the intended interpretation.
Other controls can show or hide expand-and-collapse buttons and field captions. These choices change what report readers see, not the underlying summary. If readers need to expand categories, keep the buttons visible. If the report is intended as a compact, finished presentation, hiding interactive controls may reduce clutter, but it also makes exploration less obvious.
When printing, check the worksheet’s page layout and print preview rather than assuming the PivotTable will fit. A wide report with many column fields may need landscape orientation or a different scaling choice. Avoid shrinking it until the text is unreadable; filtering or choosing a more compact layout may be a better fix.
How to choose the right options
Start with the question the report should answer, then choose the smallest set of controls that makes that answer clear. For a manager comparing monthly sales by region, put Region in Rows, a date grouped by month in Columns, and Amount in Values as Sum. Apply a currency number format, enable grand totals if they help comparison, and add a slicer only if readers need to explore regions themselves.
- Choose Tabular Form when separate category columns make a report easier to scan or reuse.
- Use a percentage-of-total display when relative contribution matters more than raw totals.
- Add slicers or a timeline for interactive reports; use ordinary filters when the report needs fewer controls.
- Turn off unnecessary subtotals if they interrupt comparisons, but retain them when readers need group-level totals.
- Set number formats through Value Field Settings so the format is associated with the measure.
Excel for Mac has PivotTable tools and many corresponding settings, but ribbon labels and dialog placement can differ from Windows; look for the PivotTable Analyze and Design tabs after selecting the report. Excel for the web supports common PivotTable viewing and editing tasks, but its available controls may differ, and some advanced desktop options are not exposed there. If a needed setting is missing, open the workbook in the desktop app.
Common problems and misconceptions
A total that seems wrong may reflect the selected summary rather than a broken PivotTable. Check whether a numeric field is being counted instead of summed, whether source values are stored as text, and whether filters are hiding records. If a value appears as Count, inspect the source column for text entries or inconsistent data types, then correct the source and refresh.
A refresh that misses new records often means the PivotTable uses a fixed range that excludes the added rows. Check Change Data Source and consider using an Excel table as the source. If date grouping fails, inspect the entire date column for blanks, text, or invalid dates. Formatting disappearing after refresh can often be addressed by enabling Preserve cell formatting on update in PivotTable Options, and by applying number formats through Value Field Settings.
One common misconception is that changing a PivotTable’s layout edits the source data. It does not: moving fields, filtering, or grouping changes the summary view. Another is that a PivotTable automatically reflects every source edit. It generally needs a refresh, unless a particular connection or workflow refreshes it for you. Keep a clean, consistently typed source table and refresh after changes to get dependable results.
Frequently Asked Questions
Where are PivotTable options in Excel?
Click inside the PivotTable to show the PivotTable Analyze and Design tabs. For additional settings, right-click inside it and choose PivotTable Options.
How do I change Sum to Count or Average?
Open the value field’s menu in the PivotTable field list, choose Value Field Settings, and select the calculation under Summarize Values By. The available calculations depend on the source and field.
Why is my PivotTable not updating?
Use PivotTable Analyze > Refresh or Data > Refresh All. If new rows are missing, check whether the source range includes them; using an Excel table as the source can make future additions easier to include.
How do I show percentages instead of totals?
Open Value Field Settings and select an appropriate option under Show Values As, such as percentage of grand total. This changes how the summarized values are displayed, not the source values.
Can I group dates by month?
Yes. Right-click a valid date in the PivotTable, choose Group, and select the desired time periods. Grouping can fail if the source date field contains blanks, invalid dates, or text values.
Related
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Power Pivot Table Tutorial for BeginnersIn Excel for Microsoft 365 on Windows, enable Power Pivot, add your data tables to the workbook Data…
- Excel Gantt Chart by Month: Build, Format, and UpdateTo make an Excel Gantt chart by month, set up one column for each month and use conditional…
- Excel Sparklines: Where to Find Them and How to InsertIn Excel for Microsoft 365 on Windows, go to Insert > Sparklines, then choose Line, Column, or Win/Loss.…
- How to Make a Graph in Excel From Your Spreadsheet DataTo make a graph in Excel, arrange your information in labeled columns or rows, select the cells you…
- Excel Chart Title From Cell: Link, Format, and FixesTo link an Excel chart title to a cell, select the chart title, click the formula bar, type…
- Excel Pivot Table Data Range: How to Extend It SafelyTo extend an Excel PivotTable’s source range, select the PivotTable, choose PivotTable Analyze > Change Data Source, enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.