Administration and Executive AssistantTier 350 minintermediate

PivotTables, Part 1: Summarise by Two Fields

Turn a long list into a cross-tab of totals, with a PivotTable and with formulas.

What you will learn

• Build a PivotTable with a field in Rows, a field in Columns and a sum in Values
• Check a pivot total against the source data
• Build the same cross-tab with one SUMIFS and mixed references
• Know when a formula summary is the better choice than a pivot

Excel skills

PivotTable, SUMIFS, mixed references · Cross-Tab Summaries

The task

I need this year's sales as a table: regions down the side, quarters across the top, with totals. In Excel I would drop the Sales tab into a PivotTable. This workspace has no PivotTable tool, so build the same table with formulas on the Summary tab - ideally one formula you can fill across and down, so it still works when more rows are added to Sales.

You work in a real spreadsheet in your browser, and your answer is checked against hidden traps the moment you submit.

Try this lesson free