Excel formulas cheat sheet for job tests
These are the formulas that show up most on office Excel tests. Each row gives the syntax, a working example you can copy, and the mistake that costs people points. Every function also works in Google Sheets, except where noted.
Feel free to print this page or link to it. Every example below uses the same sample sales table: reps in column A, sales in B2:B7, targets in D, regions in E and hire dates in H.
Math and counting
| Formula | Example | Watch out for |
|---|---|---|
| SUM | =SUM(B2:B7) | Including the header row |
| AVERAGE | =AVERAGE(B2:B7) | Blank cells are skipped, zeros are counted |
| MIN / MAX | =MAX(B2:B7)-MIN(B2:B7) | Spread needs both functions |
| COUNT / COUNTA | =COUNTA(A2:A7) | COUNT only counts numbers |
| ROUND | =ROUND(AVERAGE(B2:B7),0) | Formatting hides decimals but doesn't remove them |
| Percent of total | =B2/SUM($B$2:$B$7) | Lock the total with $ before copying down |
Conditions
| Formula | Example | Watch out for |
|---|---|---|
| IF | =IF(B2>D2,"Yes","No") | Text results need quotes |
| COUNTIF | =COUNTIF(B2:B7,">1000") | The condition goes in quotes |
| COUNTIFS | =COUNTIFS(E2:E7,"East",B2:B7,">1000") | Ranges must be the same size |
| SUMIF | =SUMIF(E2:E7,"East",B2:B7) | The range to add comes last |
| SUMIFS | =SUMIFS(B2:B7,E2:E7,"East",B2:B7,">1000") | The range to add comes first |
| AVERAGEIF | =AVERAGEIF(E2:E7,"East",B2:B7) | Same order as SUMIF |
| IFERROR | =IFERROR(B2/F2,0) | It hides every error, including real mistakes |
Lookups
| Formula | Example | Watch out for |
|---|---|---|
| VLOOKUP | =VLOOKUP(E2,$J$2:$L$4,2,FALSE) | Always end with FALSE for an exact match |
| XLOOKUP | =XLOOKUP(E3,J2:J4,K2:K4) | Not in Excel 2019 or earlier |
| INDEX + MATCH | =INDEX(B2:B7,MATCH("Priya",A2:A7,0)) | MATCH needs 0 for an exact match |
Text and dates
| Formula | Example | Watch out for |
|---|---|---|
| TRIM + PROPER | =PROPER(TRIM(G2)) | TRIM doesn't remove non-breaking spaces from web data |
| UPPER / LEFT | =UPPER(LEFT(A2,3)) | LEFT counts characters, not words |
| Join text | =A2&" - "&E2 | Add the spaces yourself |
| YEAR | =YEAR(H2) | Format the result as a number, not a date |
| DATEDIF | =DATEDIF(H2,$K$6,"Y") | Start date first, and it won't show in autocomplete |
References in one line
B2 moves when you copy. $B$2 never moves. $B2 keeps the column, B$2 keeps the row. Press F4 in Excel to cycle through them. More on references.
Questions people ask
Which Excel formulas are on most job tests?
SUM, AVERAGE, IF, COUNTIF and SUMIF show up on almost every office test. Analyst tests add VLOOKUP or XLOOKUP, SUMIFS and IFERROR.
Do these formulas work in Google Sheets?
Yes. Every formula on this page works the same way in Google Sheets.
Practice on a real spreadsheet
CellReady grades your formula the moment you press Enter and tells you why a wrong answer is wrong. The first 7 lessons are free, with no sign-up to start.
Related guides
What an Excel test for a job interview usually covers by role, the formats employers use, and a practical plan to prepare in a week.
How to use SUMIF in Excel, the argument order that trips people up, using cell references in the condition, and a live exercise.
How VLOOKUP works in Excel, why you should always end it with FALSE, how to lock the table with $, its limits, and a live exercise.