The Excel formulas to know before a job skills test
You don't need hundreds of formulas to do well on an Excel test. A small set covers most of what basic and intermediate assessments ask for. Here's what each one does, with an example you can try.
The essentials
Expect these on almost any test. Each one takes a range of cells and returns a single answer.
| Formula | What it does | Example |
|---|---|---|
| SUM | Adds up numbers | =SUM(B2:B6) |
| AVERAGE | Finds the average | =AVERAGE(B2:B6) |
| MIN and MAX | Find the lowest and highest | =MAX(B2:B6) |
| COUNT | Counts cells containing numbers | =COUNT(B2:B6) |
| COUNTA | Counts cells that aren't empty | =COUNTA(A2:A6) |
| ROUND | Rounds to a number of decimals | =ROUND(B2, 2) |
Formulas that make decisions
IF checks a condition and returns one of two results. These three are a common step up from the basics.
| Formula | What it does | Example |
|---|---|---|
| IF | Returns one result if true, another if false | =IF(B2>=70, "Pass", "Fail") |
| COUNTIF | Counts the cells that meet a condition | =COUNTIF(C2:C20, "Boston") |
| SUMIF | Adds the values that meet a condition | =SUMIF(C2:C20, "Boston", D2:D20) |
Looking things up
Lookups find a value in a table, such as a price for a product code. VLOOKUP has been around for decades, so it still comes up often. XLOOKUP is simpler, but it's only in newer versions, so learn both.
| Formula | What it does | Example |
|---|---|---|
| VLOOKUP | Searches the first column of a table and returns a value from another column | =VLOOKUP(A2, F2:H20, 3, FALSE) |
| XLOOKUP | Searches one range and returns from another | =XLOOKUP(A2, F2:F20, H2:H20) |
| IFERROR | Shows your own result instead of an error | =IFERROR(VLOOKUP(A2, F2:H20, 3, FALSE), "Not found") |
The FALSE at the end of VLOOKUP asks for an exact match. Leaving it out is one of the most common mistakes, because the formula can return a wrong answer without any error.
Relative and absolute references
When you copy a formula, its cell references shift. That's usually what you want, but not when every row should point to the same cell, like a tax rate. Dollar signs lock a reference in place: $B$2 always means B2, wherever you copy it.
In the desktop app on Windows, pressing F4 while editing a reference cycles through the options. Tests often check this indirectly, by asking you to copy a formula that only works if the reference is locked.
More guides
DeskReady is an independent practice tool, not affiliated with any software vendor or employer. Tests vary, so use this as a general guide.