Cheat sheetOffice
Excel formulas
The Excel formulas you actually reach for — lookups, logic, text, totals and dates — each with an example and a one-line explanation.
Last updated
Lookups
| Formula | What it does |
|---|---|
| =VLOOKUP(A2, D:E, 2, FALSE) | Finds A2 in column D and returns the matching value 2 columns right — FALSE demands an exact match. |
| =XLOOKUP(A2, D:D, E:E, "not found") | The modern replacement for VLOOKUP — looks left or right, and names a fallback instead of erroring. |
| =INDEX(E:E, MATCH(A2, D:D, 0)) | MATCH finds the row, INDEX returns the value there — works looking left, which VLOOKUP can't. |
| =HLOOKUP(A2, D1:H2, 2, FALSE) | Same as VLOOKUP but searches across a row instead of down a column. |
Logic
| Formula | What it does |
|---|---|
| =IF(A2>100, "High", "Low") | Tests a condition and returns one value if true, another if false. |
| =IFS(A2>100, "High", A2>50, "Mid", TRUE, "Low") | Checks conditions in order and returns the first match — reads cleaner than nested IFs. |
| =IFERROR(A2/B2, "n/a") | Runs the formula, and if it errors, shows your fallback instead of #DIV/0! or #N/A. |
| =IF(AND(A2>0, B2>0), "Both", "No") | AND requires every condition true; swap in OR to require just one. |
| =IF(A2>100, "High", IF(A2>50, "Mid", "Low")) | Nested IF for more than two outcomes — IFS above does the same job with less punctuation. |
Text
| Formula | What it does |
|---|---|
| =CONCAT(A2, " ", B2) or =A2&" "&B2 | Joins text from separate cells into one string — the & version works in every version of Excel. |
| =LEFT(A2, 3) =RIGHT(A2, 3) =MID(A2, 2, 3) | Pulls characters from the start, the end, or from a middle position you specify. |
| =TRIM(A2) | Strips leading, trailing and repeated internal spaces — fixes text pasted from elsewhere. |
| =LEN(A2) | Counts how many characters are in the cell, spaces included. |
| =TEXT(A2, "0.00") | Formats a number as text using a pattern you choose, e.g. currency or padded decimals. |
| =SUBSTITUTE(A2, "-", "/") | Replaces every occurrence of one piece of text with another inside a cell. |
| =TEXTSPLIT(A2, ",") | Splits one cell into several by a delimiter, spilling the pieces across neighboring cells. |
Numbers & totals
| Formula | What it does |
|---|---|
| =SUMIF(A:A, "West", B:B) | Adds up column B only where the matching row in column A meets the condition. |
| =SUMIFS(C:C, A:A, "West", B:B, ">100") | Like SUMIF but with multiple conditions across multiple columns, all must be true. |
| =COUNTIF(A:A, "West") =COUNTIFS(A:A, "West", B:B, ">100") | Counts rows matching one condition, or matching several at once. |
| =AVERAGEIF(A:A, "West", B:B) | Averages column B only where the matching row in column A meets the condition. |
| =ROUND(A2, 2) | Rounds to the number of decimal places you specify — negative numbers round to the left of the decimal. |
| =SUBTOTAL(9, A2:A100) | Sums (or averages, counts, etc. by code) only the visible rows — ignores anything filtered out. |
Dates
| Formula | What it does |
|---|---|
| =TODAY() | Today's date, recalculated every time the sheet opens or changes. |
| =NOW() | Today's date plus the current time, also live-recalculated. |
| =DATEDIF(A2, B2, "Y") | The difference between two dates in whole years — swap "Y" for "M" or "D" for months or days. |
| =EOMONTH(A2, 0) | The last day of A2's month — use 1 or -1 for next or previous month. |
| =NETWORKDAYS(A2, B2) | Counts working days between two dates, excluding weekends (and holidays if you list them). |
| =WEEKDAY(A2) | Returns the day of the week as a number, 1 for Sunday by default. |
Worth remembering
- →Every formula here works the same in Google Sheets except XLOOKUP and TEXTSPLIT, which need a recent Excel (365) or Sheets — older Excel needs VLOOKUP or INDEX+MATCH instead.
- →SUBTOTAL's first argument is a function code (9 = SUM, 1 = AVERAGE, 3 = COUNTA) — it's the one formula built specifically to respect filters.