Excel for Data Analysis: Formulas That Matter

Excel has hundreds of functions, but a small set does most of the work in data analysis. The examples below use a typical setup: a sales table formatted as an Excel Table named Sales, with columns OrderDate, Region, Product, Units and Revenue, and a lookup table named Products with Product and Category. The formulas are shown with the behaviour Microsoft documents for each function; results depend on your data, so no calculated values are shown.

First, turn ranges into Tables

Before any formula, select the data and press Ctrl+T to make it a Table. Tables expand automatically as rows are added, and formulas can use structured references such as Sales[Revenue] instead of $E$2:$E$5000. Formulas become readable, and they stop breaking when the data grows. Every example below assumes Tables.

1. Lookups with XLOOKUP

Bringing a category from the product table into the sales table:

=XLOOKUP([@Product], Products[Product], Products[Category], "Not found")

XLOOKUP searches the lookup range and returns the item from the return range for the first match. The fourth argument, if_not_found, replaces the #N/A error with a clear label, and the default match mode is an exact match (Microsoft Support: XLOOKUP). Unlike VLOOKUP, it does not break when columns are inserted and can look to the left.

If your version of Excel lacks XLOOKUP, INDEX with MATCH does the same job:

=INDEX(Products[Category], MATCH([@Product], Products[Product], 0))

The 0 in MATCH requests an exact match. Always check how many rows return "Not found": unmatched lookups are one of the most common silent errors in spreadsheet analysis.

2. Conditional totals and counts with SUMIFS and COUNTIFS

Revenue for the North region in January 2026:

=SUMIFS(Sales[Revenue], Sales[Region], "North",
        Sales[OrderDate], ">=" & DATE(2026, 1, 1),
        Sales[OrderDate], "<"  & DATE(2026, 2, 1))

SUMIFS adds values that meet several criteria at once (Microsoft Support: SUMIFS). Note the date criteria: "on or after the first of the month and before the first of the next" includes every time of day on the last date, which "less than or equal to the 31st" would miss if dates carry times. COUNTIFS uses the same pattern to count rows, for example orders above 1,000 in a region:

=COUNTIFS(Sales[Region], "North", Sales[Revenue], ">1000")

Put the criteria in cells rather than typing them into formulas, such as a region in H1 and a month start date in H2, and a single formula becomes a reusable summary grid.

3. Cleaning text properly

Imported text often carries extra spaces that make identical names group separately. TRIM removes all spaces except single spaces between words, but Microsoft notes it was designed for the standard space character and does not remove the non-breaking space, character 160, which is common in data copied from web pages (Microsoft Support: TRIM). Replace it first:

=TRIM(SUBSTITUTE([@Product], CHAR(160), " "))

Combine with UPPER or PROPER when capitalisation varies. Clean in a helper column, check the result, then use the cleaned column everywhere.

4. Handling errors without hiding problems

IFERROR returns a value you choose when a formula produces an error, and the formula's result otherwise (Microsoft Support: IFERROR). It is useful for a known, expected case, such as a price per unit when units can be zero:

=IFERROR([@Revenue] / [@Units], "")

Used everywhere, though, it hides real problems: a missing lookup or a text value in a number column disappears into a blank. Prefer targeted handling, such as XLOOKUP's if_not_found or an explicit IF([@Units] = 0, "", [@Revenue] / [@Units]), so unexpected errors still show.

5. Dynamic arrays: UNIQUE and FILTER

In current versions of Excel, some functions return a whole range that spills into neighbouring cells. UNIQUE returns the distinct values in a range (Microsoft Support: UNIQUE):

=SORT(UNIQUE(Sales[Region]))

FILTER returns the rows that meet criteria you define (Microsoft Support: FILTER), for example all orders above 1,000, with a message when none qualify:

=FILTER(Sales, Sales[Revenue] > 1000, "No orders above 1,000")

Together with SUMIFS, they build live summary tables: a spilled list of regions next to a SUMIFS column that refers to it updates automatically when new regions appear. Older versions of Excel may not include these functions, so check what your organisation uses before sharing workbooks.

6. Readable formulas with LET

Long formulas that repeat the same calculation are hard to check. LET assigns names to intermediate results inside a formula, like variables in programming (Microsoft Support: LET):

=LET(
    revenue, SUMIFS(Sales[Revenue], Sales[Region], H1),
    units,   SUMIFS(Sales[Units],   Sales[Region], H1),
    IF(units = 0, "", revenue / units)
)

Each total is calculated once, the final line reads like a sentence, and a reviewer can check each part.

Habits that keep spreadsheet analysis trustworthy

• Keep raw data, calculations and outputs on separate sheets.
• Never type numbers into formulas that others might need to change; put assumptions in labelled cells.
• Reconcile totals between your formulas and a pivot table built on the same data.
• Count unmatched lookups and errors instead of hiding them.
• Document what each summary includes and excludes in a note beside it.

Pivot tables are often the fastest way to cross-check formula results; pivot tables in Excel covers them. For repeatable cleaning of imported data, Excel Power Query records each step so it can be refreshed.

Excel basics, formulas and functions, VLOOKUP, INDEX-MATCH and IF functions, and pivot tables are part of the Excel module of the Data Analytics course.

Sources

• XLOOKUP function (Microsoft Support) — Microsoft Support
• SUMIFS function (Microsoft Support) — Microsoft Support
• TRIM function (Microsoft Support) — Microsoft Support
• IFERROR function (Microsoft Support) — Microsoft Support
• FILTER function (Microsoft Support) — Microsoft Support
• UNIQUE function (Microsoft Support) — Microsoft Support
• LET function (Microsoft Support) — Microsoft Support