Spreadsheet Practice

Google Sheets lesson 11 of 11, Sheets power tools

Capstone: a sales summary in Sheets

Build a working sales summary on one order log: a self-filling revenue column, a region table, a top-reps ranking and a drill-down.

ProSheets mode20 minutes, 4 exercisesARRAYFORMULASORTUNIQUESUMIFSQUERYFILTER

Watch it done

5 steps

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

How it works

This capstone puts the power tools together on one order log. Each task builds on the one before, the way a real summary sheet grows over a week of requests.

Start with a calculated column that fills itself. =ARRAYFORMULA(IF(A2:A="", "", D2:D*E2:E)) multiplies units by price for every row that has a rep and leaves the rest blank, so a new order gets its revenue without anyone copying a formula down.

Then the summaries. SORT(UNIQUE(B2:B13)) lists each region once, in order, and a SUMIFS beside it adds up revenue for each one. QUERY builds a grouped, ranked summary in a single formula. FILTER answers the follow-up question every summary gets: which orders make up that number?

Point every summary at the revenue column instead of multiplying units by price again, and give each result room to spill.

Syntax

=ARRAYFORMULA(IF(A2:A="","",D2:D*E2:E))A whole calculated column from one cell
=SORT(UNIQUE(B2:B13))Each region once, A to Z
=SUMIFS($F$2:$F$13,$B$2:$B$13,H2)Revenue for the region in H2
=QUERY(A1:F13,"select A, sum(F) group by A order by sum(F) desc limit 3",1)Top three reps by revenue
=IFNA(FILTER(C2:F13,B2:B13=L7,F2:F13>=L8),"No orders")The orders behind a number, with a fallback

Watch out for

  • Without the IF, ARRAYFORMULA(D2:D*E2:E) puts a 0 in every empty row down the column.
  • Don't type anything inside a spill area. One stray value blocks the whole result and the formula shows an error.
  • UNIQUE treats "East" and "East " (with a trailing space) as two regions. Clean the text before you summarize it.
  • SUMIFS ranges must be the same size. F2:F13 with B2:B14 returns an error.

The exercises

  1. A revenue column that fills itself. In F2, write one ARRAYFORMULA that fills the whole Revenue column: units (D) times price (E) for every row with a rep in column A, and blank for rows without one. Use open-ended ranges that start at row 2.
  2. Revenue by region. In H2, list each region from column B once, sorted A to Z. Then, in I2, total the revenue (column F) for the region in H2 with SUMIFS, and fill it down to I5.
  3. Top three reps. In K1, use QUERY on A1:F13 to show the top three reps by total revenue (column F), biggest first, with the total column labeled "Revenue". Pass 1 for headers.
  4. The orders behind a number. Your manager asks which orders make up a region's total. In K10, list the product, units, price and revenue (columns C to F) of the orders in the region typed in L7 with revenue of at least the amount in L8. Show "No orders" when nothing matches.

The practice grid runs in Sheets mode: FILTER, SORT, QUERY, ARRAYFORMULA, SPLIT and the REGEX functions behave as they do in Google Sheets, and empty results stay blank.

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. A revenue column that fills itself
  2. Revenue by region
  3. Top three reps
  4. The orders behind a number
Try these functions in the Formula Lab