Excel PivotTable value field settings control how a field in the Values area is summarized and how its results are displayed. To open them in Excel for Microsoft 365 on Windows, right-click a number in the PivotTable and choose Value Field Settings; then use Summarize Values By, Show Values As, or Number Format to change the result.
These settings matter because the same source column can answer different questions: Sum of Sales totals revenue, Average of Sales gives a typical transaction value, and % of Grand Total shows each result’s share of all sales. Knowing which setting changes the calculation—and which only changes its presentation—makes a PivotTable easier to interpret and troubleshoot.
What Value Field Settings Control
A PivotTable separates fields into areas such as Rows, Columns, Filters, and Values. A field in Values is calculated for each group created by the other areas. For example, if Region is in Rows and Sales is in Values, Excel calculates a result for each region. Value Field Settings lets you choose the calculation and, separately, an optional display calculation.
There are two main tabs in the settings dialog:
- Summarize Values By selects the underlying aggregation, such as Sum, Count, or Average.
- Show Values As transforms the displayed result into a percentage, difference, running total, rank, or another comparison.
Those choices are independent. You can sum sales and then show each regional sum as a percentage of the grand total, for instance. The source data is not changed; the settings affect the PivotTable’s calculation and presentation.
How to Open and Apply the Settings
- Click a numeric result in the value field you want to change. If the same source field appears more than once in Values, make sure you click a result belonging to the correct instance.
- Right-click and choose Value Field Settings.
- In Summarize Values By, select the calculation you need. Choose Show Values As if you also want a comparison or percentage.
- To control how numbers look, click Number Format in the dialog, choose a category such as Number, Currency, or Percentage, set the options, and confirm.
- Choose OK to apply the changes.
The same dialog is available by opening the field’s menu in the PivotTable Fields pane and selecting Value Field Settings. Right-clicking a result cell is usually the quickest route and helps identify which value field you are editing. On Mac, use a right-click or Control-click to open the contextual menu; labels and placement can vary slightly by Excel release. Excel for the web has fewer PivotTable authoring controls than desktop Excel, so open the workbook in the desktop app if a needed setting is unavailable.
Choosing a Summary Calculation
Excel commonly uses Sum for numeric data and Count for text or mixed data when a field is first added to Values. You can change the default at any time. The best choice depends on what one source row represents and what question the summary should answer.
- Sum: Adds numeric values. Use it for totals such as units sold, hours, or invoice amounts.
- Count: Counts nonempty entries, including text. Use it to count records or responses when each populated cell represents an entry.
- Count Numbers: Counts numeric entries rather than all nonempty entries. This can help when a column contains both numbers and text.
- Average: Calculates the arithmetic mean of numeric values. It is useful for average order size or average time, but not necessarily for an average of already-aggregated averages.
- Max and Min: Return the largest or smallest value in each group, such as the latest date or highest sale.
- Product: Multiplies the numeric values together. This is less common and is only suitable when multiplication matches the meaning of the data.
- StdDev, StdDevp, Var, and Varp: Calculate statistical measures of spread. The versions ending in p treat the data as the full population; the other versions treat it as a sample.
For example, suppose a sales table has one row per order line, with Region, Product, and Sales columns. Summing Sales by Region gives total sales by region. Counting Product gives the number of nonblank product entries, which may represent order lines—not necessarily the number of distinct products or orders. The meaning depends on the row structure, so verify what a record represents before interpreting a count.
Using Show Values As
Show Values As changes the displayed result relative to other values in the PivotTable. It does not replace the selected summary calculation. The available options depend on the layout and can include:
- % of Grand Total, % of Column Total, and % of Row Total: Express each value as a share of the relevant total. These are useful for showing regional contribution or product mix.
- Difference From and % Difference From: Compare a value with a selected item in a base field, such as this year versus last year. The percentage version expresses the change relative to the base item.
- Running Total In: Accumulates values across items in a chosen base field, often dates. The field’s item order affects the running result.
- % Running Total In: Shows the accumulating amount as a share of the total.
- Rank Smallest to Largest and Rank Largest to Smallest: Ranks values within the PivotTable comparison context.
- % Of: Shows a value relative to a selected base field and base item.
- Index: Provides a comparison measure based on a value’s row and column totals. It is more specialized and should be used only when that comparison is meaningful for the report.
For a year-over-year report, place Year in Columns and Sales in Values. Select % Difference From, set Year as the base field, and select the appropriate prior year as the base item. The result expresses the change relative to that prior-year value. A blank or unexpected result can occur if the base item is missing from a group, so check that the compared periods are present and arranged as intended.
Number Format and Custom Names
The Number Format button inside Value Field Settings is generally the best way to format PivotTable values. It associates the format with that value field, which is more reliable than formatting a handful of result cells directly. For example, choose Currency or Accounting for monetary totals, Percentage for percentage-based results, or Number with an appropriate number of decimal places for averages.
Formatting changes how a value appears, not the underlying value or calculation. Applying a percentage format to a sum of 25 does not calculate a share; it displays 25 as 2500%. To get a share, first select the relevant percentage option under Show Values As, then use Number Format to control decimal places and the percent sign.
The Custom Name box lets you use a readable heading such as Total Sales or Average Order Value instead of Sum of Sales. Each value field needs a name that does not duplicate another field name in the PivotTable. If Excel reports that the name already exists, choose a distinct label. A custom name changes the PivotTable heading, not the source column’s name.
One Source Field Can Have Different Settings
You can add the same source field to Values more than once. This is useful when a report needs both a total and a percentage of that total. For example, add Sales twice, leave the first instance as Sum, and set the second instance to % of Grand Total. Give them clear custom names such as Total Sales and Share of Sales.
Each instance has its own summary, display, and number-format settings. This is why selecting a result cell before opening Value Field Settings is important: it targets the instance associated with that cell. If the field list shows similar entries, use the PivotTable headings and the Values area to identify which one you are changing.
Common Problems and Misconceptions
- Sum appears as Count: Excel may interpret some entries in the source column as text, or the column may contain text or blanks alongside numbers. Check the source values and their data types, correct the data if appropriate, and refresh the PivotTable.
- A count is larger or smaller than expected: Count includes nonempty text entries, while Count Numbers counts numeric entries. Neither necessarily counts unique items. For unique customer or product counts, use Distinct Count where available.
- Distinct Count is missing: In Excel for Microsoft 365 on Windows, Distinct Count is available for PivotTables created using the Data Model. When creating the PivotTable, select the option to add the data to the Data Model. It is not a standard summary choice for every ordinary PivotTable.
- Percentages do not add to 100%: Check whether the setting uses a row, column, or grand total, and whether filters or hidden items affect the displayed groups. A percentage of a row total answers a different question from a percentage of the grand total.
- Formatting changes after a refresh: Prefer Number Format in Value Field Settings over formatting individual cells. Also check PivotTable options if layout or formatting behavior is unexpected; refresh updates the report from its source and can change the displayed items.
- A setting seems unavailable: Some choices need a suitable base field or item, and some PivotTable types have feature limits. For example, a comparison cannot work as expected if its selected base item is absent.
Refresh the PivotTable after editing its source data so the report reflects the updated records. In desktop Excel, use PivotTable Analyze > Refresh for the selected PivotTable, or Data > Refresh All to update workbook connections and PivotTables. Refreshing does not fix incorrect source data types; those need to be corrected in the source and then refreshed.
Value Field Settings Versus Other Calculations
Value Field Settings is for summarizing existing source values and changing how those summaries are displayed. It is not the same as a calculated field, a calculated item, or a formula added next to the PivotTable. A calculated field defines a calculation using source fields; a Show Values As choice compares or transforms PivotTable results. These tools solve different problems.
For example, if each row contains Units and Unit Price, summing an existing Line Total column may be the simplest way to report revenue. If the source does not contain Line Total and the calculation needs to combine fields, a calculated field or a source-data formula may be more appropriate. If the goal is to show each region’s share of the total revenue, use Show Values As instead. Choose the approach that matches the data and the question, rather than trying to make a display setting perform a new row-level calculation.
Frequently Asked Questions
How do I change Sum to Count in a PivotTable?
Right-click a value in the relevant PivotTable field, select Value Field Settings, choose Count under Summarize Values By, and select OK. Count includes nonempty text as well as numbers.
How do I show a PivotTable value as a percentage?
Open Value Field Settings, select Show Values As, and choose the appropriate option, such as % of Grand Total or % of Row Total. Use Number Format to adjust how many decimal places appear.
Why is my PivotTable counting instead of summing?
Excel may have detected text or mixed data in the source column. Inspect the source for numbers stored as text or nonnumeric entries, correct them where needed, refresh the PivotTable, and then select Sum in Value Field Settings.
Can I show a total and a percentage in the same PivotTable?
Yes. Add the same source field to Values twice, keep one instance as Sum, and set the other to a percentage under Show Values As. Use distinct custom names so readers can tell the two results apart.
Does changing Value Field Settings alter my source data?
No. The settings change the PivotTable’s calculation or display, not the underlying worksheet records. Editing the source and refreshing the PivotTable is a separate operation.
Related
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Black Bullseye Chart in Excel: How to Create OneA black bullseye chart is usually a custom target graphic made from concentric circles; it is not a…
- Creating a Pie Chart in Excel: Steps, Labels, and TipsTo create a pie chart in Excel for Microsoft 365 on Windows, arrange category names beside their values,…
- Pie Chart in Excel: How to Create, Format, and Label ItTo create a pie chart in Excel for Microsoft 365 on Windows, select a column of category names…
- Excel Pivot Table: Show Unique Values Without RepeatsYes. To show each distinct item once in an Excel PivotTable, drag its field into the Rows area;…
- Excel Pivot Table Totals at Top? Show SubtotalsYes. To put category subtotals above their detail rows in Excel for Microsoft 365 on Windows, click inside…
- Excel Pivot Table: Show Only Filtered Data in Your Report?Yes. In Excel for Microsoft 365 on Windows, filter a PivotTable field using its dropdown, or add a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.