Spreadsheet Practice

Lesson 28 of 29, Capstones

Capstone: sales report

Build a report the way you would at work: a calculated column, an input-driven summary and a self-updating table.

Pro15 minutes, 3 exercisesSUMIFSSORTUNIQUE#

Watch it done

2 steps

Watch the technique worked out on sample data, then try it yourself in the exercises.

How it works

Real reports chain small skills. First a calculated column. Then a summary that reacts to an input cell. Then a summary table that grows when a new rep appears, with no refilling.

The last step uses the spill reference #: L2# means "the whole list that spills from L2", however long it gets.

Syntax

=E2*F2Revenue column
=SUMIFS(G2:G13,C2:C13,J2)Region total from an input
=SUMIFS(G2:G13,B2:B13,L2#)One total per rep, spilled

Watch out for

  • Point at L2#, not L2:L6. A fixed range misses new reps.

The exercises

  1. Revenue column. In G2, calculate revenue as units × price. Fill down to G13.
  2. Region summary. In J3, total revenue for the region typed in J2.
  3. Self-updating rep table. In L2, list each rep once, A to Z. In M2, total each rep's revenue with one formula that points at the whole spill in L2.

Graded practice for this lesson is part of Pro

The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 27 Pro lessons, Excel and Google Sheets, 79 exercises in all.

  1. Revenue column
  2. Region summary
  3. Self-updating rep table
Try these functions in the Formula Lab