Excel Practice
Lessons Lesson 98

Project: A Sales Report

About this lesson

A sales report is three things. A row-level total filled down. A summary block of SUMIFS with locked data ranges and unlocked labels. And a lookup that turns the biggest number into a name.

By the end you can

  • build a report with row totals, a summary block and a top-rep lookup.
  • lock the right ranges so a summary block fills down correctly.

Practises SUMIFS INDEX MATCH MAX COUNTIFS

The idea

The first project is the report every team has. The row total fills down. The summary block reads the labels beside it, so one SUMIFS serves every region. That works only if the data ranges are locked and the label is not, which is the mistake step 3 repairs. The best rep is MAX inside MATCH inside INDEX. The share is the part over the whole.

Nothing here is new. The point of a project is the order. Row numbers first, then the summaries that read them, then the one-line answers a manager will ask for.

The mistake to watch for

A summary block whose ranges moved. SUMIFS copied down without the dollar signs still totals something, so the wrong number looks right. Lock the data ranges and leave the label relative. Then check that the region totals add up to the grand total.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In E2, Ada's year: the two quarters added. This fills down to E7.

To begin, type it exactly:

=C2+D2

E2
Row A B C D E F G
1 Region Rep Q1 Q2 Year
2 North Ada 120 135
3 South Ben 98 110
4 North Cai 143 128
5 East Dee 76 88
6 South Eli 130 142
7 North Fay 88 95
8 North
9 South
10 Best rep
11 North share
12 North over 200

Every step

  1. 1

    In E2, Ada's year: the two quarters added. This fills down to E7. To begin, type it exactly: =C2+D2.

    Hint. Two cells added.

  2. 2

    In B8, the North total for the year: SUMIFS over the year column where the region matches A8. Lock the two ranges so it fills down the summary.

    Hint. Lock the ranges, not the label.

  3. 3

    B9 was copied down from a version without the dollar signs. Its ranges moved, and it now totals the wrong cells. It even reads the labels in A8 and A9 as data. Fix it.

    Hint. Dollar signs on the two ranges.

  4. 4

    In B10, the name of the rep with the highest year. INDEX over the names, with MATCH finding where the MAX of the year column sits.

    Hint. INDEX, MATCH, MAX, from the outside in.

  5. 5

    Read =B8/SUM(E2:E7) and say what share of the year the North had, as a fraction.

    Hint. 709 over the grand total.

  6. 6

    In B12, write a formula using COUNTIFS that counts the North reps whose year is over 200.

    Hint. "North" and ">200".