The 15 Excel Formulas That Do Most Data Analysis

Excel has around 500 functions. Analysts use about fifteen of them for the overwhelming majority of real work, and most of those fifteen come in families that share one syntax. This is a reference to that core set, organized by the job each one does, using a sales sheet with Region (A), Product (B), Units (C), Revenue (D), Date (E).

Aggregating with conditions

1. SUMIFS

Total where all criteria match. Sum range first, then range/criteria pairs.

=SUMIFS(D:D, A:A, "North", B:B, "Widget")

2. COUNTIFS

Same syntax, no sum range. "How many orders in the North over 100 units?"

=COUNTIFS(A:A, "North", C:C, ">100")

3. AVERAGEIFS / MAXIFS / MINIFS

The rest of the family. Average revenue for Widgets:

=AVERAGEIFS(D:D, B:B, "Widget")

For dates, criteria are strings with an operator: E:E, ">="&DATE(2024,2,1). See SUMIFS vs pivot tables for when to use a pivot instead.

4. SUMPRODUCT

The escape hatch when the IFS family can't express the logic — OR conditions, calculations inside the criteria, or weighted sums.

=SUMPRODUCT((A2:A100="North")*(C2:C100)*(D2:D100))

Multiplies units × revenue only where region is North. Slower on huge ranges; unmatched in flexibility.

Looking things up

5. XLOOKUP

Find a value in one column, return the same row from another. Exact match by default, looks in any direction, has a built-in not-found argument.

=XLOOKUP("G-200", Products!A:A, Products!C:C, "Not found")

XLOOKUP vs VLOOKUP covers the differences; if you're on Excel 2019 or earlier, use INDEX/MATCH.

6. INDEX / MATCH

The older two-function version, still worth reading:

=INDEX(C:C, MATCH("G-200", A:A, 0))

Reshaping data (dynamic arrays, Excel 365 / 2021)

These return multiple cells — a "spill" — and replaced a generation of helper-column tricks.

7. FILTER

Rows that match a condition, as a live table.

=FILTER(A2:E100, (A2:A100="North")*(D2:D100>1000))

Multiply conditions for AND, add them for OR. Combine with SUM for a conditional sum that a pivot can't express.

8. UNIQUE

Distinct values. =UNIQUE(A2:A100) lists the regions. =COUNTA(UNIQUE(A2:A100)) counts them — the "how many distinct customers" question in one cell.

9. SORT / SORTBY

=SORTBY(A2:B100, D2:D100, -1) — region and product ordered by revenue, descending. Wrap in TAKE(…, 10) for a top-10 list that updates itself.

10. GROUPBY (Excel 365, 2024+)

A pivot table as a formula:

=GROUPBY(A2:A100, D2:D100, SUM)

Revenue by region, spilled. Add PIVOTBY for a two-dimensional grid. If your Excel has these, a lot of the pivot-vs-formula debate goes away.

Text

11. TEXT

Number → formatted string. Essential for building labels and keys.

=TEXT(E2, "yyyy-mm")

turns a date into a month key you can group on. TEXT(D2, "$#,##0.00") for display.

12. TRIM / CLEAN / SUBSTITUTE

The cleaning trio. =TRIM(A2) removes extra spaces, CLEAN removes non-printing characters, SUBSTITUTE replaces text. Together: =VALUE(SUBSTITUTE(TRIM(D2), "$", "")) turns " $2,400.50 " into a number. More in cleaning messy data.

13. TEXTSPLIT / TEXTBEFORE / TEXTAFTER (365)

Split "Smith, John" without Text to Columns: =TEXTBEFORE(A2, ",") and =TEXTAFTER(A2, ", ").

Dates

14. EOMONTH / EDATE / DATE

=EOMONTH(E2, 0) — last day of the month, the standard month key. =EOMONTH(E2, -1)+1 — first day. =EDATE(E2, 12) — one year later. =DATE(2024, 2, 1) — build a date safely (never type "2/1/2024" into a criteria; locales differ).

15. YEAR / MONTH / WEEKNUM / NETWORKDAYS

Extract parts for grouping; NETWORKDAYS(start, end) for business-day durations.

Two that hold everything together

IFERROR — =IFERROR(formula, "") — but use it sparingly: it hides every error, including typos. Prefer XLOOKUP's built-in not-found, or IFNA, which only catches #N/A.

LET — name intermediate values inside one formula so it's readable:

=LET(net, D2-F2, margin, net/D2, IF(margin<0.2, "Low", "OK"))

Putting five of them together

A typical Monday question: "top 5 products by revenue in the North this quarter, with their share of North revenue." In modern Excel that's one spill formula and two helpers:

=LET(
  north, FILTER(A2:E1000, (A2:A1000="North")*(E2:E1000>=DATE(2024,1,1))*(E2:E1000<DATE(2024,4,1))),
  products, UNIQUE(INDEX(north,,2)),
  totals, SUMIFS(INDEX(north,,4), INDEX(north,,2), products),
  ranked, SORTBY(HSTACK(products, totals), totals, -1),
  TAKE(ranked, 5)
)

FILTER narrows to the region and quarter, UNIQUE finds the products, SUMIFS totals each, SORTBY ranks, TAKE keeps five, and LET keeps it readable. Divide the totals by SUM(totals) for share. In Excel 2019 or earlier the same answer is a pivot table with a Region filter and a Top 10 value filter — see the pivot table tutorial.

A note on the alternative

Every formula above encodes a question you already know how to ask: total by region, distinct customers, top 10, revenue by month. If you have the question but not the formula, upload the sheet to ChatExcel and ask it directly — "top 10 products by revenue in the North", "how many distinct customers per month". Then ask "how do I do that in Excel?" and you get the formula from this list, written against your actual columns. That's the fastest way to learn the fifteen: one real question at a time.