Side-by-side comparisons of similar Excel functions and tools, each with a worked example and a clear recommendation.
20 articles
Use MEDIAN to describe a typical value when a few very large or very small numbers could skew the result, such as salaries, house prices or order values. Use AVERAGE when the values are fairly even, or when you need a number that multiplies back to the total, as in budgets. When the two differ a lot, report both.
Use & or CONCAT to join a few pieces of text, and TEXTJOIN when every piece gets the same separator or some cells may be empty. TEXTJOIN adds the separator for you and can skip blanks. CONCATENATE is the older version of CONCAT, kept for compatibility. CONCAT and TEXTJOIN need Excel 2019 or later.
COUNT counts only cells that hold numbers, including dates. COUNTA counts every cell that isn't empty: numbers, text, TRUE or FALSE, and errors. COUNTBLANK counts the empty cells. Use COUNT for numeric data, COUNTA for "how many entries" and COUNTBLANK for "how many are missing". All three work in every Excel version.
Excel on Mac and Windows has the same formulas and almost the same features. Most shortcuts swap Ctrl for ⌘ (Ctrl+C becomes ⌘C), but some differ: AutoSum is ⌘⇧T, locking a reference is ⌘T, editing a cell is Control+U or fn+F2, and the number-format shortcuts keep Control. Power Pivot is Windows-only.
For job tests, the core skills are the same in Excel and Google Sheets: cell references, SUM, IF, COUNTIFS, SUMIFS, VLOOKUP, XLOOKUP and pivot tables work almost identically. The differences are in details: FILTER takes different arguments, error messages differ, Sheets adds QUERY, and some shortcuts and features are Excel-only. Practise in the tool the test uses.
Use AutoFilter (Ctrl+Shift+L) to look at a subset of rows quickly: it hides the other rows in place and works in every Excel version. Use the FILTER function when you need the matching rows in a separate, live list that updates by itself, such as a report or a dashboard. FILTER needs Excel 2021, Excel 2024 or Microsoft 365.
FIND and SEARCH both return the position of some text inside a cell. FIND is case-sensitive and treats * and ? as normal characters. SEARCH ignores capitals and supports the wildcards * and ?. Use SEARCH for most "does this cell contain a word" checks, and FIND when capital letters matter.
Use IFNA around lookups such as VLOOKUP, MATCH and INDEX MATCH: it replaces only #N/A ("not found"), so real mistakes like #REF! still show. Use IFERROR when other errors are expected and harmless, such as #DIV/0! from dividing by zero. IFERROR hides every error, including your own typos, so use it with care.
Yes, INDEX MATCH is still worth learning. XLOOKUP is shorter and has a built-in "not found" message, so use it for new files when everyone has Excel 2021, Excel 2024 or Microsoft 365. INDEX MATCH works in every version, appears in many existing workbooks, and still comes up in interviews.
Use XMATCH in Excel 2021, Excel 2024 or Microsoft 365: it defaults to an exact match, can search from the last row up, and finds the next smaller or larger value without sorted data. Use MATCH with 0 as its third argument when the file must work in older Excel. Both return a position, usually fed into INDEX.
IFS is better for most new formulas with three or more results: it lists each test and result in order, with no nested brackets. Nested IF is better when the file must work in Excel 2016 or older, or when you need a built-in "otherwise" result. With IFS, add TRUE as the last test to catch everything else.
Use a pivot table to explore data and build a summary fast: drag fields, regroup in seconds and show percentages of the total. Use SUMIFS when a report needs totals in fixed cells that update by themselves, such as a dashboard or a monthly template. Pivots need a refresh after the data changes; SUMIFS recalculates automatically.
A relative reference like A1 moves when you copy a formula, so each row reads its own cells. An absolute reference like $A$1 never moves, so every copy reads the same cell, such as a tax rate. A mixed reference like $A1 or A$1 locks only the column or only the row, which you need for grids.
ROUND rounds to the nearest value, so 2.5 becomes 3 and 2.4 becomes 2. ROUNDUP always rounds away from zero, and ROUNDDOWN always rounds toward zero, whatever the next digit is. Formatting a cell to show fewer decimals is different: it only changes what you see, and Excel still calculates with the full value.
SUMIF adds up numbers that meet one condition. SUMIFS adds up numbers that meet one or more conditions, and every condition must be true. The other big difference is argument order: SUMIF puts the range to add last, while SUMIFS puts it first. Both work in every current version of Excel.
Use TODAY() when you need the current date, such as for ages, days overdue or due-date checks. Use NOW() only when the time of day matters too, such as hours since a ticket was opened. TODAY returns a whole number; NOW adds the time as a decimal, which can make date comparisons and day counts come out wrong.
Use the UNIQUE function when you want a list of distinct values that stays live and leaves your data untouched, such as a list of customers for a summary. Use Remove Duplicates when you really want to delete repeated rows from the data itself, such as cleaning an export once. UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365.
The best way to learn Excel for a job is hands-on practice: typing formulas yourself, making mistakes and fixing them, because tests and real work make you produce the answer from an empty cell. Videos are useful for seeing what a feature can do, but watching someone else type doesn't build the recall and speed a test needs.
Use VLOOKUP for quick lookups to the right in a table that won't change shape. Use INDEX MATCH when you need to look left, when columns may be inserted, or when you want a two-way lookup. Both work in every version of Excel, so INDEX MATCH is the flexible choice when XLOOKUP isn't available.
Use XLOOKUP if everyone who opens the file has Excel 2021, Excel 2024 or Microsoft 365: it can look left, defaults to an exact match and doesn't break when columns move. Use VLOOKUP when the file must work in older versions such as Excel 2019, or when a job test asks for it by name.