Excel Formulas for Data Analysis: 18 You'll Actually Use
In this article
- The sample data
- Quick reference: 18 Excel formulas for data analysis
- Summarize with conditions
- Look up and combine data
- Categorize with logic
- Build live lists with dynamic arrays
- Work with dates and text
- Make results cleaner and safer
- Before you trust a formula: a quick checklist
- Common Excel formula mistakes
- 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.
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:
| Row | A: Order ID | B: Order Date | C: Region | D: Product | E: Units | F: Revenue |
|---|---|---|---|---|---|---|
| 2 | 1001 | 1/5/2026 | West | Notebook | 12 | $96 |
| 3 | 1002 | 1/12/2026 | East | Backpack | 3 | $135 |
| 4 | 1003 | 1/20/2026 | South | Desk Lamp | 5 | $150 |
| 5 | 1004 | 1/28/2026 | West | Backpack | 4 | $180 |
| 6 | 1005 | 2/3/2026 | Midwest | Pen Set | 10 | $120 |
| 7 | 1006 | 2/10/2026 | East | Notebook | 25 | $200 |
| 8 | 1007 | 2/18/2026 | West | Desk Lamp | 2 | $60 |
| 9 | 1008 | 2/25/2026 | South | Backpack | 6 | $270 |
| 10 | 1009 | 3/4/2026 | East | Pen Set | 8 | $96 |
| 11 | 1010 | 3/15/2026 | West | Desk Lamp | 20 | $600 |
Total revenue is $1,907 from 95 units. A second sheet named Products holds a small lookup table in A1:C5:
| A: Product | B: Category | C: Unit Price |
|---|---|---|
| Notebook | Paper | $8 |
| Backpack | Bags | $45 |
| Desk Lamp | Lighting | $30 |
| Pen Set | Paper | $12 |
Quick reference: 18 Excel formulas for data analysis
| # | Formula | Use it to | Result in our example |
|---|---|---|---|
| 1 | SUMIFS | Add values that meet one or more conditions | $936 of West revenue |
| 2 | COUNTIFS | Count rows that meet conditions | 4 West orders |
| 3 | AVERAGEIFS | Average with conditions | $234 per West order |
| 4 | MAXIFS / MINIFS | Find the largest or smallest value with conditions | $200 / $96 in the East |
| 5 | MEDIAN | Find the typical value when there are outliers | $142.50 |
| 6 | XLOOKUP | Pull a value from another table | "Lighting" |
| 7 | IFERROR | Replace an expected error with a clean value | "No orders" |
| 8 | IF | Label rows with a yes/no rule | 5 "Large" orders |
| 9 | IFS | Assign several tiers without nesting | 1 A, 4 B, 5 C |
| 10 | UNIQUE | List distinct values | 4 regions |
| 11 | FILTER | Return only the rows you need | 3 orders of $200 or more |
| 12 | SORT | Order a range or a formula result | 1010, 1008, 1006 |
| 13 | TEXT | Turn numbers and dates into labels | "Jan 2026" |
| 14 | YEAR, MONTH, EOMONTH | Work with reporting periods | 1/31/2026 |
| 15 | TRIM + PROPER | Clean messy text | "Jordan Lee" |
| 16 | ROUND | Round a value for real, not just on screen | 11.97 |
| 17 | LET | Name the parts of a long formula | 11.97 |
| 18 | SUBTOTAL | Sum 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.
- 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
- 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
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 ID | Order Date | Region | Product | Units | Revenue |
|---|---|---|---|---|---|
| 1006 | 2/10/2026 | East | Notebook | 25 | $200 |
| 1008 | 2/25/2026 | South | Backpack | 6 | $270 |
| 1010 | 3/15/2026 | West | Desk Lamp | 20 | $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.
How to Make a Budget in 6 Steps (With Real Examples)
How to Improve Your Credit Score: 10 Steps That Work
Debits and Credits Explained: Rules, Examples and Cheat Sheet