Use a Pivot Table to Summarize Excel Data
In an earlier post, we used the SUMIFS function to create a summary table like the one on the right of this example:
There, we summed the budget figures for various projects, segmenting them by status of Red, Amber, and Green (columns), and by Area (rows).
We can create a similar table using a Pivot Table. It is arguably more automated, and easier to manipulate. Let’s do it:
Start by selecting the data –in our case, the range A3:D13. Note that we include the row of headings.
On the Insert ribbon, in the first group (Tables), click Pivot Table.
In the Pivot Table dialog box, the Table/Range should already be $A$3:$D$13.
Choose “Existing Worksheet” and Location F16:
Click OK.
A new window pane, PivotTable Fields, opens on the right of the Excel window. Meanwhile, a placeholder for PivotTable1 appears, anchored on cell F16. The screen then looks like this:
We are going to segment the results by Status (columns) and by Area (rows). From the PivotTable Fields list, drag Status into the “Columns” block in the grid below. Drag Area into the “Rows” block below.
To perform the summation, from the PivotTable Fields list, drag Budget to the ∑ (Sum) Values block in the grid below.
In the ∑ (Sum) Values block in the grid, click on “Sum of Budget”. From the pop-up/dropdown menu, choose Value Field Settings:
Notice, firstly, that our calculations are not limited to a Sum. All the usual statistical functions are available: Count, Average, Max, Min, Standard Deviation, Variance, etc.
We will keep the Sum, but shorten the Custom Name to “Sum Budget”.
Use the Number Format button at the bottom left to change the format to Number, with zero decimals and a 1000 Separator. Click OK for Number Format.
Click OK for Value Field Settings.
Your screen should now look like this:
As you can see, we get the same totals by the Pivot Table method as we did using the SUMIFS formulas. The columns are in a different order, since the Pivot Table sorts the columns in order.
The Pivot Table also gives us Grand Totals per column, row, and for all the data.