Excel Advanced Formulas You Must Know as a Data Analyst (2026 Cheat Sheet)

Excel is not going anywhere. Data analysts live in it. Stakeholders send Excel files. Quick analyses happen in Excel. Dashboards get prototyped in Excel. Knowing advanced formulas separates analysts who work fast from those who get stuck.

This is a practical cheat sheet. Every formula here is something you will use in real analysis work. Each one comes with a real-world example, not a textbook definition. Save this. Refer to it. Practice the examples on your own data.

VLOOKUP is still around. It should not be. XLOOKUP replaces it completely. It searches a range and returns a corresponding value. It works left-to-right and right-to-left. It handles missing values gracefully. It does not break when you insert columns.

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

Real example. You have employee IDs in column A and names in column B. You want to find the name for employee ID 1042.

1=XLOOKUP(1042, A:A, B:B, "Not Found")

If the ID exists, you get the name. If not, you get "Not Found" instead of an ugly error.

XLOOKUP with a different column order. Unlike VLOOKUP, the return column does not need to be to the right of the lookup column.

1=XLOOKUP(1042, B:B, A:A)

XLOOKUP with wildcard match. Find a product name containing "Candle" anywhere.

1=XLOOKUP("*Candle*", C:C, D:D, , 2)

The match_mode 2 enables wildcard matching. This is useful for partial text searches.

XLOOKUP handles most cases. INDEX-MATCH still wins for two-way lookups, finding a value at the intersection of a row and column.

1XLOOKUP handles most cases. INDEX-MATCH still wins for two-way lookups, finding a value at the intersection of a row and column.

Real example. You have a table with months as rows and product categories as columns. Find the sales value for "March" and "Electronics."

1=INDEX(B2:M10, MATCH("March", A2:A10, 0), MATCH("Electronics", B1:M1, 0))

The first MATCH finds the row number. The second MATCH finds the column number. INDEX returns the value at that intersection.

These functions calculate aggregates based on multiple conditions. They are used constantly in real analysis.

SUMIFS sums a range based on multiple criteria.

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

Sum total sales for the North region in January 2026.

1=SUMIFS(E:E, C:C, "North", B:B, ">=01/01/2026", B:B, "<=31/01/2026")

COUNTIFS counts rows matching multiple criteria.

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

Count orders above 1000 rupees in the Electronics category.

1=COUNTIFS(E:E, ">1000", D:D, "Electronics")

AVERAGEIFS averages a range based on multiple criteria.

1=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Average order value for the West region.

1=AVERAGEIFS(E:E, C:C, "West")

The FILTER function returns an array of values that meet your criteria. It spills results into adjacent cells automatically. This is only available in Excel 365 and Excel 2021 onward.

1=FILTER(array, include, [if_empty])

Filter all orders from the Electronics category.

1=FILTER(A2:E100, D2:D100="Electronics", "No orders found")

Filter with multiple conditions. Electronics category AND order above 1000.

1=FILTER(A2:E100, (D2:D100="Electronics") * (E2:E100>1000), "No matching orders")

The asterisk acts as AND. Use plus for OR.

Filter with OR condition. Electronics OR Furniture category.

1=FILTER(A2:E100, (D2:D100="Electronics") + (D2:D100="Furniture"), "No matching orders")

SORT arranges data by one or more columns. SORTBY sorts by a column that may not be in the result.

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

Sort orders by total amount, highest first.

1=SORT(A2:E100, 5, -1)

The sort_index 5 is the fifth column in the range, TotalAmount. Minus 1 is descending order.

SORTBY sorts using a separate column.

1SORTBY sorts using a separate column.

Sort employee names by their sales amount, which is in a different range.

1=SORTBY(A2:A100, E2:E100, -1)

UNIQUE returns distinct values from a range. It can also return values that appear exactly once.

1=UNIQUE(array, [by_col], [exactly_once])

Get a list of distinct product categories.

1=UNIQUE(D2:D100)

Get distinct combinations of region and category.

1=UNIQUE(B2:C100)

Get values that appear exactly once, useful for finding anomalies.

1=UNIQUE(D2:D100, , TRUE)

Real data is dirty. Dates formatted as text. Names with extra spaces. Inconsistent casing. These functions clean data.

TRIM removes extra spaces.

1=TRIM(A2)

" John Smith " becomes "John Smith".

UPPER, LOWER, PROPER standardize text case.

1=UPPER(A2)
2=LOWER(A2)
3=PROPER(A2)

PROPER capitalizes the first letter of each word. Useful for names.

LEFT, RIGHT, MID extract parts of text.

1=LEFT(A2, 5)
2=RIGHT(A2, 3)
3=MID(A2, 3, 4)

MID starts at position 3 and extracts 4 characters.

TEXT splits text using a delimiter. Available in Excel 365.

1=TEXTSPLIT(A2, ",")

"John,Smith,30" split by comma into separate cells.

TEXTJOIN combines text from multiple cells with a delimiter.

1=TEXTJOIN(", ", TRUE, A2:C2)

Joins A2, B2, C2 with a comma and space. TRUE ignores empty cells.

Date handling is a core analyst skill. These functions extract date parts and calculate date differences.

TODAY returns the current date. It updates automatically.

1=TODAY()

EOMONTH returns the last day of a month, some number of months away.

1=EOMONTH(A2, 0)
2=EOMONTH(A2, 1)
3=EOMONTH(A2, -1)

Zero gives the end of the current month. One gives the end of next month. Minus one gives the end of last month.

DATEDIF calculates the difference between two dates in various units.

1=DATEDIF(start_date, end_date, "d")   ' days
2=DATEDIF(start_date, end_date, "m")   ' months
3=DATEDIF(start_date, end_date, "y")   ' years
4=DATEDIF(start_date, end_date, "ym")  ' months ignoring years

Find how many full months an employee has worked.

1=DATEDIF(B2, TODAY(), "m")

YEAR, MONTH, DAY, WEEKDAY extract date parts.

1=YEAR(A2)
2=MONTH(A2)
3=DAY(A2)
4=WEEKDAY(A2, 2)

WEEKDAY with second argument 2 returns Monday as 1, Sunday as 7.

TEXT formats a date as a custom text string.

1=TEXT(A2, "YYYY-MM-DD")
2=TEXT(A2, "MMMM YYYY")
3=TEXT(A2, "DDD")

IF handles single conditions. IFS handles multiple conditions without nesting. SWITCH matches exact values.

1=IF(logical_test, value_if_true, value_if_false)

Categorize order as High if above 1000, Low otherwise.

1=IF(E2>1000, "High", "Low")

IFS checks multiple conditions without nesting.

1=IFS(E2>1000, "High", E2>500, "Medium", E2>0, "Low", TRUE, "None")

SWITCH matches a value against a list and returns the corresponding result.

1=SWITCH(D2, "Electronics", "ELEC", "Furniture", "FURN", "Clothing", "CLTH", "Other")

LET assigns names to calculation results. It makes complex formulas readable and often faster because intermediate calculations run once, not repeatedly.

1=LET(name1, value1, [name2, value2], calculation)

Calculate a sales commission where the rate is 10 percent if sales exceed 50000, else 5 percent.

1=LET(
2  sales, SUM(E2:E100),
3  rate, IF(sales>50000, 0.1, 0.05),
4  sales * rate
5)

Without LET, the SUM might appear multiple times in the formula. With LET, it is calculated once and reused. This matters in large workbooks.

LAMBDA lets you create custom reusable functions without VBA. Define the formula once. Use it anywhere in the workbook.

1=LAMBDA(parameter1, parameter2, calculation)

Create a function to calculate a 10 percent commission.

Go to Formulas, Name Manager, New. Name it COMMISSION. In Refers To, enter:

1=LAMBDA(sales, sales * 0.1)

Now use COMMISSION like any other function.

1=COMMISSION(E2)

More complex example. A function to categorize sales as High, Medium, or Low.

1=LAMBDA(amount, IFS(amount>1000, "High", amount>500, "Medium", TRUE, "Low"))

Name this CATEGORIZE_SALES. Use it on any cell.

Array formulas operate on ranges and return arrays. In Excel 365, these spill automatically. In older versions, press Ctrl plus Shift plus Enter.

Extract unique values sorted alphabetically in one formula.

1=SORT(UNIQUE(D2:D100))

Filter and sort in one chain.

1=SORT(FILTER(A2:E100, E2:E100>1000), 5, -1)

This filters rows where the amount exceeds 1000, then sorts them descending by amount. One formula replaces multiple manual steps.

Nothing looks less professional than error values in a report. These functions trap errors and return something clean.

1=IFERROR(formula, value_if_error)

If a VLOOKUP or XLOOKUP might fail, wrap it.

1=IFERROR(XLOOKUP(A2, C:C, D:D), "Not Found")

IFNA specifically traps the NA error while letting other errors through. Useful when NA has a specific meaning, like no match found, but other errors need attention.

1=IFNA(XLOOKUP(A2, C:C, D:D), "No Match")

SUMPRODUCT multiplies corresponding elements in arrays and returns the sum. It can replace many array formulas and handle conditional sums without SUMIFS.

Basic weighted sum.

1=SUMPRODUCT(B2:B10, C2:C10)

Conditional sum. Sum amounts for the North region.

1=SUMPRODUCT((C2:C10="North") * E2:E10)

The condition (C2:C10="North") returns TRUE/FALSE. Multiplying by the amount converts TRUE to 1 and FALSE to 0. The result is the sum of amounts where the condition is TRUE.

Conditional count. Count orders above 1000 in Electronics.

1=SUMPRODUCT((D2:D10="Electronics") * (E2:E10>1000))

Not a formula. But more important than any single formula. Power Query is Excel's data transformation engine. It connects to files, databases, and web sources. It cleans and reshapes data without formulas. Steps are recorded and repeatable.

When to use Power Query instead of formulas. Data is too large for formulas to handle efficiently. The same cleaning steps need to run repeatedly on updated data. Data comes from multiple sources that need combining. Transformations require pivoting, unpivoting, or merging tables.

Access Power Query from the Data tab, Get Data. The interface is visual. Each transformation step appears in the Applied Steps pane. You can edit, reorder, or delete steps. Refresh pulls new data and replays all steps automatically.

Common Power Query transformations. Remove duplicates. Fill down missing values. Split columns by delimiter. Merge queries like SQL JOINs. Pivot and unpivot. Group by with aggregations. Add conditional columns.

You do not need all these formulas memorized. You need to know they exist and what they solve. The details are a search away. The awareness is what makes you fast.

Practice these on your own data. The difference between knowing a formula exists and having used it ten times is the difference between pausing to look it up and typing it without thinking. The second analyst works faster, makes fewer errors, and gets promoted.

SkillsYard's Data Analytics program covers Excel in depth alongside SQL, Power BI, and Python. Live mentorship. Real projects. Placement support. If you want structured training to master these tools, a free demo class is available. No commitment. Just a session to see if the approach works for you.