An Excel practice test: the month-end report

The last lesson is a test. A month of sales, five questions in plain words, and no formula shown unless you ask for it. Each question needs one of the earlier lessons. The sheet above is the test; try it before you read on, because the answers are below.

The brief

This sheet comes from the lesson, set in the Plant nursery: who sold, through which channel, how many units at what price.

Fill in the revenue for every sale, then answer the questions in column G: the month's total, the total for one channel, that channel's share of the month, who made the single best sale, and whether the month hit the target in H6.

Each answer goes in the cell next to its question. There is a Show me button on every step; it drops that step to bronze, nothing more.

What each question needs

Revenue per sale

Units times price, then filled down the column. From Copying: absolute reference.

The month's total

A SUM of the revenue column. From Totals: SUM formula.

One channel only

A SUMIF on the channel column, adding the revenue. From Conditions: SUMIF and COUNTIF.

Its share

The channel's total divided by the month's. The cell is already formatted as a percentage. From Percentages: percentage formula.

The best single sale

Two things at once: the largest revenue, then who made it. MAX finds the first, a lookup finds the second. From Lookups: VLOOKUP.

Target hit?

An IF that compares the total with the target and answers Yes or No. From Decisions: IF function.

The answers

Revenue in E2 is =C2*D2, 66.00, filled down to the last sale. The month's total is =SUM(E2:E8), 1,250.00. The channel total is =SUMIF(B2:B8,"Web",E2:E8), 285.00, and its share =H3/H2, 22.8%.

The best sale takes one formula inside another: =XLOOKUP(MAX(E2:E8),E2:E8,A2:A8) gives Omar. MAX finds the largest revenue, and XLOOKUP looks for that number in the revenue column and brings back the name from column A. In Excel before 2021, INDEX with MATCH does the same: =INDEX(A2:A8,MATCH(MAX(E2:E8),E2:E8,0)) gives Omar.

Finally =IF(H2>=H6,"Yes","No") gives No: the total in H2 against the target in H6. Change any number in the data and the whole report follows.

The test, step by step

1. 1 Revenue per sale

   In E2: =C2*D2 gives 66.00.

2. 2 Every row

   Fill E2 down over E2:E8. The last one, E8, becomes =C8*D8.

   They give 63.00 222.00 420.00 70.00 301.00 108.00

3. 3 The month's total

   In H2: =SUM(E2:E8) gives 1,250.00.

   Named mistake: =SUM(E2:E7) gives 1,142.00. That stops one row early, at E7. The range goes down to E8.

4. 4 Only Web

   In H3: =SUMIF(B2:B8,"Web",E2:E8) gives 285.00.

5. 5 Web's share

   In H4: =H3/H2 gives 22.8%.

   Named mistake: =H3/H2*100 gives 2280.0%. Leave out *100. The cell is set to show a percentage, so Excel does that part.

6. 6 Who made the best sale?

   In H5: =XLOOKUP(MAX(E2:E8),E2:E8,A2:A8) gives Omar.

7. 7 Did we hit the target?

   In H7: =IF(H2>=H6,"Yes","No") gives No.

After the test

Play it again with New sheet for another setting with new numbers; the questions stay, the answers change. Clearing it in three settings with gold on every step counts as mastered on your progress page. Then there is the Daily sheet, one a day, the same for everyone.

Keep going

1. Next up is the Daily sheetOne sheet a day, the same for everyone.
2. 6
   Conditions: SUMIF and COUNTIFTotals and counts that only include the rows you ask for.
3. 7
   Lookups: VLOOKUPFind a code in a table and bring back its row, with VLOOKUP and XLOOKUP.