Free Training & Career Tips... Subscribe to Get Weekly Career Tips
By Subscribing You are Agreeing to Terms and Conditions
The Excel formulas every manager should know are SUM, SUMIF/SUMIFS, AVERAGE, IF, nested IF, VLOOKUP, XLOOKUP, INDEX/MATCH, COUNTIF, CONCAT/TEXTJOIN, IFERROR and the core date functions. Master these and your team covers the vast majority of the calculations a business performs every day — from budgets to headcount to deadlines.
You do not need to be a power user to lead a team that uses Excel well. You need to know which functions exist, what each one is for, and when to reach for it. This guide gives you exactly that: a skimmable reference you can share with your staff, plus a sense of where formal Excel training closes the gap between “we use spreadsheets” and “we trust our spreadsheets.”
Why formula fluency matters for a team
Most spreadsheet errors are not exotic. They come from people copying values by hand instead of summing a range, or eyeballing a list instead of looking it up. When a finance pack, a stock count or a leave tracker is built on manual workarounds, it breaks the moment data changes — and someone (often you) spends an afternoon finding out why a total is wrong.
Formula fluency removes that fragility. A team that knows the right function reaches for it automatically, builds workbooks that update themselves, and stops re-checking numbers that the spreadsheet could verify. The result is faster reporting, fewer mistakes and decisions you can actually stand behind.
Quick win for managers: download the free Excel & Office Skills Audit + Team Competency Matrix to map exactly which of these formulas each team member already knows — and where the training gaps are.
The 15 Excel formulas every business team should know
Here is the working list. Each row says what the function does and, more usefully, what it is for at work. Share this table with your team as a starting reference.
| Function | What it does | What it’s for at work |
|---|---|---|
SUM | Adds a range of numbers | Totalling budgets, sales, hours, line items |
SUMIF / SUMIFS | Adds numbers that meet one or more conditions | “Total sales for the Durban region” or “spend by cost centre and month” |
AVERAGE | Returns the mean of a range | Average order value, average resolution time, average score |
IF | Returns one result if a test is true, another if false | Flagging “Over budget” vs “OK”, pass/fail, in/out of target |
Nested IF | Stacks several IF tests in one cell | Grading into bands (A/B/C), tiered commission, RAG status |
VLOOKUP | Looks up a value in a table and returns a matching column | Pulling a price, name or status from a master list by ID |
XLOOKUP | Modern lookup — left or right, exact by default | The cleaner replacement for VLOOKUP in Microsoft 365 |
INDEX / MATCH | Flexible lookup combining two functions | Lookups VLOOKUP can’t do; robust against inserted columns |
COUNTIF / COUNTIFS | Counts cells that meet conditions | “How many tickets are still open?”, headcount by department |
CONCAT / TEXTJOIN | Joins text from several cells | Building full names, addresses or reference codes from parts |
IFERROR | Replaces error messages with a tidy value | Stops #N/A and #DIV/0! cluttering reports sent to clients |
TODAY / NOW | Returns the current date/time | Live “days outstanding”, ageing reports, dashboard timestamps |
DATEDIF / NETWORKDAYS | Calculates between two dates | Tenure, project duration, working days to a deadline |
LEFT / RIGHT / MID | Extracts part of a text string | Splitting codes, pulling area codes, cleaning imported data |
ROUND | Rounds a number to set decimals | Clean currency figures, avoiding fractional-cent errors |
Start here: SUM, AVERAGE and COUNTIF
These three carry the load in almost every report. SUM totals; AVERAGE gives you the central figure; COUNTIF answers “how many of these meet my criteria?” If a team member can build a summary using only these, they are already saving you manual tallying.
The decision-makers: IF and nested IF
IF turns a spreadsheet from a calculator into a tool that reacts. It lets a cell say “Over budget” automatically, or mark a deal as “won”. Nested IFs extend this into bands — useful for grading, commission tiers or a red/amber/green status — though once you stack more than three or four, it is usually cleaner to use a lookup instead.
The connectors: VLOOKUP, XLOOKUP and INDEX/MATCH
This is the family that separates competent users from confident ones. Lookups let one sheet pull data from another — a price from a product list, a manager’s name from a staff register — so you maintain one master list instead of re-typing the same facts everywhere.
VLOOKUP is still the most-requested skill in business, but it only looks rightward and breaks when columns move. XLOOKUP (Microsoft 365) fixes both and reads more simply. INDEX/MATCH remains the most robust option for complex models. If your team only learns one new thing this quarter, make it a reliable lookup — it pays back immediately.
The finishers: IFERROR, TEXTJOIN and date functions
IFERROR keeps reports client-ready by hiding ugly error codes. TEXTJOIN and CONCAT build clean labels and references from separate fields. The date functions — TODAY, DATEDIF, NETWORKDAYS — power ageing reports, tenure calculations and deadline tracking, which matter for any manager watching SLAs or project timelines.
From formulas to PivotTables
Once your team is fluent with formulas, the natural next step is the PivotTable — Excel’s fastest way to summarise large data without writing a single formula. Drag a field into “Rows”, a number into “Values”, and you have instant totals by region, month or product. PivotTables turn a 5,000-row export into a one-page management summary in under a minute, which is why they appear in almost every advanced Excel course.
For teams still finding their feet, our Excel formulas for beginners guide covers cell references, the order of operations and how to write your first formulas without fear. When you are ready to summarise data at scale, the PivotTables for managers walkthrough takes it further.
Train the whole team, not just one person
Knowing these formulas exist is one thing; having a team that uses them consistently is another. The risk in most businesses is the “spreadsheet hero” — one person who builds everything, and whose leave or resignation takes the institutional knowledge with them.
Standardising skills across the team fixes that. BOTI delivers practical, accredited Excel training for South African teams — in-house at your offices in Johannesburg, Cape Town, Durban or Pretoria, or live online for distributed staff. Excel sits within BOTI’s accredited IT End User Computing qualification (Services SETA / MICT SETA); these unit-standard qualifications are migrating to the new QCTO system, so accredited enrolment is available now — please confirm current accreditation when you book. Sessions are built around your actual workbooks, so people learn on the reports they use every day, not generic exercises.
There is a funding angle worth knowing. SA companies can fund staff training through their Skills Development budget, and accredited training contributes to the skills-development element of your B-BBEE scorecard. The B-BBEE skills-development spend target is 6% of the leviable amount, and the Skills Development Levy you already pay (1% of payroll) is meant to be reinvested in exactly this kind of upskilling.
Ready to lift your team’s Excel skills? Request a quote or book a 15-minute callback and we will tailor a programme to your team’s level and reporting needs. Prefer to scope the gaps first? Grab the free Excel & Office Skills Audit + Team Competency Matrix.
Frequently asked questions
What is the single most useful Excel function for managers?
For most managers it is a lookup — VLOOKUP, or XLOOKUP if you have Microsoft 365. Lookups let you maintain one master list and pull from it everywhere, which removes the biggest source of duplicated, out-of-date data in business spreadsheets.
Should my team learn VLOOKUP or XLOOKUP?
Learn both. XLOOKUP is easier and more flexible and is the better default in Microsoft 365, but VLOOKUP is still everywhere in existing workbooks and on older versions, so your team will encounter it regularly and needs to read it confidently.
How long does it take a team to learn these Excel formulas?
A focused team can become comfortable with the core formulas in this list in one to two days of structured training. Lookups and nested logic take a little practice, which is why hands-on sessions using your own workbooks work far better than watching generic tutorials.
Do we need an advanced Excel course or a beginners’ one?
It depends on the team’s starting point. If people are confident with SUM, IF and basic formatting, an advanced Excel course covering lookups, INDEX/MATCH and PivotTables is the right fit. If they are still hesitant with basic formulas, start with foundational Excel training first.
Can South African companies fund Excel training for staff?
Yes. SA companies can fund staff training through their Skills Development budget, and accredited training counts towards the skills-development element of your B-BBEE scorecard (the spend target is 6% of the leviable amount). Request a quote and we can explain how to structure it.