Data Analysis and What-If Tools
This domain tests summarising and exploring data: sorting and filtering correctly, subtotalling, building pivot tables, and using the what-if tools to answer questions about inputs you do not yet know.
Sorting, filtering, and subtotals
- Sort works on the whole record set; sorting only one column destroys row integrity. Multi-level sorts apply up to the available sort levels, and custom lists sort by a defined order such as month names.
- AutoFilter shows matching rows; Advanced Filter copies results elsewhere and supports criteria ranges with AND across a row and OR down a column.
- SUBTOTAL with function number 9 sums visible rows and ignores rows hidden by a filter, while 109 also ignores manually hidden rows. Plain SUM counts everything, so it double-counts when subtotal rows sit inside the summed range.
Pivot tables
A pivot table places fields in Rows, Columns, Values, and Filters. Values aggregate by Sum, Count, Average and others; the default for numeric fields is Sum and for text fields Count. Pivot tables read a cached copy of the source, so Refresh is required after the data changes, and adding rows outside the original source range is invisible until the source is redefined or a table is used.
What-if tools
- Goal Seek finds one input value that produces a specified formula result.
- Data Table shows how one or two inputs affect one or more formula results.
- Scenario Manager stores and compares named sets of input values.
- Solver, an add-in, optimises a target by changing multiple variables subject to constraints.
Worked example
A loan sheet computes a monthly payment from principal, term, and rate. You know the payment you can afford and want the maximum rate. Use Goal Seek: set the payment cell to the target value by changing the rate cell. Solver would be needed only if several inputs could change at once, or if constraints applied.
Traps that cost marks
- Expecting a pivot table to update without refreshing.
- Sorting a single column and separating values from their records.
- Mixing SUM with subtotal rows in the same column.