Row and Column Totals in a Sales Table
Your task: In E2:E4 and B5:E5, total each store's quarter, each month, and the grand total.
Full instructions, scenario and goal
Scenario
A retail chain has three stores. The table shows each store's sales for the first quarter. The regional manager wants a total for every store, a total for every month and the grand total, and wants them to keep working when late figures are filled in.
Your task
- In E2, add up the three months for the Downtown store. Do the same in E3 and E4 for the other stores.
- In B5, add up the January sales of all three stores. Do the same for February (C5) and March (D5).
- In E5, calculate the grand total for the whole quarter.
- Tip: write one formula, then copy it (Ctrl+C) and paste it (Ctrl+V) into the cells that need the same calculation.
- Select Check Answer when every highlighted cell has a formula.
You are done when
- Every total is a formula that adds the matching figures.
- The totals still work if a figure changes or a month is empty.
What you will practise
Add up a row and a column of figures with SUM, and understand that a copied formula shifts its references.
The idea in 30 seconds
SUM adds every number in a range. A range is two corner cells joined by a colon: G5:I5 is the three cells of row 5 from column G to I, and G5:G7 is the three cells of column G from row 5 to 7. The same function works across a row and down a column, so a table with row totals on the right and column totals at the bottom needs only SUM.
Spreadsheet
Loading spreadsheet…
Keyboard shortcuts
- Arrow keys
- Move between cells
- Shift + Arrow keys, or drag with the mouse
- Select a range
- Ctrl + Arrow keys
- Jump to the edge of a block of data
- Type, or F2, or double-click
- Edit a cell (F2 switches arrow keys between moving and editing)
- Click or arrow keys inside a formula
- Insert a cell reference after =, +, (, comma and similar
- F4
- Cycle a reference through A1, $A$1, A$1, $A1
- Enter / Tab while editing
- Confirm and move down / right (Shift reverses)
- Esc
- Cancel the edit
- Ctrl + C / X / V
- Copy, cut and paste (formulas adjust like in Excel)
- Delete
- Clear the selected cells
- Ctrl + Z / Ctrl + Y
- Undo and redo
- Ctrl + Enter
- Check your answer
- Tab when not editing
- Leave the spreadsheet
Type your answer in cell E2. Click a cell and type. Enter confirms, Esc cancels, Ctrl+Enter checks your answer.Tap a highlighted cell to type in it, then tap the green tick to confirm. Scroll the sheet sideways if it is wider than your screen.
New here? How an exercise works
- Read the task. Highlighted cells are yours to fill in; the rest are locked.
- Select (or tap) a highlighted cell and type a formula. It starts with =, like =A1+B1.
- Press Check answer. Stuck? Reveal hints one at a time.
The full explanation: the idea behind this exercise
SUM adds every number in a range. A range is two corner cells joined by a colon: G5:I5 is the three cells of row 5 from column G to I, and G5:G7 is the three cells of column G from row 5 to 7. The same function works across a row and down a column, so a table with row totals on the right and column totals at the bottom needs only SUM.
When you copy a formula to another cell, Excel moves its references by the same distance. Copy a SUM over G5:I5 one row down and it now adds G6:I6. Copy a SUM over G5:G7 one column to the right and it now adds H5:H7. This is called a relative reference, and it is why you rarely need to retype a formula for every row. Empty cells inside a range are simply ignored.
Skills you will practise
- SUM
- Ranges
- Relative references
- Totals
Complete first: Invoice Total with Cell References
Common mistakes to avoid
- Stopping a range one cell early
- A range that ends one column early skips March. Select the whole row of figures.
- Typing the totals
- A typed number does not change when the data does. Use SUM so the totals update.