Excel functions and skills library

Find the formula, shortcut, or tool that solves a real workplace task.

• VLOOKUP — Find an exact match in a table and return a related value from another column.
• HLOOKUP — Search across a horizontal table and return a value from a selected row.
• XLOOKUP — Use a modern lookup that can search in either direction and handle missing results.
• INDEX and MATCH — Combine two flexible functions to locate values without relying on column positions.
• Two-way lookups — Match a row and column to retrieve a value from a pricing or planning matrix.
• SUM — Add values across a range for totals, budgets, and sales reports.
• SUMIF — Add values that meet one condition, such as a department or product.
• SUMIFS — Total values using several business conditions at the same time.
• AVERAGE — Calculate the arithmetic mean of a useful set of values.
• COUNT and COUNTA — Count numeric records or all non-empty cells in a range.
• COUNTIF and COUNTIFS — Count records that match one or several criteria.
• MIN and MAX — Identify the smallest and largest values in a dataset.
• LARGE and SMALL — Return ranked values such as the second-highest sale or third-lowest cost.
• ROUND and percentages — Calculate rates and present results with sensible precision.
• CONCAT and TEXTJOIN — Combine names, codes, or labels into one clean text value.
• TEXTBEFORE and TEXTAFTER — Extract text on either side of a delimiter such as a dash or email symbol.
• LEFT, RIGHT, MID and LEN — Extract useful parts of product codes, references, and account numbers.
• FIND and SEARCH — Locate a word or character inside a longer text value.
• SUBSTITUTE — Replace known text patterns while cleaning imported information.
• TRIM and CLEAN — Remove unwanted spaces and non-printing characters from imported data.
• UPPER, LOWER and PROPER — Standardise inconsistent text case in names and reference fields.
• IF — Turn a business rule into one result when true and another when false.
• IFS and SWITCH — Handle several outcomes without building an unreadable chain of nested formulas.
• AND, OR and NOT — Combine or reverse logical tests for more precise decisions.
• IFERROR — Replace expected formula errors with a useful fallback message or value.
• Absolute and mixed references — Keep important rows or columns fixed when copying formulas.
• Excel keyboard shortcuts — Build speed with navigation, selection, editing, and formatting shortcuts.
• Drop-down lists — Create controlled inputs that make workbooks safer and easier to use.
• Conditional formatting — Highlight exceptions, deadlines, and performance patterns automatically.
• Format Painter — Copy a consistent visual format without rebuilding it manually.
• Wildcard characters — Use *, ? and ~ when matching incomplete or variable text.
• TODAY and date calculations — Create reports that respond to the current date and calculate deadlines.
• PivotTables — Summarise larger datasets quickly by category, time, or owner.
• FILTER, SORT and UNIQUE — Build live lists and reports that expand automatically.
• Excel charts and dashboards — Turn reliable summaries into clear visuals for decision-makers.
• Excel interview test practice — Prepare for timed spreadsheet tasks using realistic business scenarios.
• MO-200 skills practice — Practise the workbook, data, formula, and chart skills tested at associate level.
• MO-201 expert skills practice — Build confidence with advanced formulas, data analysis, and workbook controls.