Yes—set the value field’s Show Values As option to No Calculation to display its normal result instead of a percentage, running total, or other comparison. However, an Excel PivotTable still summarizes fields placed in the Values area; No Calculation does not show every source record as an unsummarized value.
What No Calculation means in a PivotTable
PivotTables organize source records into groups and summarize the records in each group. For example, if you put Region in Rows and Units Sold in Values, Excel normally adds the units for each region. The result is a sum for each group, not a list of the original unit entries.
The Show Values As setting changes how that summary is presented. Options such as % of Grand Total, Difference From, and Running Total In transform the summarized result for comparison or analysis. No Calculation turns off that extra transformation, so the field displays its ordinary summary, such as Sum of Units Sold.
That distinction matters: there are two separate choices. Summarize Values By determines whether Excel uses Sum, Count, Average, or another summary. Show Values As determines whether Excel further displays that summary as a percentage, difference, running total, or similar calculation. Setting the second choice to No Calculation does not change the first.
Set Show Values As to No Calculation
In Excel for Microsoft 365 on Windows, use these steps:
- Click any number in the PivotTable’s value column, or click the value field in the PivotTable Fields pane.
- Right-click the selected value and choose Show Values As, then choose No Calculation, if that option appears in the context menu.
- If the context menu does not offer the setting, right-click a value and choose Value Field Settings. You can also open the field’s drop-down menu in the Values area of the PivotTable Fields pane and choose Value Field Settings.
- In the dialog, open the Show Values As tab, select No Calculation, and click OK.
The dialog also has a separate Summarize Values By tab. Leave that setting as it is if you only want to remove a display calculation. If the field currently says % of Grand Total, for example, No Calculation returns it to the selected summary—often Sum—not to individual source values.
In Excel for Mac, the field settings are available from the PivotTable’s value field menu or by Control-clicking a value and opening Value Field Settings. The dialog’s options may be arranged somewhat differently depending on the Excel build, but look for the Show Values As setting and choose No Calculation. Excel for the web offers PivotTable field settings too, though the available commands and dialog layout can differ from the Windows desktop app.
Why Excel summarizes values in the first place
A PivotTable’s purpose is to group many records into a compact report. If a source list has hundreds of sales rows, placing Product in Rows and Revenue in Values gives a total per product. Without summarization, Excel would have no single value to put in a group that contains several source records.
Excel chooses a default summary based partly on the data it finds. Numeric values are commonly summarized by Sum. Text or mixed data is commonly summarized by Count, which counts nonblank entries. A field used as a category—such as a salesperson’s name or a product label—usually belongs in Rows or Columns rather than Values if you want to see the labels themselves.
For example, suppose the source data has three entries for North with units of 4, 6, and 5. With Region in Rows and Units in Values, the North result is 15 when summarized by Sum. No Calculation displays 15 plainly. It does not display 4, 6, and 5 separately because those three records belong to the same Region group.
Show one value per group when the source supports it
If each group has exactly one source record, a summary can appear to be the original value. For instance, if every product appears once, the PivotTable’s Sum for each product equals that row’s value. You could also choose Max or Min and get the same result for a group containing just one number. That is still a summary operation, even if the displayed result matches the source.
Be careful if a group can contain multiple records. Max returns the largest value and Min returns the smallest; neither preserves all the values. Count counts entries rather than displaying their contents, and Average produces a mean. Choosing an aggregation simply to make the PivotTable look unsummarized can hide meaningful differences in the data.
For a text field, placing the field in Values generally produces a count rather than the text entry. To see labels such as customer names or notes, place the field in Rows or Columns. If you need a separate text value associated with each group, first decide what should happen when a group has more than one text value; a PivotTable cannot infer which record you intend to display.
Display individual source records instead
If your goal is to see every underlying record, use the PivotTable to identify a group and then open its details. In many PivotTables, double-clicking a summarized number opens a new worksheet containing the source rows behind that number. You can also right-click the number and choose Show Details. This is often called drilling down. It reveals the records contributing to the summary; it does not change the PivotTable into a non-aggregating table.
For a report that should show all records in rows, use the original data as a regular Excel table or create a separate report from it. You can still use filters, sorting, and formatting without a PivotTable. Another option is to include more identifying fields in the PivotTable’s Rows area so each source record becomes its own row-level group. This can make the PivotTable much larger, and identical combinations of row fields may still be grouped together.
To change the layout of row fields, click inside the PivotTable and use Design > Report Layout. The Tabular Form and Outline Form options can make field labels easier to read than Compact Form. These layouts affect how row fields are displayed; they do not turn off the summary applied to the Values area.
Choose the right summary and number format
If No Calculation is selected but the result still is not what you expect, check the summary method. Click a value, open Value Field Settings, and review Summarize Values By. Common choices include:
- Sum: Adds numeric entries in each group.
- Count: Counts nonblank entries, including text.
- Average: Calculates the arithmetic mean of numeric entries.
- Max or Min: Returns the largest or smallest numeric entry in a group.
- Count Numbers: Counts numeric entries rather than all nonblank entries.
The suitable choice depends on what one source row represents. For sales amounts, Sum may be appropriate; for a list of transaction IDs, Count may answer how many transactions occurred. Confirm the meaning of the result before using it in a report.
If the value is correct but its appearance is wrong, change its number format rather than its calculation. In Value Field Settings, choose Number Format and select an appropriate format, such as Number, Currency, or Percentage. Formatting the value field this way is more reliable than formatting only one cell, because the field format is more likely to remain consistent when the PivotTable refreshes or changes size.
Common problems and quick checks
- The field still shows a total: That is expected if Summarize Values By is Sum. No Calculation removes an extra display calculation, not the aggregation.
- The field shows Count instead of the text or number expected: Check the source column for text-formatted numbers, blanks, or a mixture of text and numbers. Correct the source data if appropriate, then refresh the PivotTable.
- No Calculation is already selected: The PivotTable may already be showing the ordinary summary. If the summary itself is the problem, review Summarize Values By or change the report layout.
- Changes to the source do not appear: Click inside the PivotTable and choose PivotTable Analyze > Refresh. If you added rows outside the PivotTable’s source range, update the source using PivotTable Analyze > Change Data Source, or use an Excel table as the source so added rows can be included when refreshed.
- The field is missing from the field list: Confirm that the source range includes the relevant column and has a clear heading. Refresh or change the source range if the data structure has changed.
Refreshing updates the PivotTable from its source; it does not turn off calculations or change a field’s intended meaning. If the underlying data contains errors or inconsistent categories, fix those at the source rather than relying on a different display option to disguise them.
PivotTable versus a regular table
A regular Excel table keeps one row for each source record and is usually the better choice when the priority is to read or edit individual entries. A PivotTable is better when you want grouped totals, counts, comparisons, or a compact summary that can be rearranged quickly. You can maintain both: keep the detailed source table intact and build a separate PivotTable for analysis.
Do not overwrite the source data just to make a PivotTable display the way you want. A PivotTable is a report based on that data, not a substitute for the detailed records. Keeping the source unchanged makes it easier to audit totals, refresh the report, and investigate a particular group using Show Details.
Frequently Asked Questions
Can an Excel PivotTable show values without adding them?
Not as individual source entries in the Values area. You can change Sum to another summary, or use No Calculation to remove an extra display calculation, but a Values field remains summarized by group. Put detail fields in Rows or use the source table to show records.
What does No Calculation mean in PivotTable Value Field Settings?
It means Excel displays the selected summary as-is instead of converting it to a percentage, difference, running total, or another Show Values As result. It does not mean that Excel stops summarizing the source records.
How do I show text instead of Count in a PivotTable?
Move the text field from Values to Rows or Columns. Values is intended for summaries, so a text field placed there is generally counted rather than displayed as a text entry.
Can I display every original row in a PivotTable?
You can add enough fields to Rows to distinguish records, but identical combinations may still be grouped and value fields are still summarized. For a straightforward record-by-record list, use the source table. To inspect the records behind one summary, use Show Details or double-click its value.
Why does my PivotTable show Count instead of Sum?
Excel may have detected text, blanks, or mixed types in the source column, or the field may contain labels rather than numeric data. Check and correct the source values, refresh the PivotTable, and then review Summarize Values By in Value Field Settings.
Related
- How to Make a Nice Poster in PowerPoint: A Practical Guide
- Debt Snowball Spreadsheet: Set It Up in Excel
- Excel Opposite of Text to Columns: How to Combine Data
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Project Timeline Excel Template With Gantt ChartA project timeline Excel template with a Gantt chart can be built from a task list, start and…
- New Excel Charts for Dashboards: What to UseYes. Excel for Microsoft 365 includes several chart types that can make dashboards more informative, including waterfall, funnel,…
- Google Sheets Chart Editor: Create and Customize ChartsThe Google Sheets Chart editor is the sidebar for creating and changing charts in a spreadsheet. Select the…
- Excel Graph Not Showing 0 Values? Fix the ChartExcel normally plots a numeric zero as a value, but it may be hard to see: it can…
- Call Center KPI Dashboard Excel Template: Setup GuideA call center KPI dashboard Excel template is a workbook that turns consistently recorded call data into a…
- Excel Pivot Table Fields in Filters and Rows ExplainedIn Excel for Microsoft 365 on Windows, a PivotTable field normally cannot occupy the Filters and Rows areas…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.