Excel Training for Employees: The Formulas Everyone Needs

Your path

9 steps · 2 quizzes · about 60 minutes · open any step, or read it here

1. 1
   Excel training for employees: the formulas everyone needsStart hereIn this lesson you practise the Excel formulas everyone needs at work: cell references, SUM, AVERAGE, IF and absolute references. Every step has a working spreadsheet in it, so you learn each formula by typing it and watching what it does.InteractiveHands-on · 5 min

   Read this step

   In this lesson you practise the Excel formulas everyone needs at work: cell references, SUM, AVERAGE, IF and absolute references. Every step has a working spreadsheet in it, so you learn each formula by typing it and watching what it does.

   Watch the sheet below: one formula prices a whole office supplies order.

   Interactive · One formula, a whole order — try it in the lesson

   In this lesson you'll:

   • write formulas that use cell references, so totals update by themselves
   • summarise a column with SUM, AVERAGE, MIN, MAX and COUNT
   • copy a formula down a column, and lock the one cell it must always use
   • make a decision in a cell with IF, and fix the errors you'll meet

   It takes about 60 minutes. Click any cell above to see what it holds.

2. 2
   Cells, references and the formula barA spreadsheet is a grid of cells. Each cell has an address: its column letter and row number. B2 is column B, row 2. A cell holds either a value you typed (a number, a word) or a formula. A formula starts with = and Excel shows its result, not the formula.InteractiveHands-on · 5 min

   Read this step

   A spreadsheet is a grid of cells. Each cell has an address: its column letter and row number. B2 is column B, row 2.

   A cell holds either a value you typed (a number, a word) or a formula. A formula starts with = and Excel shows its result, not the formula. The formula itself appears in the formula bar above the grid when you select the cell.

   The useful part: a formula can point at other cells. =B2*C2 means "the price in B2 times the quantity in C2". Change B2 and the result updates at once. That is why you write =B2*C2 and not =3.5*40: the second one never changes when the price does.

   Formulas use + - * (multiply) / (divide) and brackets, in the usual order: multiply and divide before add and subtract.

   Try it: click D3 and look at the amber cells — those are the cells its formula reads. Then do the three tasks under the sheet: select a cell, type in the bar, press Enter.

   Interactive · Edit the order sheet — try it in the lesson

   Takeaway: point formulas at cells, never type the numbers into them — then the sheet keeps itself right.

3. 3
   SUM and ranges: add up a whole columnAdding cells one by one (=B2+B3+B4+B5+B6+B7) works, but it is long and breaks when you add a row. Use SUM with a range instead. A range is two addresses joined by a colon: B2:B7 means every cell from B2 to B7 — six cells. =SUM(B2:B7) adds all of them.InteractiveHands-on · 5 min

   Read this step

   Adding cells one by one (=B2+B3+B4+B5+B6+B7) works, but it is long and breaks when you add a row. Use SUM with a range instead.

   A range is two addresses joined by a colon: B2:B7 means every cell from B2 to B7 — six cells. =SUM(B2:B7) adds all of them.

   A range can be part of a column, a row (B2:F2) or a block (B2:D7). In Excel you can select it with the mouse while typing the formula, and Excel writes the address for you; Alt+= (Windows) or Cmd+Shift+T (Mac) inserts a SUM of the cells above.

   Try it: press Pick a range, then tap the first and the last month you want. What do April to June add up to? And January to March?

   Interactive · Add a range of months — try it in the lesson

   Takeaway: =SUM(first:last) adds every cell in between — one formula, however many rows.

4. 4
   AVERAGE, MIN, MAX and COUNTSUM has four close relatives. They all take a rangeInteractiveHands-on · 5 min

   Read this step

   SUM has four close relatives. They all take a range:

   Formula	Gives
   =AVERAGE(B2:B7)	the mean: the sum of the numbers divided by how many numbers there are
   =MIN(B2:B7)	the smallest number
   =MAX(B2:B7)	the largest number
   =COUNT(B2:B7)	how many cells hold a number
   =COUNTA(B2:B7)	how many cells are not empty (text counts too)

   The detail that catches people: these functions skip empty cells and text in a range. An empty cell is not a zero. If one agent was on leave and their cell is empty, AVERAGE divides by the people who have a number. Type a 0 there and the average drops, because now they count.

   Try it: switch between the functions and watch which cells are counted. Then turn on "Ira's empty cell becomes 0" and see AVERAGE and COUNT change.

   Interactive · What each function counts — try it in the lesson

   Takeaway: empty means "not counted", 0 means "counted as zero" — decide which one you mean.

5. Quick check: references and functionsQuiz · 4 questions

   Read the questions

   Question 1. B2 holds 4 and C2 holds 5. What does a cell with =B2*C2 show?

   • =B2*C2
   • 45
   • 20
   • 9

   Question 2. How many cells are in the range B2:B6?

   • 4
   • 5
   • 6
   • 2

   Question 3. A2 holds 10, A3 is empty and A4 holds 20. What does =AVERAGE(A2:A4) show?

   • 10
   • 15
   • 30
   • #DIV/0!

   Question 4. B2:B6 holds three numbers, the text n/a and one empty cell. What does =COUNT(B2:B6) show?

   • 3
   • 4
   • 5
   • 2
6. 5
   Copy a formula down a columnYou almost never write the same formula twice. Write it once, then copy it: drag the small square at the bottom-right corner of the cell (the fill handle) down the column, or double-click it to fill down as far as the data next to it goes.InteractiveHands-on · 5 min

   Read this step

   You almost never write the same formula twice. Write it once, then copy it: drag the small square at the bottom-right corner of the cell (the fill handle) down the column, or double-click it to fill down as far as the data next to it goes. Ctrl+D fills the selected cells down from the top one.

   When a formula is copied, its references move with it. =B2*C2 copied one row down becomes =B3*C3; two rows down, =B4*C4. Excel keeps the same relationship — "the price and quantity on my own row" — not the same addresses. These are called relative references, and they are the default.

   Try it: with "Show formulas" on, press Fill down one row until the column is full. Watch the row numbers in each formula.

   Interactive · Fill a formula down — try it in the lesson

   Takeaway: a copied formula keeps its shape and moves its references by the same number of rows and columns it moved.

7. 6
   Absolute references: lock a cell with $Sometimes every row must use the same cell: one tax rate, one exchange rate, one target. Copy =B3(1+B1) down and the reference to B1 moves too — to B2, B3, B4 — and the results go wrong. A $ locks the part of the address it stands in front ofInteractiveHands-on · 5 min

   Read this step

   Sometimes every row must use the same cell: one tax rate, one exchange rate, one target. Copy =B3*(1+B1) down and the reference to B1 moves too — to B2, B3, B4 — and the results go wrong.

   A $ locks the part of the address it stands in front of:

   Reference	When copied
   B1	column and row both move (relative)
   $B$1	nothing moves (absolute)
   B$1	the row stays, the column moves
   $B1	the column stays, the row moves

   You don't type the dollars. Put the cursor on the reference in the formula bar and press F4 (Windows; Cmd+T on a Mac): each press cycles B1 → $B$1 → B$1 → $B1 → B1.

   Try it: fill the formula down with plain B1 first and look at C4 to C6. Then press F4 until the rate is locked, and fill down again. The 20% VAT rate is only an example.

   Interactive · Lock the VAT rate — try it in the lesson

   Takeaway: when every row needs the same cell, lock it with $ before you copy.

8. 7
   IF: let the sheet decideIF puts a decision in a cell. It takes three parts, separated by commas: =IF(test, value if true, value if false) · =IF(B3>=$B$1,"Bonus","No") reads: if the sales in B3 are at least the target in B1, show Bonus, otherwise show No.InteractiveHands-on · 5 min

   Read this step

   IF puts a decision in a cell. It takes three parts, separated by commas:

   =IF(test, value if true, value if false)

   =IF(B3>=$B$1,"Bonus","No") reads: if the sales in B3 are at least the target in B1, show Bonus, otherwise show No. Text goes in double quotes; numbers and cell references don't.

   The tests compare two values: = equal, <> not equal, > greater, < less, >= at least, <= at most. The difference between > and >= matters exactly at the boundary — someone who sold exactly the target.

   Try it: move the target and watch who gets a bonus. Then set the target to 1000 and switch between >= and >: what happens to Jo?

   Interactive · Who reaches the target? — try it in the lesson

   Takeaway: =IF(test, if true, if false) — and check what your test does at the boundary.

9. 8
   The four errors you'll meet, and how to fix themWhen a formula can't produce a result, Excel shows an error code that starts with #. Each one tells you what went wrongInteractiveHands-on · 5 min

   Read this step

   When a formula can't produce a result, Excel shows an error code that starts with #. Each one tells you what went wrong:

   Error	What it means	Usual fix
   #DIV/0!	the formula divides by zero or by an empty cell	check the cell, or guard it: =IF(B2=0,0,A2/B2)
   #VALUE!	arithmetic on text, such as the word "ten"	put a number in the cell
   #NAME?	Excel doesn't know a name — usually a typo like SUMM, or text without quotes	fix the spelling, add the quotes
   #REF!	the formula pointed at a cell that was deleted	undo the delete, or point the formula at the right cell

   Two habits help: select the cell with the error and read its formula in the formula bar, and look at the cells it reads.

   Try it: press Why? on each error, then fix it — with the button, or yourself in the formula bar.

   Interactive · Fix the errors — try it in the lesson

   Takeaway: an error is a message — the code says what's wrong, the formula bar says where.

10. 9
    Practice: formulas on a sales sheetUse this sheet for every exercise. Work them out with the formula, then type the result Excel would show.Practice · 6 exercises

    Read this step

    Use this sheet for every exercise. Work them out with the formula, then type the result Excel would show.

    	A	B	C
    1	Rep	Sales	Target
    2	Dana	1200	1000
    3	Omar	800	1000
    4	Lee	1500	1200
    5	Sam	950	900
    6	Ana	600	900

    What does =SUM(B2:B6) show?

    What does =AVERAGE(B2:B6) show?

    What does =MAX(B2:B6)-MIN(B2:B6) show?

    Ana's cell B6 is cleared and left empty. What does =AVERAGE(B2:B6) show now?

    D2 holds =IF(B2>=C2,"Bonus","No") and is filled down to D6. How many cells in D2:D6 show Bonus?

    E2 holds =B2*$F$1. You copy it down to E4. What is in E4?

    • =B4*$F$1
    • =B4*$F$3
    • =B2*$F$1
    • =B4*F3