Summary tables practice exercises

Build the summary a pivot table gives you, with formulas: totals, counts, averages and shares per group, in a table you can fill down and across.

Start the first exercise

How to build a summary table with formulas

A summary table shows one number per group, such as total sales per region, the way a pivot table does. You build it with SUMIF, SUMIFS, COUNTIF and AVERAGEIF, and lock the data ranges with $ so one formula can be filled down and across.

Start with a list of regions in E2:E4. In F2, =SUMIF($A$2:$A$9,E2,$C$2:$C$9) totals the sales for the region in E2, and filling it down gives the other regions. For a cross-table with regions down and products across, use =SUMIFS($C$2:$C$9,$A$2:$A$9,$E3,$B$2:$B$9,F$2). The $ in $E3 locks the column and the $ in F$2 locks the row. Divide a region's total by SUM($C$2:$C$9) to get its share.

A pivot table suits big data sets and quick rearranging, while formulas give you a table you control and that updates as soon as the data changes. New groups must be added to the list by hand. The exercises below cover totals, a cross-table, counts, averages, shares and a counted cross-table.

  1. 1Total per region, ready to fill downIn F2, total the Sales for the region in E2 with SUMIF. Lock the data ranges with $ so the formula can be filled down to F4: =SUMIF($A$2:$A$9,E2,$C$2:$C$9).
  2. 2A cross-table with mixed referencesPremiumBuild a table with regions down and products across. In F3, use SUMIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across.
  3. 3Orders per region with COUNTIFPremiumIn F2, count the orders for the region in E2 with COUNTIF. Lock the region range with $ so the formula can be filled down.
  4. 4Average order per regionPremiumIn F2, find the average Sales for the region in E2 with AVERAGEIF, with the data ranges locked so the formula can be filled down.
  5. 5Each region's share of the totalPremiumIn F2, work out the share of all sales for the region in E2, as a decimal like 0.25: the region's SUMIF total divided by SUM($C$2:$C$9).
  6. 6Count a cross-table with COUNTIFSPremiumCount the orders with regions down and products across. In F3, use COUNTIFS for the region in E3 and the product in F2, with $E3 and F$2 so the formula can be filled down and across.

Functions in this topic

Read the guide

Questions

What is the difference between a pivot table and a summary table with formulas?

A pivot table builds the groups for you, and you refresh it after the data changes. A formula table updates the moment the data does, and you decide the layout, but you have to type each group name yourself. For large data and quick exploring, a pivot table is faster.

How do I show each share as a percentage in a summary table?

Divide each group's total by the grand total, as in =SUMIF($A$2:$A$9,E2,$C$2:$C$9)/SUM($C$2:$C$9). The result is a decimal like 0.25. Select the cells and click Percent Style on the Home tab to show 25%. The formatting changes only how the number looks, not its value.

Why do my numbers go wrong when I fill the formula down?

The ranges are probably not locked. Without $, A2:A9 moves down with the formula and soon points at the wrong rows. Press F4 while the cursor is in a range to add the $ signs, giving $A$2:$A$9. Keep the cell with the group name, like E2, unlocked so it moves down.