Excel Formulas Data People Use Every Week Explained

Open any analyst's workbook and the same two dozen formulas appear again and again. A lookup to attach a name to an ID. A conditional sum for the total by region. A trimmed, cleaned, split piece of text. A date turned into a month. An IFERROR somewhere hiding a problem. The list is short, and the difference between a useful workbook and a fragile one lies in knowing how each formula behaves at the edges: what happens when the key is missing, when the cell is blank, when the number is really text. This guide walks through the formulas that data people use every week, with the cases that bite, and points to the spokes that go deeper on each. It is written for people who move data between Excel and databases, so each section notes the SQL form as well. For translating whole workbooks the other way, translating Excel formulas to SQL is the companion.

How to read the formulas in this guide

Formulas are shown as text, starting with an equals sign, with cell references in the A1 style. Sample data is small enough to check by eye. Where a result depends on the Excel version, the text says so. Dynamic array functions (FILTER, SORT, UNIQUE, XLOOKUP, LET) need Microsoft 365 or Excel 2021 and later; in older versions they return #NAME?. If your recipients use Excel 2016 or 2019, stay with the older equivalents or keep the logic outside the workbook.

Lookups

A lookup finds a row by a key and returns a value from another column. It is the formula people use most and trust least.

**XLOOKUP** is the modern answer:

=XLOOKUP(A2, Customers!A:A, Customers!C:C, "not found")

It takes the key, the range to search, the range to return and an optional value for "not found". Unlike VLOOKUP, it does not depend on column position, so inserting a column does not break it. It defaults to an exact match, which is what you almost always want. It can search from the bottom (search_mode of -1), return multiple columns at once and use wildcards if you ask for them (XLOOKUP examples that replace VLOOKUP pain goes through them).

**VLOOKUP** still runs most workbooks:

=VLOOKUP(A2, Customers!A:C, 3, FALSE)

The fourth argument must be FALSE for an exact match. Leave it out, and VLOOKUP does an approximate match on sorted data, returning a plausible wrong answer. The column number 3 counts from the left of the range, so adding a column at the wrong place changes the result silently.

**INDEX with MATCH** works in every version and looks in any direction:

=INDEX(Customers!C:C, MATCH(A2, Customers!A:A, 0))

The 0 makes MATCH exact. This is the safe pattern for workbooks that must open in Excel 2016.

All lookups share a failure mode: the key has trailing spaces, a different case in a case-sensitive system, or a different type (the number 417 versus the text "417"). A lookup that returns #N/A for rows that look identical is almost always a type or whitespace problem. Clean the key with TRIM and TEXT or convert both sides to the same type (trimming spaces and non breaking spaces covers the invisible characters).

The SQL form is a join: LEFT JOIN customers c ON c.id = o.customer_id. A lookup that finds several matching rows returns the first, where a join returns all of them and multiplies rows, which is a difference worth knowing (VLOOKUP and XLOOKUP as SQL joins).

Conditional totals

**SUMIFS, COUNTIFS and AVERAGEIFS** aggregate rows that meet conditions.

=SUMIFS(Sales!D:D, Sales!B:B, "North", Sales!C:C, ">="&DATE(2026,9,1), Sales!C:C, "<"&DATE(2026,10,1))

The sum range comes first, then pairs of criteria range and criterion. Conditions are combined with AND. Comparison operators sit inside the quotes and join to cell values or functions with &. The half-open date pattern at the end avoids losing the last day of the month. Wildcards * and ? work in text criteria, and a leading ~ escapes them.

Pitfalls: the ranges must be the same size; a criterion of "<>" means "not empty"; a criterion against text that looks like a number can match both; and COUNTIF(range, "*") counts only text, not numbers. For an OR condition, add two SUMIFS or use SUMPRODUCT.

**SUMPRODUCT** handles what SUMIFS cannot: arithmetic across arrays before summing, OR conditions, weighted averages.

=SUMPRODUCT((Sales!B2:B500="North")*(Sales!D2:D500)*(Sales!E2:E500))

It multiplies the arrays element by element and adds the result. It is slower on large ranges and it is flexible enough that people call it the Swiss Army formula (SUMPRODUCT as a Swiss Army formula). The SQL form of all of these is SUM(...) FILTER (WHERE ...) or a GROUP BY (SUMIF and SUMIFS as GROUP BY).

Dynamic arrays

Microsoft 365 formulas can return many values, which "spill" into the cells below.

=UNIQUE(Sales!B2:B500)
=SORT(UNIQUE(Sales!B2:B500))
=FILTER(Sales!A2:E500, Sales!B2:B500="North", "no rows")
=SORTBY(Sales!A2:E500, Sales!D2:D500, -1)

UNIQUE lists distinct values, SORT and SORTBY order them, FILTER returns the rows that meet a condition. They replace helper columns, copy-paste of filtered ranges and a good deal of Power Query work for small tasks. The result updates when the source changes, and a reference to the whole spilled range uses the hash operator: =SUM(F2#).

A spill needs empty cells to expand into; otherwise the formula shows #SPILL!. Dynamic arrays also matter when a script reads the file, because the spilled cells are stored as cached values with a hidden array formula in the anchor cell (dynamic array functions FILTER, SORT and UNIQUE covers the file side, and array formulas and why they break imports covers why a loader sees something unexpected).

Text cleanup

Data from other systems arrives with spaces in the wrong places and several fields in one cell.

FIND is case-sensitive and SEARCH is not and accepts wildcards. TRIM does not touch the non-breaking space that web pages and PDFs leave behind, which is why a clean-looking value fails a lookup. TEXTSPLIT, TEXTBEFORE and TEXTAFTER arrived with the newer functions; in older versions use LEFT, MID and FIND together (TEXTSPLIT, TEXTJOIN and text cleanup formulas goes through the patterns). In SQL the equivalents are trim, split_part, substring, regexp_replace and string_agg (text functions in Excel and SQL).

Dates and periods

Excel dates are serial numbers, which makes arithmetic easy and display tricky (Excel stores dates as serial numbers).

EOMONTH is the reliable way to find month boundaries, and it returns a serial number that you format as a date. DATEDIF is undocumented in the formula helper but works; use it for completed months or years between dates. NETWORKDAYS counts weekdays and optionally subtracts a list of holidays. DATEVALUE and text dates depend on locale, so prefer DATE with explicit parts (date formulas for month ends and business days has the full set). The SQL forms are date_trunc, interval arithmetic and generate_series (date functions in Excel and SQL).

Logic

**IF and IFS choose between values. AND, OR and NOT build conditions. SWITCH** maps a value to a result.

=IF(AND(D2 > 0, E2 = "active"), "pay", "hold")
=IFS(D2 >= 90, "A", D2 >= 80, "B", D2 >= 70, "C", TRUE, "F")
=SWITCH(E2, "A", "active", "I", "inactive", "unknown")

Nested IF chains past three levels are a sign to use IFS, SWITCH or a lookup table. A lookup table beats a long formula because a person can read and edit it. The SQL form is CASE WHEN (IF and IFS as CASE).

Handling errors

IFERROR(value, fallback) returns the fallback when the value is any error. It is convenient and it hides problems, which is why overuse is the commonest fault in inherited workbooks. If a lookup fails because a key is missing, you may want a visible "not found". If it fails because a column was deleted, you want the error to shout.

=IFNA(XLOOKUP(A2, Customers!A:A, Customers!C:C), "unknown customer")
=IF(B2 = 0, "", A2 / B2)

IFNA catches only #N/A, so a #REF! still shows. Testing the divisor before dividing is clearer than wrapping everything. Handle errors at the place they have meaning, and leave the rest visible (error handling with IFERROR and safer alternatives lays out the options).

Making formulas readable

A long formula that repeats a sub-expression is hard to read and slow. **LET** names intermediate values.

=LET(
  total, SUMIFS(Sales!D:D, Sales!B:B, A2),
  budget, XLOOKUP(A2, Budget!A:A, Budget!B:B, 0),
  IF(budget = 0, "", (total - budget) / budget)
)

Each name is computed once and used where needed. **LAMBDA** goes further: it defines a reusable function you can name in Name Manager and call like a built-in (LET and LAMBDA for readable formulas). Both need Microsoft 365 or Excel 2021 and later, and a file that uses them opens with errors in older versions, so decide who your readers are.

Other readability habits help more than any function: named ranges and tables for inputs (named ranges and tables explained), one formula pattern per column, no constants buried in formulas, and a notes column or sheet that says what a calculation is for.

Performance

Formulas that scan whole columns, volatile functions (NOW, TODAY, OFFSET, INDIRECT, RAND) and thousands of lookups into unsorted ranges slow a workbook. Limit ranges to the data or to a table, avoid volatile functions in big sheets, compute a lookup once in a helper column and reuse it, and prefer XLOOKUP with a bounded range. When a workbook takes more than a few seconds to recalculate, the cause is usually a handful of formulas, which you can find by switching calculation to manual and measuring (slow Excel formulas over many rows).

Formulas and the tools that read them

A script that reads a workbook sees formulas as text and cached values as results, and which one it gets depends on the library and the mode (what happens when code reads Excel formulas). A workbook saved by a program that does not calculate has formulas but no cached values, and reading it with data_only returns blanks (handling Excel formulas and cached values). If a pipeline depends on the numbers, open and save the file in Excel or recalculate it with LibreOffice before reading.

Moving the logic to SQL

Formulas are a good home for a calculation until the data grows, the logic is shared or the numbers must be auditable. Signs it is time: lookups into sheets of 100,000 rows, the same formula copied across ten files, a result that must be reproduced exactly next quarter, or a second person who cannot follow the chain. Then translate: lookups become joins, SUMIFS becomes GROUP BY with FILTER, text functions become string functions, dates become date_trunc and interval (when SQL beats formulas gives the criteria). Keep Excel as the front end where people want to explore, and let the database produce the numbers.

Checking a formula before you trust it

Four habits catch most errors. Test the formula on a row where you know the answer. Test it on the awkward rows: a blank, a zero, a text where a number should be, a value at the boundary of a condition. Use Formulas, Evaluate Formula to step through a nested expression. And compare a total to an independent source: a control total from the database or the ledger. A formula that passes on the first row and has never been tried on the last is a common source of silent error.

A worked example: a month-end sales summary

The formulas make more sense together, so take a task most analysts meet: a sheet of September orders arrives from the database export, and the regional lead wants a summary by region with the top customer for each and a flag where the month missed budget.

The export has one row per order line, with columns for order date, region, customer ID, quantity and unit price. The first job is cleaning. Region names come with stray spaces, so a helper column holds =TRIM(B2) and everything downstream uses it. Customer IDs arrive as text in one file and numbers in another, so a second helper column converts them with =TEXT(C2, "00000") and the lookup table uses the same form. The line value is =D2*E2, rounded where the finance rule says it should be.

Next comes the list of regions. Instead of typing them, a cell holds =SORT(UNIQUE(RegionCol)), and the list updates when a new region appears in the data. Beside it, =SUMIFS(ValueCol, RegionCol, F2, DateCol, ">="&Start, DateCol, "<"&End) gives revenue for the month, where Start and End are two cells holding the first day of September and the first day of October. Putting the dates in cells and not inside the formula means that next month's analyst changes two cells, not forty formulas.

The top customer per region needs a little more. One way is =LET(rows, FILTER(Orders, RegionCol=F2), cust, INDEX(rows,,3), vals, INDEX(rows,,6), tot, SUMIFS(vals, cust, cust), INDEX(cust, MATCH(MAX(tot), tot, 0))), which filters the region's rows, totals each customer's rows and returns the customer with the highest total. It is dense, and it is also the case where the SQL form is easier to read: a GROUP BY with row_number() OVER (PARTITION BY region ORDER BY sum(value) DESC). The reasonable approach is to try the formula, notice that it takes five minutes to explain, and decide whether the workbook or the database should own that logic.

The budget flag is the easy part. =XLOOKUP(F2, Budget!A:A, Budget!B:B, 0) brings the budget in, =G2-H2 gives the variance, and =IF(H2=0, "no budget", IF(G2<H2, "behind", "on track")) gives the flag. The IF(H2=0, ...) test is there on purpose, so a region without a budget is reported as such and not as a division by zero or a silent pass.

Finally, the checks. Total revenue across regions should equal the sum of the line values, which is a one-cell comparison. The row count of the data should equal the count the database reported. If either comparison fails, the summary does not go out. A summary that is pretty and wrong is the usual outcome of skipping this step.

The whole sheet uses about ten distinct formulas. Learn those well, with their edge cases, and the rest of Excel's function list is a reference to consult.

Mistakes that show up again and again

A handful of errors account for most of the wrong numbers analysts find in inherited workbooks.

Ranges that stop short. A SUM over D2:D200 when the data now runs to row 240. Tables and whole-column references avoid it; so does a count check that compares the number of rows to the range size.

Numbers stored as text. A column that looks numeric left-aligns, sums to zero or is skipped by SUMIFS. The tell is the small green triangle in the cell corner. Converting with VALUE or multiplying by one fixes the formula, and fixing the import fixes the cause (fixing numbers stored as text in Excel).

Approximate match by accident. A VLOOKUP without the last argument, or MATCH without the zero. It works on sorted data and quietly gives wrong answers on unsorted data.

Hard-coded values inside formulas. A tax rate typed as *1.2 in four hundred cells. When the rate changes, half the cells are updated.

Copying a formula that needed a fixed reference. =B2/B10 filled down becomes =B3/B11. The dollar sign in $B$10 fixes it, and so does naming the cell.

Circular references. A cell that depends on itself, directly or through a chain, which Excel warns about and which imported workbooks sometimes arrive with (fixing circular reference warnings in imported workbooks).

Volatile functions on large ranges. OFFSET and INDIRECT recalculate on every change, and a workbook that takes ten seconds to react to a keystroke often has a few hundred of them.

Merged cells and layout rows in data. A formula that expects a table gets a report with titles and subtotals (Excel merged cells and hidden rows).

Each of these has a simple defense once you know to look: structured tables, explicit exact-match arguments, input cells for constants, and checks that compare to something outside the sheet.

Choosing between a formula, a pivot, Power Query and SQL

The same result can often be reached four ways, and the right choice depends on who will maintain it and how it will run.

A formula suits a calculation inside a sheet that people read and adjust: a variance column, a flag, a lookup of a name. It is visible, it updates instantly, and it stops scaling when it needs to touch tens of thousands of rows many times over.

A pivot table suits interactive summaries, where the reader wants to drag fields around. It carries a cache of the data inside the file, which is a size and a privacy consideration (pivot tables and their SQL equivalents).

Power Query suits repeatable reshaping: combine files, unpivot, split columns, change types, and refresh with a button. The steps are recorded, which makes it a good middle ground between clicking and coding (Power Query for repeatable Excel cleanups).

SQL suits anything large, shared, scheduled or audited. It lives in a repository, runs without a person and returns the same answer every time for the same inputs.

A sensible rule: begin with the simplest tool that works, and move up when you hit one of its limits. A workbook that began as five formulas and has become a hundred, with three tabs of helper columns, is asking to be a query.

Working on formulas as a team

Workbooks are shared, and formulas are code that happens to live in cells. A few team habits keep them healthy. Put inputs, calculations and outputs on separate sheets, so a reviewer can see what drives what. Use the same column for the same meaning on every sheet. Keep a "Notes" sheet that lists the assumptions, the source of each input and the date of the last change. Lock cells that contain formulas and leave inputs editable, with the understanding that protection is a guard against accidents (protecting formula cells from edits). Use a naming scheme for ranges and tables, such as tbl_Orders and inp_TaxRate, so that formulas read like sentences.

When a formula changes, say so. A change log row with the date, the cell range and the reason takes ten seconds and saves a meeting. For anything that feeds money or a regulator, treat the workbook like code: keep it in version control as a binary if you must, review changes by diffing the formulas exported to text, and keep dated copies.

Learning the list well

You do not need to memorize two hundred functions. A practical way to learn the list in this guide is to build one small workbook that uses each formula on invented data, with a row where it works and a row where it fails. Seeing XLOOKUP return "not found" for a trailing-space key teaches more than reading about it. Add each new lesson as a row. In a month, that workbook is a reference you wrote yourself, with the traps you actually met.

Pair the practice with SQL. For each formula, write the query that does the same on the same data, and compare the results. The exercise shows where Excel and SQL agree, where they differ on blanks and ties, and which tool reads better for which task. The mapping posts in this cluster (translating Excel formulas to SQL) are the shortcut.

Versions, compatibility and who opens the file

Formulas have a release date, and your readers' Excel has a version. XLOOKUP, FILTER, SORT, UNIQUE, SEQUENCE, LET and LAMBDA appeared in Microsoft 365 and Excel 2021, with a few arriving later than the rest, and they are missing from Excel 2016 and 2019. TEXTSPLIT, TEXTBEFORE, TEXTAFTER and the stacking functions arrived later still. A workbook that uses them, opened in an older version, shows #NAME? where the results should be, and the cached values may vanish on the first recalculation.

Find out who opens the file before you pick the formulas. If the readers are in one company on a managed Microsoft 365 install, use the modern functions freely. If the file goes to customers or suppliers, assume the oldest version in circulation, or send values and keep the formulas in your own copy. Google Sheets and LibreOffice support many of the same functions with differences at the edges (differences between Sheets and Excel formulas). Test the file in the target before you send it, and keep a short compatibility note on the README sheet.

A related point is calculation mode. A workbook set to manual calculation shows stale numbers until someone presses F9, and a script that opens it without recalculating gets stale cached values. Check the mode (Formulas, Calculation Options) on inherited files, and make the mode part of the handover note.

Documenting a formula so someone else can use it

The person who inherits your workbook will not have your memory. A formula needs three things around it: what it is for, what it assumes and what would break it. A short cell comment or a note column does the job: "Revenue by region for the period in B1:B2, excludes test accounts, expects the Orders table to be refreshed first." For complex logic, a one-line example with the expected answer next to the formula works as a test: "Row 5 should be 1,250.00."

Name things. A named range called TaxRate makes =D2*TaxRate readable where =D2*Settings!$B$4 is not. A table called Orders makes =SUMIFS(Orders[Value], Orders[Region], F2) readable and keeps the range correct when rows are added. Prefer names that describe meaning over names that describe position. Review names when the workbook changes, because a stale name is worse than none.

What a good formula review looks like

When someone asks you to check a workbook, the useful review is short and systematic. Read the inputs sheet first and ask whether each value is documented and plausible. Pick three outputs and trace each back through its formulas, using Trace Precedents to see the chain. Look for ranges that stop short, hard-coded numbers inside formulas, inconsistent formulas down a column (Excel can highlight them), and any cell that mixes an input and a calculation. Recompute one total by a different route, such as a pivot or a quick query. Then write down what you checked and what you did not, so the next reviewer does not assume more than was done.

Reviews like this find the errors that cost money. The cause is almost never an exotic function. It is a range, a type, a constant or a copied formula that no longer fits its row.

When formulas are the wrong tool

Formulas are the wrong tool when the same logic must run on a schedule without a person, since a script or SQL runs unattended and a workbook does not. They are wrong for data too large for a sheet, or when many people edit the same file and overwrite each other. They are wrong when you need history of what changed. And they are wrong for logic with many branches that business owners want to review: put the rules in a table, or in code under version control. For the middle ground, where logic is repeatable and visible, Power Query is the usual answer (Power Query for repeatable Excel cleanups).

Where to go next

Start with XLOOKUP examples that replace VLOOKUP pain if lookups are your daily work, and dynamic array functions FILTER, SORT and UNIQUE if you are on Microsoft 365. For cleanup, read TEXTSPLIT, TEXTJOIN and text cleanup formulas and date formulas for month ends and business days. For structure and safety, read LET and LAMBDA for readable formulas, SUMPRODUCT as a Swiss Army formula and error handling with IFERROR and safer alternatives.

Microsoft keeps the authoritative list in Excel functions by category, and the dynamic array formulas guidance explains spilling in detail. For the SQL side, the PostgreSQL functions and operators chapter is the reference. The tools on this site run in your browser and are handy for quick conversions of small samples.