The six formulas that cover 80% of office work
SUM, AVERAGE, COUNT, MIN, MAX and IF. Learn these six and you can do most spreadsheet tasks at work.
There are over 500 functions in Excel. You will use about six of them almost every day, and the other 494 occasionally or never.
Here are the six.
1. SUM — add things up
=SUM(B2:B10)That colon means "through". B2:B10 is "B2 through B10", all nine cells.
2. AVERAGE — the typical value
=AVERAGE(B2:B10)Careful: averages lie when there's one extreme value. Nine people earning $30,000 and one earning $5,000,000 gives an "average" salary of $527,000, which describes nobody in the room.
3. COUNT and COUNTA — how many?
=COUNT(B2:B100)counts cells containing numbers.=COUNTA(B2:B100)counts cells that aren't empty (text included).
A very common need: how many of these are above 100?
=COUNTIF(B2:B100, ">100")4 & 5. MIN and MAX — smallest and biggest
=MIN(B2:B10)
=MAX(B2:B10)Boring, and you'll use them constantly. Cheapest supplier, biggest order, earliest date.
6. IF — the decision maker
This is the one that makes a spreadsheet feel intelligent.
=IF(B2>=40, "Pass", "Fail")Read it out loud as a sentence: "If B2 is 40 or more, write Pass, otherwise write Fail."
The structure is always three parts separated by commas:
=IF( the question , what to do if yes , what to do if no )More examples:
=IF(C2>1000, C2*0.9, C2) Give 10% off orders over 1000
=IF(D2="", "Missing", "OK") Flag blank cells
=IF(E2>TODAY(), "Upcoming", "Past")The three errors you'll meet
You will see these. They're not disasters, they're messages.
| Error | What it means | Usual fix |
|---|---|---|
#DIV/0! | You divided by zero or by an empty cell | Wrap it: =IF(C2=0,"",B2/C2) |
#VALUE! | You did maths on text | Check for a stray letter or space |
#NAME? | Excel doesn't recognise the function name | You misspelled it — check SUM not SUMM |
Putting it together
A tiny sales sheet using five of the six:
Total sales =SUM(C2:C50)
Average order =AVERAGE(C2:C50)
Number of orders =COUNT(C2:C50)
Biggest order =MAX(C2:C50)
Status =IF(SUM(C2:C50)>100000,"Target hit","Keep going")That's a real management report. It took five lines.
Too Long; Didn’t Read
- SUM, AVERAGE, COUNT, MIN, MAX and IF cover the large majority of office spreadsheet work.
- Use ranges like B2:B10 instead of long chains of plus signs — chains silently break when rows are inserted.
- IF has exactly three parts: the question, the yes answer, the no answer.
Your tiny task
Take any list of numbers you have — marks, expenses, cricket scores — and add five cells below it: total, average, count, biggest, and an IF that says "Good" or "Needs work" based on a threshold you choose.
It takes a few minutes and it’s the bit that makes the lesson stick.
Quick check
0 of 3 answered
Three questions. Get one wrong and you’ll get a hint — there’s no penalty and you can try again straight away.
Your score is saved to your dashboard.
Finished reading? Tick it off.
Create a free account to save your progress, streak and badges.
Up next: The dollar sign that fixes everything ($)
Advertisement
Ad space (lessonFooter)
Add your AdSense IDs to .env and real ads appear here.
Ads keep the free courses free. Go Pro to remove them.