22 min read

Introduction to PivotTables

Insert → PivotTable from tblExpenses. Sum Amount by Department and Category.

Why a PivotTable

A PivotTable summarises tblExpenses by dragging Department and Amount without writing many SUMIF formulas. Governors ask new questions every meeting. A PivotTable rearranges the same expense data in seconds — by department, category, or both. James Okonkwo builds PivotSummary on NorthgateBudget.xlsx so Dr. Helen Marsh sees total spend per department before approving next term's budgets.

You *could* write many SUMIF formulas for each department. A PivotTable lets you drag fields and change the layout in seconds — no new formula each time the governors ask a different question. Approach When to use SUMIF One fixed total (e.g. Science only) PivotTable Many views — department, category, cross-tab

Approach	When to use
SUMIF	One fixed total (e.g. Science only)
PivotTable	Many views — department, category, cross-tab

On Expenses, click any cell in tblExpenses . Insert tab \to Tables group \to PivotTable . In the dialog, choose New Worksheet . Click OK . Excel opens a new sheet with an empty PivotTable and the PivotTable Fields pane on the right. Rename the sheet tab to PivotSummary (right-click tab \to Rename). Create tblExpenses first — if you select A1:E9 once and never convert to a table, row 10's new expense is outside the pivot source until you edit Change Data Source .

In the PivotTable Fields pane, drag Department to Rows . Drag Category to Columns (optional — creates a cross-tab). Drag Amount to Values . Excel defaults to Sum of Amount . Expected result: Each department shows a total; with Category in Columns you see Stationery vs Textbooks vs Equipment subtotals. Science should total £795 ( £185 + £610 ) if you entered the sample expenses. If Amount shows Count instead of Sum, right-click a value \to Summarize Values By \to Sum — someone may have dragged a text column to Values.

Click inside the PivotTable — PivotTable Analyze appears: Refresh — reread tblExpenses after new rows Change Data Source — if you renamed the table or expanded the range Field Settings — switch Sum to Count or Average After November purchases, James Okonkwo clicks Refresh once — all department totals update. Add a row to tblExpenses . Click inside the PivotTable \to PivotTable Analyze \to Refresh (or right-click the PivotTable \to Refresh). The PivotTable rereads the table — no manual formula updates.

Drag Category to Filters instead of Columns — pick Equipment only. The pivot shows department totals for lab and calculator spend without rebuilding the sheet. PivotSummary complements =SUMIF(tblExpenses[Department],"Science",tblExpenses[Amount]) — both should agree on Science's total before the governor pack goes out.

Practice

You drag Amount to the Values area. What calculation does Excel usually apply first?

Why a PivotTable

A PivotTable summarises tblExpenses by dragging Department and Amount without writing many SUMIF formulas. Governors ask new questions every meeting. A PivotTable rearranges the same expense data in seconds — by department, category, or both. James Okonkwo builds PivotSummary on NorthgateBudget.xlsx so Dr. Helen Marsh sees total spend per department before approving next term's budgets.

Why Not Just SUMIF?

You *could* write many SUMIF formulas for each department. A PivotTable lets you drag fields and change the layout in seconds — no new formula each time the governors ask a different question. Approach When to use SUMIF One fixed total (e.g. Science only) PivotTable Many views — department, category, cross-tab

Approach	When to use
SUMIF	One fixed total (e.g. Science only)
PivotTable	Many views — department, category, cross-tab

Create the PivotTable

On Expenses, click any cell in tblExpenses . Insert tab \to Tables group \to PivotTable . In the dialog, choose New Worksheet . Click OK . Excel opens a new sheet with an empty PivotTable and the PivotTable Fields pane on the right. Rename the sheet tab to PivotSummary (right-click tab \to Rename). Create tblExpenses first — if you select A1:E9 once and never convert to a table, row 10's new expense is outside the pivot source until you edit Change Data Source .

Drag Fields into the Layout

In the PivotTable Fields pane, drag Department to Rows . Drag Category to Columns (optional — creates a cross-tab). Drag Amount to Values . Excel defaults to Sum of Amount . Expected result: Each department shows a total; with Category in Columns you see Stationery vs Textbooks vs Equipment subtotals. Science should total £795 ( £185 + £610 ) if you entered the sample expenses. If Amount shows Count instead of Sum, right-click a value \to Summarize Values By \to Sum — someone may have dragged a text column to Values.

PivotTable Analyze Tab

Click inside the PivotTable — PivotTable Analyze appears: Refresh — reread tblExpenses after new rows Change Data Source — if you renamed the table or expanded the range Field Settings — switch Sum to Count or Average After November purchases, James Okonkwo clicks Refresh once — all department totals update. Add a row to tblExpenses . Click inside the PivotTable \to PivotTable Analyze \to Refresh (or right-click the PivotTable \to Refresh). The PivotTable rereads the table — no manual formula updates.

Filter Inside the Pivot

Drag Category to Filters instead of Columns — pick Equipment only. The pivot shows department totals for lab and calculator spend without rebuilding the sheet. PivotSummary complements =SUMIF(tblExpenses[Department],"Science",tblExpenses[Amount]) — both should agree on Science's total before the governor pack goes out.

Practice

You drag Amount to the Values area. What calculation does Excel usually apply first?