Data Analysis and PivotTables
Scope
Data Analysis and PivotTables covers summarizing large data interactively and the what-if tools used to test outcomes.
PivotTable fundamentals
Base the pivot on an Excel table or a well-formed range with unique headers and no blank rows. Insert > PivotTable places it on a new or existing sheet. Fields go to Filters, Columns, Rows and Values. Values default to SUM for numeric fields and COUNT for text; Value Field Settings change the summary to Average, Max, Min, Count Numbers, or Show Values As (% of Grand Total, % of Column Total, Running Total, Rank). Dates can be grouped by month, quarter and year; numbers by ranges. The pivot uses a cached copy of the source, so Refresh is required after edits. Slicers and Timelines filter interactively; calculated fields build new totals from existing fields.
What-if tools
Goal Seek finds the single input that makes a formula reach a target. Data Table shows one or two variables across a grid of results. Scenario Manager stores named sets of inputs and produces summary reports. Solver, an add-in, optimizes with multiple constraints. Forecast Sheet projects a time series.
Worked example
To count orders per region, drag Region to Rows and Order ID to Values. Because IDs are text, the default is Count, which is exactly what is wanted. If the pivot shows Count of Sales instead of a total, open Value Field Settings and switch to Sum. If the count looks stale after new orders are added, the cache must be refreshed.
Traps
- Blank cells in the source produce a "(blank)" row unless filtered out.
- Calculated fields operate on sums of fields, not row-by-row values.
- GETPIVOTDATA makes formulas robust but blocks normal cell referencing; it can be switched off in PivotTable Options.
- A pivot chart tied to a pivot loses fields if the pivot is filtered to nothing.