Free Training & Career Tips... Subscribe to Get Weekly Career Tips
By Subscribing You are Agreeing to Terms and Conditions
The Excel functions every manager should know are SUM, SUMIF/SUMIFS, AVERAGE, IF, nested IF, VLOOKUP, XLOOKUP, INDEX/MATCH, COUNTIF/COUNTIFS, CONCAT/TEXTJOIN, IFERROR and the core date functions. Together, these Excel formulas cover most of the everyday calculations behind budgets, headcount reports, stock counts and deadline tracking. When your team needs structured practice, BOTI’s Microsoft Office and Excel training courses run from beginner to advanced level.
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 a skimmable reference you can share with your staff. It also shows 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 happen when people type values in by hand instead of summing a range, or scan a list by eye instead of looking it up. If a finance pack, stock count or leave tracker is built on manual workarounds, it breaks as soon as the data changes. Someone (often you) then spends an afternoon working out why a total is wrong.
Formula fluency removes that fragility. A team that knows the right function uses it automatically and builds workbooks that update themselves. It also stops re-checking numbers the spreadsheet could check for it. The result is faster reporting, fewer mistakes and decisions you can stand behind.
Quick win for managers: before you choose a course, ask BOTI to help you map 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 that searches left or right and is 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 do most of the work in almost every report. SUM gives the total, AVERAGE gives the typical figure and COUNTIF answers “how many of these meet my criteria?”. A team member who can build a summary with only these three is already saving you hours of manual counting.
The decision-makers: IF and nested IF
IF turns a spreadsheet from a calculator into a tool that reacts to your data. It lets a cell show “Over budget” automatically, or mark a deal as “won”. Nested IFs extend this into bands, which is useful for grading, commission tiers or a red/amber/green status. Once you stack more than three or four, a lookup table is usually cleaner.
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, such as a price from a product list or a manager’s name from a staff register. You keep one master list instead of re-typing the same facts everywhere.
VLOOKUP is still common in existing business workbooks, but it only looks to the right and breaks when columns move. XLOOKUP (Microsoft 365) fixes both problems and is easier to read. INDEX/MATCH remains a robust option for complex models and older versions of Excel. If your team learns only one new thing this quarter, make it a reliable lookup. For a short, focused session on modern lookups, see the 3-hour Excel Power Session: XLOOKUP, PivotTables and Dynamic Arrays masterclass.
The finishers: IFERROR, TEXTJOIN and date functions
IFERROR keeps reports client-ready by hiding 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. These matter to any manager watching service levels or project timelines.
From formulas to PivotTables
Once your team is fluent with formulas, the natural next step is the PivotTable. It is Excel’s quickest way to summarise large data sets without writing formulas. Drag a field into “Rows” and a number into “Values”, and you have totals by region, month or product. A PivotTable can turn a long system export into a one-page management summary in minutes, which is why it appears in almost every advanced Excel course.
For teams still finding their feet, the Microsoft Excel Beginners to Intermediate course (2 days) covers cell references, the order of operations and writing your first formulas with confidence. When you are ready to summarise data at scale, the Excel Pivot Tables, Dashboards and Power Pivot course (2 days) takes it further.
Train the whole team, not just one person
Knowing that these formulas exist is one thing. Having a team that uses them consistently is another. The risk in many businesses is the “spreadsheet hero”: one person builds everything, and when they go on leave or resign, the knowledge goes with them.
Building the same skills across the whole team fixes that. BOTI runs practical Excel training for South African teams in-house at your premises, at BOTI’s venue in Sunningdale, Johannesburg, or live online for distributed staff. Sessions can be built around your own workbooks, so people practise on the reports they use every day rather than generic exercises.
If you need an accredited route for computer skills, BOTI has applied to the QCTO for accreditation of the End User Computing skills programmes, including the Intermediate End User Computing skills programme (SP-240202). Enrolment opens only once the QCTO confirms the accreditation, and you can register interest now. The short Excel courses in this guide are professional skills programmes, not accredited qualifications.
There is also a funding angle worth knowing. The Skills Development Levy you already pay is 1% of the leviable amount, which is your total remuneration bill.[1] Employers whose leviable amount over the next 12 months will not exceed R500,000 are exempt.[1] On the B-BBEE scorecard, the amended generic Codes set skills development targets as a share of the leviable amount: 3.5% on learning programmes for black people and 2.5% on bursaries for black students at higher education institutions, 6% in total.[2] Sector codes may set different targets, and how a particular training spend is recognised depends on your own verification. BOTI does not promise grants or points.
Ready to lift your team’s Excel skills? Request a quote or book a callback and we will tailor a programme to your team’s level and reporting needs. Prefer to scope the gaps first? Ask BOTI to help you assess your team’s current Excel level before you book.
Frequently asked questions
What are the most useful Excel functions for managers?
For most managers the most useful function is a lookup: VLOOKUP, or XLOOKUP if you have Microsoft 365. A lookup lets you keep one master list and pull from it everywhere, which removes the biggest source of duplicated, out-of-date data in business spreadsheets. SUMIFS, COUNTIFS and IF come close behind.
Should my team learn VLOOKUP or XLOOKUP?
Learn both. XLOOKUP is easier, more flexible and the better default in Microsoft 365. VLOOKUP still appears in many existing workbooks and older versions of Excel, so your team will meet it regularly and needs to read it confidently.
How long does it take a team to learn these Excel formulas?
A focused team can usually get comfortable with the core formulas in this list in one to two days of structured training. Lookups and nested logic take a little more practice, which is why hands-on sessions using your own workbooks tend to work 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 already 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. Most companies pay for staff training from their skills development budget, and training spend can count towards the skills development element of the B-BBEE scorecard, depending on the learners and your sector code. BOTI does not promise grants or points. Request a quote and we can talk through how to plan the training.
Build these Excel skills with BOTI
To take a team from first formulas to confident everyday use of the functions in this guide, look at Microsoft Excel Beginners to Advanced, a 3-day course that moves delegates from the basics to more advanced Excel work. Call BOTI on 011 882 8853 or email [email protected]. Training runs in-house at your premises, at BOTI’s venue in Sunningdale, Johannesburg, or live online. This course is not accredited. It is delivered as a professional skills programme, and delegates receive a BOTI certificate of attendance.
Sources
- Skills Development Levy – South African Revenue Service (SARS), 2026.
- Codes of Good Practice on B-BBEE: Code Series 300, Statement 300 – Skills Development (Government Gazette 42496) – Department of Trade and Industry, 31 May 2019.