Spreadsheet function guides
Short, plain-English guides to the formulas people use most at work. Each one has a worked example, the mistakes to watch for, and a sheet to try it on.
Looking things up
- INDEX and MATCH: a simple worked exampleHow INDEX and MATCH work together to look up a value in any column, left or right, with a worked example in plain steps, common mistakes and a sheet to practise on.
- UNIQUE and FILTER: lists that update themselvesHow UNIQUE lists each value once and FILTER pulls out the rows that match, how the answers spill into the cells below, and how to count them. With a practice sheet.
- VLOOKUP: a simple worked exampleHow VLOOKUP finds a value in one table and brings back a matching value from another column, with a worked example, the common mistakes and a sheet to practise on.
- XLOOKUP: a simple worked exampleHow XLOOKUP finds a value in one column and brings back the matching value from another, even to the left, with a worked example and a sheet to practise on.
Adding up and counting
- AVERAGEIFS: the average of the rows that matchHow AVERAGEIF and AVERAGEIFS work out the average of only the rows that match one or more conditions, with a worked example and a sheet to practise on.
- COUNTIF and COUNTIFS: counting rows that matchHow COUNTIF counts the cells that match a condition, and COUNTIFS several conditions, with a worked example, common mistakes and a sheet to practise on.
- SUMIFS: adding up the rows that matchHow SUMIFS adds up only the rows that match all your conditions, such as one region in one month, with a worked example, common mistakes and a sheet to practise on.
Decisions and errors
- #N/A in Excel: what it means and how to fix itWhy VLOOKUP, XLOOKUP and MATCH show #N/A, the five usual causes from extra spaces to numbers stored as text, and how to fix each, with a sheet to practise on.
- #SPILL! in Excel: what it means and how to fix itWhy Excel shows #SPILL!, from cells in the way to merged cells and tables, how to fix each cause, and how to get a single answer instead. With a practice sheet.
- IFERROR: showing a message instead of an errorHow IFERROR shows a blank, a zero or a message instead of #DIV/0! or #N/A, when to use it and when not to, with a worked example and a sheet to practise on.
- The IF function: a simple worked exampleHow the IF function shows one result when something is true and another when it isn't, with a worked example, the common mistakes and a sheet to practise on.
Formula basics
- Absolute references: what the $ does, and the F4 keyWhy a formula goes wrong when you copy it down, how the $ sign locks a reference, and how F4 adds it for you, with a worked example and a sheet to practise on.
- Excel formula not calculating: showing as text, or adding to 0Why Excel shows a formula instead of the answer, why SUM gives 0, and how to fix numbers stored as text and manual calculation, with a sheet to practise on.
- Percentages in Excel: of a total, change and increaseHow to work out a percentage of a total, a percentage change and a percentage increase, with worked numbers first, common mistakes and a sheet to practise on.
- ROUND, ROUNDUP and ROUNDDOWNHow ROUND, ROUNDUP and ROUNDDOWN work in Excel, why rounding with a formula differs from formatting, and when to always round up, with a sheet to practise on.
Text
- Joining text: &, CONCAT and TEXTJOINHow to join first names and surnames, or any text, with &, CONCAT and TEXTJOIN, including spaces, commas and dates, with a worked example and a sheet to practise on.
- TRIM: removing extra spaces and tidying textHow TRIM removes extra spaces from names and codes in Excel, how to fix capitals with PROPER, and what to do when TRIM doesn't work, with a sheet to practise on.
Dates and times
- Age and length of service in Excel: DATEDIFHow to work out someone's age or length of service in whole years, or years and months, with DATEDIF, with worked dates, common mistakes and a sheet to practise on.
- Hours worked in Excel: shifts, breaks and timesheetsHow to work out hours worked in Excel from start and end times, turn them into decimal hours for pay, take off unpaid breaks and handle night shifts.
- Working days between two dates: NETWORKDAYSHow to count working days between two dates in Excel with NETWORKDAYS, leave out bank holidays, and add working days with WORKDAY, with a sheet to practise on.
Work tasks
- Attendance percentage in Excel, and persistent absenceHow to work out pupils' attendance percentage in Excel from sessions attended, flag persistent absence at 90% or below, and find the share who are PA.
- Excel test for a job interview: what to expect and how to practiseWhat an Excel test at interview usually covers, from SUM and IF to VLOOKUP, SUMIFS and pivot tables, how the tests work, and free practice marked as you type.
- Grade boundaries in Excel: marks to gradesHow to turn marks into grades in Excel with a grade boundary table and VLOOKUP or XLOOKUP approximate match, instead of long nested IFs, with a sheet to practise on.
- Holiday entitlement in Excel: part-time staff and startersWork out UK statutory holiday in Excel: 5.6 weeks capped at 28 days, pro rata for part-time staff, a share for people who join mid-year, and days left to book.
- The Bradford Factor in ExcelHow to work out the Bradford Factor in Excel: spells of absence squared times days lost, straight from an absence log with COUNTIF and SUMIF. With a practice sheet.
- VAT in Excel: adding it, taking it off and roundingHow to add 20% VAT to a price, take VAT off a price that includes it, and round VAT to the penny in Excel, with worked numbers and a sheet to practise on.