Excel Formulas for Data Analysis: 30 Formulas With Examples

wandex

Topic — Data Analytics

Published — November 8, 2026

About 20 Excel formulas cover most day-to-day data analysis: summarising with SUMIFS and COUNTIFS, looking up values with XLOOKUP, cleaning text with TRIM and TEXT functions, and handling errors with IFERROR. This guide groups the essential Excel formulas for data analysis by task, with a worked example for each, using one small sales dataset so you can practise as you read.

The sample dataset

Create a sheet with these columns and fill 20 to 30 rows with your own sample values. Turn it into an Excel Table with Ctrl + T; tables expand automatically when you add rows, so your formulas keep working.

A: Date	B: City	C: Product	D: Units	E: Price	F: Revenue
05-01-2026	Pune	Laptop	3	52000	=D2*E2
06-01-2026	Mumbai	Mouse	12	650	=D3*E3

The examples below use column letters for clarity. In a real table you can use structured references such as Sales[Revenue], which are easier to read.

1. Summarising data

SUM, AVERAGE, MIN, MAX and COUNT

=SUM(F2:F100)        total revenue
=AVERAGE(D2:D100)    average units per order
=MAX(F2:F100)        biggest single order
=COUNT(D2:D100)      number of numeric entries
=COUNTA(B2:B100)     number of non-empty cells

SUMIFS, COUNTIFS and AVERAGEIFS

These are the workhorses of analysis. They add, count or average only the rows that meet one or more conditions.

=SUMIFS(F:F, B:B, "Pune")                          revenue from Pune
=SUMIFS(F:F, B:B, "Pune", C:C, "Laptop")          Pune laptop revenue
=COUNTIFS(D:D, ">=10")                              orders with 10+ units
=AVERAGEIFS(E:E, C:C, "Mouse")                      average mouse price
=SUMIFS(F:F, A:A, ">="&DATE(2026,1,1), A:A, "<"&DATE(2026,2,1))   January revenue

Put the condition, such as the city name, in a cell and refer to that cell instead of typing it into the formula. Then one change updates the whole report.

2. Looking up values

XLOOKUP (recommended)

Suppose a second sheet lists each product with its category. To bring the category into your sales table:

=XLOOKUP(C2, Products!A:A, Products!B:B, "Not found")

XLOOKUP searches left or right, returns an exact match by default, and lets you set a value when nothing is found. It is available in Microsoft 365 and Excel 2021 or later.

VLOOKUP (for older versions)

=VLOOKUP(C2, Products!A:B, 2, FALSE)

Always use FALSE as the last argument for an exact match. VLOOKUP can only look to the right of the lookup column, and it breaks if someone inserts a column, which is why XLOOKUP replaced it.

INDEX and MATCH

=INDEX(Products!B:B, MATCH(C2, Products!A:A, 0))

The classic flexible alternative that works in every version and is still common in interviews.

3. Logic and conditions

=IF(F2>=50000, "High", "Normal")
=IFS(F2>=100000, "Large", F2>=20000, "Medium", TRUE, "Small")
=IF(AND(B2="Pune", D2>5), "Priority", "")
=IFERROR(E2/D2, 0)

IFERROR replaces errors such as #DIV/0! or #N/A with a value you choose. Use it carefully: hiding every error can also hide real data problems.

4. Cleaning text

Real data arrives messy: extra spaces, inconsistent capitals, codes joined together. These formulas fix most of it.

=TRIM(B2)                     remove extra spaces
=PROPER(B2)                   pune -> Pune
=UPPER(C2) / =LOWER(C2)       change case
=LEFT(A2, 4)  =RIGHT(A2, 3)   take characters from either end
=MID(A2, 3, 2)                take characters from the middle
=LEN(B2)                      count characters
=TEXTJOIN(", ", TRUE, B2:B5)  join values with a separator
=SUBSTITUTE(B2, "-", " ")      replace characters

In Microsoft 365, TEXTSPLIT splits text into columns with a formula, and the Data > Text to Columns tool does the same in any version.

5. Working with dates

=TODAY()                       today's date
=YEAR(A2)  =MONTH(A2)          parts of a date
=TEXT(A2, "mmm-yyyy")          Jan-2026, useful for grouping
=EOMONTH(A2, 0)                last day of that month
=NETWORKDAYS(A2, TODAY())      working days between two dates
=DATEDIF(A2, TODAY(), "m")     complete months between dates

If a date will not calculate, it is probably stored as text. Check whether it is aligned left, then convert it with DATEVALUE or Text to Columns.

6. Dynamic array formulas (Microsoft 365)

These return many values at once and update automatically, which makes them excellent for quick analysis.

=UNIQUE(B2:B100)                          list of distinct cities
=SORT(UNIQUE(C2:C100))                    sorted product list
=FILTER(A2:F100, B2:B100="Pune")          all Pune rows
=SORTBY(C2:C100, F2:F100, -1)             products by revenue, highest first
=XLOOKUP(MAX(F2:F100), F2:F100, C2:C100)  product with the biggest order

A powerful pattern is a mini report: list cities with UNIQUE, then put SUMIFS next to it pointing at that list.

7. Ranking and percentages

=RANK.EQ(F2, F$2:F$100)          rank of each order by revenue
=F2/SUM(F$2:F$100)               share of total (format as %)
=(F3-F2)/F2                      growth from one period to the next
=PERCENTILE.INC(F2:F100, 0.9)    90th percentile order value

The dollar signs make an absolute reference, so the range does not shift when you copy the formula down. Press F4 to toggle them.

Formulas vs pivot tables vs Power Query

Tool	Best for
Formulas	Custom calculations, flags, lookups, reports that must update cell by cell
Pivot tables	Fast summaries by category and date with drag and drop, no formulas needed
Power Query	Repeatable cleaning and combining of files or tables before analysis

Analysts use all three. Learn the formulas above first, because they also help you understand what a pivot table is doing.

Practice exercise

Clean the City column with TRIM and PROPER.
Add a Month column with TEXT(A2, "mmm-yyyy").
Build a summary with UNIQUE cities and SUMIFS revenue.
Find the top product with SORTBY or XLOOKUP.
Flag orders above the 90th percentile with IF.

Then build the same summary with a pivot table and check that the numbers match. Excel is the first step on our data analyst roadmap, followed by SQL and Power BI.

Frequently asked questions

Which Excel formulas are most important for a data analyst?

SUMIFS, COUNTIFS, XLOOKUP or VLOOKUP, INDEX and MATCH, IF and IFERROR, TRIM, TEXT and date functions. Add UNIQUE and FILTER if you use Microsoft 365.

Is XLOOKUP better than VLOOKUP?

Yes, in most cases. It looks in any direction, defaults to an exact match and handles missing values cleanly. Learn VLOOKUP too, because older files and interviews still use it.

Do data analysts still use Excel?

Yes. Even teams that use SQL, Python and Power BI rely on Excel for quick checks, ad-hoc analysis and sharing results with business users.

Why does my formula show the formula text instead of a result?

The cell is probably formatted as Text. Change the format to General, then re-enter the formula.

Practise SQL next with our SQL interview questions for data analysts, or explore more data analytics tutorials.

wandex

The Radium4 team writes practical, hands-on guides for people learning DevOps, cloud, data, Python, testing and cyber security. Every tutorial is tested on real systems before it is published.

All articles by this author