Excel Formulas for Data Analysis: 18 You'll Actually Use

Excel Formulas for Data Analysis: 18 You'll Actually Use
In this article
  1. The sample data
  2. Quick reference: 18 Excel formulas for data analysis
  3. Summarize with conditions
  4. Look up and combine data
  5. Categorize with logic
  6. Build live lists with dynamic arrays
  7. Work with dates and text
  8. Make results cleaner and safer
  9. Before you trust a formula: a quick checklist
  10. Common Excel formula mistakes
  11. Frequently asked questions

The most useful Excel formulas for data analysis are SUMIFS, COUNTIFS and AVERAGEIFS to summarize with conditions, XLOOKUP to pull data from another table, IF and IFS to categorize rows, UNIQUE, FILTER and SORT to build live lists, TEXT and date functions to report by period, and IFERROR, ROUND and LET to keep results clean and readable. Learn these 18 and you can answer most everyday business questions in a spreadsheet, without writing a line of code.

Every example below uses the same small sales table, so you can type it into a blank sheet and check each result yourself. All results were calculated on this exact data.

Before you start

Everything here works in Microsoft 365, Excel 2021 and later, and Excel for the web. Formulas use commas between arguments, the U.S. English default; if your Excel uses semicolons because of regional settings, swap them. Google Sheets has all 18 functions too, but its FILTER and SORT take slightly different arguments (see the FAQ).

The sample data

A sheet named Orders with headers in row 1 and ten orders in rows 2 to 11:

RowA: Order IDB: Order DateC: RegionD: ProductE: UnitsF: Revenue
210011/5/2026WestNotebook12$96
310021/12/2026EastBackpack3$135
410031/20/2026SouthDesk Lamp5$150
510041/28/2026WestBackpack4$180
610052/3/2026MidwestPen Set10$120
710062/10/2026EastNotebook25$200
810072/18/2026WestDesk Lamp2$60
910082/25/2026SouthBackpack6$270
1010093/4/2026EastPen Set8$96
1110103/15/2026WestDesk Lamp20$600

Total revenue is $1,907 from 95 units. A second sheet named Products holds a small lookup table in A1:C5:

A: ProductB: CategoryC: Unit Price
NotebookPaper$8
BackpackBags$45
Desk LampLighting$30
Pen SetPaper$12

Quick reference: 18 Excel formulas for data analysis

#FormulaUse it toResult in our example
1SUMIFSAdd values that meet one or more conditions$936 of West revenue
2COUNTIFSCount rows that meet conditions4 West orders
3AVERAGEIFSAverage with conditions$234 per West order
4MAXIFS / MINIFSFind the largest or smallest value with conditions$200 / $96 in the East
5MEDIANFind the typical value when there are outliers$142.50
6XLOOKUPPull a value from another table"Lighting"
7IFERRORReplace an expected error with a clean value"No orders"
8IFLabel rows with a yes/no rule5 "Large" orders
9IFSAssign several tiers without nesting1 A, 4 B, 5 C
10UNIQUEList distinct values4 regions
11FILTERReturn only the rows you need3 orders of $200 or more
12SORTOrder a range or a formula result1010, 1008, 1006
13TEXTTurn numbers and dates into labels"Jan 2026"
14YEAR, MONTH, EOMONTHWork with reporting periods1/31/2026
15TRIM + PROPERClean messy text"Jordan Lee"
16ROUNDRound a value for real, not just on screen11.97
17LETName the parts of a long formula11.97
18SUBTOTALSum only the rows visible after filtering$936

Summarize with conditions

1. SUMIFS: add up values that meet conditions

The analyst's workhorse. It answers "how much?" for any slice of your data.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Total revenue from the West region:

=SUMIFS(F2:F11, C2:C11, "West")

Result: $936 (orders 1001, 1004, 1007 and 1010). Add a second condition to get West revenue from desk lamps only:

=SUMIFS(F2:F11, C2:C11, "West", D2:D11, "Desk Lamp")

Result: $660. For a date range, put the comparison operator in quotes and join it to a date with &. February revenue:

=SUMIFS(F2:F11, B2:B11, ">="&DATE(2026,2,1), B2:B11, "<="&DATE(2026,2,28))

Result: $650 (orders 1005 to 1008). Divide by the grand total to get a share: =SUMIFS(F2:F11, C2:C11, "West")/SUM($F$2:$F$11) returns 0.4908, or 49.1% when formatted as a percentage. The West brings in almost half of all revenue.

2. COUNTIFS: count rows that meet conditions

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=COUNTIFS(C2:C11, "West") returns 4 orders. How many East orders reached at least $100?

=COUNTIFS(C2:C11, "East", F2:F11, ">=100")

Result: 2 (orders 1002 at $135 and 1006 at $200; order 1009 at $96 doesn't qualify).

3. AVERAGEIFS: average with conditions

=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

=AVERAGEIFS(F2:F11, C2:C11, "West") returns $234, the average West order ($936 ÷ 4). It works on any numeric column: =AVERAGEIFS(E2:E11, D2:D11, "Notebook") returns 18.5 units per notebook order (12 and 25).

4. MAXIFS and MINIFS: the extremes of a group

=MAXIFS(F2:F11, C2:C11, "East")
=MINIFS(F2:F11, C2:C11, "East")

Results: $200 and $96, the largest and smallest East orders. Useful for spotting the range inside each segment before you trust its average.

5. MEDIAN: the typical value when the average misleads

=AVERAGE(F2:F11)
=MEDIAN(F2:F11)

Results: an average of $190.70 but a median of $142.50. One big order (1010, $600) pulls the average up, and 7 of the 10 orders are actually below it. When you describe a "typical" order, customer or salary, check the median too. There's no MEDIANIFS in Excel, but you can combine MEDIAN with FILTER (formula 11): =MEDIAN(FILTER(F2:F11, C2:C11="West")) returns $138. Our lesson on descriptive statistics explains when to use each measure.

Look up and combine data

6. XLOOKUP: pull a value from another table

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

To add each order's product category, type this in G2 and copy it down:

=XLOOKUP(D2, Products!$A$2:$A$5, Products!$B$2:$B$5, "Not found")

In row 4 (order 1003, a desk lamp) the result is "Lighting". The dollar signs lock the lookup table so it doesn't shift as you copy. XLOOKUP also finds a single record fast: =XLOOKUP(1008, A2:A11, F2:F11) returns $270, the revenue of order 1008.

VLOOKUPThe classic
Direction
Only returns columns to the right of the lookup column
Default match
Approximate, unless you add FALSE as the last argument
Columns
Uses a column number that breaks when columns are inserted
Works in every version
XLOOKUPThe modern choice
Direction
Looks left, right, up or down
Default match
Exact
Columns
Points to a return range, so inserted columns don't break it; has a built-in "not found" value
Use it when you can

7. IFERROR: replace an expected error

=IFERROR(value, value_if_error)

There are no orders from a "North" region, so =AVERAGEIFS(F2:F11, C2:C11, "North") returns #DIV/0!. In a summary table, that's noise. Wrap it:

=IFERROR(AVERAGEIFS(F2:F11, C2:C11, "North"), "No orders")

Result: "No orders". Use IFERROR only where you know which error to expect. Wrapping every formula hides real problems, such as a typo in a range.

Categorize with logic

8. IF: label rows with a rule

=IF(logical_test, value_if_true, value_if_false)

In a helper column, flag large orders and copy the formula down:

=IF(F2>=150, "Large", "Small")

Row 2 ($96) returns "Small" and row 4 ($150) returns "Large". Down the whole column you get 5 large and 5 small orders, a new category you can count, filter or put in a pivot table.

9. IFS: several tiers without nested IFs

=IFS(F2>=300, "A", F2>=150, "B", TRUE, "C")

Excel checks the conditions in order and stops at the first one that's true, so test the largest threshold first. TRUE at the end catches everything else. Copied down, order 1010 is the only A, orders 1003, 1004, 1006 and 1008 are B, and the other five are C.

Build live lists with dynamic arrays

These formulas return several values that "spill" into the cells below and to the right. If something is in the way, you'll see #SPILL!: clear those cells and the result appears.

10. UNIQUE: list distinct values

=UNIQUE(C2:C11)

Result: West, East, South, Midwest, in order of first appearance. Wrap it to count distinct values: =COUNTA(UNIQUE(C2:C11)) returns 4. Put UNIQUE next to a SUMIFS that points to it and you have a summary table that updates itself when new regions appear.

11. FILTER: return only the rows you need

=FILTER(array, include, [if_empty])

All orders of $200 or more:

=FILTER(A2:F11, F2:F11>=200, "None")
Order IDOrder DateRegionProductUnitsRevenue
10062/10/2026EastNotebook25$200
10082/25/2026SouthBackpack6$270
10103/15/2026WestDesk Lamp20$600

For several conditions, multiply them for AND and add them for OR. West orders with at least 10 units:

=FILTER(A2:F11, (C2:C11="West")*(E2:E11>=10), "None")

Result: orders 1001 and 1010. If dates come back as five-digit numbers, format that column as a date.

12. SORT: put results in order

=SORT(array, [sort_index], [sort_order], [by_col])

Sort the big orders from largest to smallest by revenue, the 6th column of the result (-1 means descending):

=SORT(FILTER(A2:F11, F2:F11>=200), 6, -1)

Result: orders 1010 ($600), 1008 ($270) and 1006 ($200), a live "top orders" list that updates when the data changes.

Work with dates and text

13. TEXT: format numbers and dates as labels

=TEXT(value, format_text)

=TEXT(B2, "mmm yyyy") returns "Jan 2026", =TEXT(B2, "dddd") returns "Monday" and =TEXT(F11, "$#,##0.00") returns "$600.00". The result is text, which is perfect for chart labels and report titles but can't be summed. Keep the original numbers for calculations.

14. YEAR, MONTH and EOMONTH: work with periods

=YEAR(B2)
=MONTH(B9)
=EOMONTH(B2, 0)
=B11-B2

Results: 2026, 2 (order 1008 was placed in February), 1/31/2026 (the last day of the order's month) and 69 (days between the first and last order). If EOMONTH shows 46053, that's the date's serial number: format the cell as a date. YEAR and MONTH helper columns make it easy to summarize by period, and EOMONTH saves you from typing month-end dates. With 2/1/2026 in cell H1, this returns February revenue ($650) for any month you type:

=SUMIFS(F2:F11, B2:B11, ">="&H1, B2:B11, "<="&EOMONTH(H1, 0))

15. TRIM and PROPER: clean messy text

=PROPER(TRIM(A2))

Imagine a customer list where cell A2 contains " jordan LEE ". The result is "Jordan Lee": TRIM removes leading, trailing and repeated spaces, and PROPER capitalizes each word. One trap: text copied from websites often contains non-breaking spaces that TRIM ignores. Replace them first with =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). Our data cleaning lesson covers duplicates, blanks and inconsistent dates as well.

Make results cleaner and safer

16. ROUND: round the value, not just the display

=ROUND(number, num_digits)

Revenue per unit in the East is =SUMIFS(F2:F11, C2:C11, "East")/SUMIFS(E2:E11, C2:C11, "East"): $431 ÷ 36 units = 11.9722… Wrapped in =ROUND(..., 2) it becomes 11.97. Number formatting only hides decimals; the full value is still used in later math. ROUND changes the value itself, which matters when you compare results or add up rounded amounts.

17. LET: name the parts of a long formula

=LET(rev, SUMIFS(F2:F11, C2:C11, "East"),
     units, SUMIFS(E2:E11, C2:C11, "East"),
     ROUND(rev/units, 2))

Result: 11.97, the same as above, but readable. LET assigns names to intermediate results (rev, units) and uses them in the final calculation, so each piece is calculated once and your formula explains itself to the next person who opens the file.

18. SUBTOTAL: totals that respect filters

=SUBTOTAL(109, F2:F11)

Turn on filters (Data > Filter) and show only the West. =SUBTOTAL(109, F2:F11) now returns $936, while =SUM(F2:F11) still returns $1,907. Function number 109 means "sum, ignoring hidden rows"; 101 does the same for an average. The Total Row of an Excel table uses SUBTOTAL for exactly this reason.

Before you trust a formula: a quick checklist

Common Excel formula mistakes

Using VLOOKUP without FALSE as the last argument.

Use XLOOKUP, or add FALSE so VLOOKUP looks for an exact match instead of an approximate one.

Hard-coding assumptions inside formulas, like =F2*0.08.

Put the rate in its own labeled cell and reference it, so you can change it once.

Wrapping everything in IFERROR.

Handle only the errors you expect and let unexpected ones show up.

Doing math with the output of TEXT.

Use TEXT for labels and keep the original numbers and dates for calculations.

Copying formulas without checking relative references.

Lock fixed ranges with $ and spot-check the last row after copying.

New to Excel, or want to practice each of these formulas step by step? Start with our free Excel course, from your first formula to pivot tables, Power Query and macros.

Frequently asked questions

What are the most important Excel formulas for data analysis?
If you learn only four, make them SUMIFS, COUNTIFS, XLOOKUP and IF. Together with pivot tables, which aren't formulas but summarize data in seconds, they cover most day-to-day questions. See our lesson on pivot tables for data analysis next.

What's the difference between SUMIF and SUMIFS?
SUMIF handles one condition and puts the range to add last: =SUMIF(C2:C11, "West", F2:F11). SUMIFS handles one or more conditions and puts the range to add first. Since SUMIFS does everything SUMIF does, many analysts use SUMIFS everywhere for consistency.

Should I use XLOOKUP or VLOOKUP?
Use XLOOKUP if everyone who opens the file has Excel 2021, Microsoft 365 or Excel for the web. It matches exactly by default, looks in any direction and doesn't break when columns are inserted. Keep VLOOKUP for files shared with people on older versions.

Do these formulas work in Google Sheets?
Yes, all 18 exist in Google Sheets. Two behave differently: Sheets' FILTER takes each condition as a separate argument and has no "if empty" value, and Sheets' SORT uses TRUE or FALSE for ascending order instead of 1 or -1.

Why does my formula show #SPILL!?
A dynamic array formula (UNIQUE, FILTER, SORT) needs empty cells to spill its results into, and something is in the way. Clear the cells below or to the right, or move the formula. Spilled results also can't go inside an Excel table; place them next to it.

Excel formulas are one of the core skills employers test for analyst roles. To see where they fit in the bigger picture, read our step-by-step guide on how to become a data analyst, or practice these formulas on a 200-row dataset in our lesson on Excel and Google Sheets for data analysis.


Want to keep going, from formulas to pivot tables, SQL and dashboards? Our free data analytics course includes a practice workbook with deliberate errors to clean, step-by-step lessons and exercises with solutions.