Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Clear out junk files and repair common Windows errorsFree Scan →Scan for outdated or missing drivers - takes under a minuteDriver Scan →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
For most Excel PivotTables, start with Sum for additive values, Count for populated entries, and Average when the mean is the measure you need. Use GETPIVOTDATA in a separate worksheet formula when you need to retrieve a visible PivotTable result by its field and item labels. The first three summarize source records; GETPIVOTDATA fetches a result the PivotTable has already calculated.
Choose the operation that answers your question
| Choice | What it returns | Use it for |
|---|---|---|
| Sum | The total of the values | Additive measures such as amounts or quantities |
| Count | The number of nonempty entries | Counting populated records, including entries stored as text |
| Average | The arithmetic mean of the values | A mean that meaningfully represents the records |
| GETPIVOTDATA | A visible result from a PivotTable, selected by field and item context | A separate worksheet calculation that refers to a PivotTable result |
Excel has additional summary choices, including Max, Min, Product, Count Numbers, and statistical functions. These four are a useful starting set, not the complete list.
Sum: total numeric values
Use Sum when the question is about a total, such as total sales, hours, or units. Microsoft documents Sum as the default summary for a numeric value field. To change a field’s summary, open its value-field settings in the PivotTable and choose the appropriate summary function. Microsoft’s PivotTable sum guidance explains the behavior.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11If a field contains blanks or nonnumeric entries when you first place it in the Values area, Excel may default to Count instead. That is not necessarily a broken PivotTable: check the selected summary function. If you switch that field to Sum, blanks and nonnumeric values are treated as zero in the PivotTable.
Count: count populated entries, not just numbers
The PivotTable summary named Count counts nonempty values, so it can include text entries as well as numbers. It is similar to the worksheet function COUNTA. Excel may choose Count by default when the value field contains blanks or nonnumeric values.
Do not confuse this with Count Numbers, a separate PivotTable summary choice that counts numeric entries and behaves like the worksheet function COUNT. Choose based on what a record means in your data: Count answers “How many entries are populated?” while Count Numbers answers “How many entries are numeric?” Microsoft’s PivotTable calculation guidance distinguishes the available calculations.
Rank #2
- Used Book in Good Condition
Average: use the mean only when it fits
Average calculates the average of the field’s values. Choose it when the arithmetic mean answers the question—for example, the average value per record. A mean can hide variation, and it may misrepresent a measure when records should contribute different weights. If the question concerns a weighted average or the spread of values, Average alone may not provide the desired measure.
GETPIVOTDATA: retrieve a visible PivotTable result
GETPIVOTDATA is a worksheet function, not a fourth PivotTable summary setting. It retrieves data visible in a PivotTable, which is useful when a separate formula needs a result identified by PivotTable field and item labels rather than by a hard-coded cell address.
The syntax is GETPIVOTDATA(data_field, pivot_table, [field1, item1], ...). data_field identifies the value field to retrieve; pivot_table refers to a cell or range inside the target PivotTable; optional field/item pairs specify the intersection to return. Excel can create a simple GETPIVOTDATA formula when you type = in another cell and click a PivotTable value. See Microsoft’s GETPIVOTDATA documentation for syntax and examples.
Example pattern
A formula can request the value field “Sales” for the item “West” in the “Region” field. The exact field and item names must match the PivotTable, and the pivot_table argument must point into that PivotTable. Field and item names that are not dates or numbers need quotation marks in the formula. For date items, use serial values or the DATE function to avoid locale-dependent date text.
Understand visibility and errors
GETPIVOTDATA can return #REF! if its PivotTable reference does not point inside a PivotTable, or if the requested field or item is not visible—for example, because a filter excludes it. It retrieves visible PivotTable data; it should not be treated as a way to obtain a result hidden by the current display. If a formula stops working after a filter change, check whether the requested item remains visible.
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Scan for outdated or missing drivers - takes under a minute3Clear out junk files and repair common Windows errorsQuick Recap
Best Value
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Check the source and calculation limits
- OLAP sources: The standard summary-function choices described here are not available for PivotTables based on OLAP source data.
- Calculated fields: In non-OLAP PivotTables, calculated-field formulas operate on sums of underlying data. They cannot use cell references or defined names, or worksheet functions that require cell references or defined names as arguments. Do not assume a normal worksheet formula can be entered unchanged as a calculated-field formula.
- PivotTable versus PivotChart: PivotTables summarize and analyze data; PivotCharts help visualize comparisons, patterns, and trends. Microsoft’s overview of PivotTables and PivotCharts describes their roles.
- Static worksheet formulas: If you need to convert PivotTable cells to worksheet formulas, rather than retrieve a visible result with GETPIVOTDATA, Microsoft documents a separate conversion workflow: Convert PivotTable cells to worksheet formulas.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
