20 Essential Excel Formulas Every Data Analyst Must Know in 2026

Whether you’re a seasoned data analyst or just starting your career, mastering Excel formulas is non-negotiable. Despite the rise of Python, R, and BI tools, Excel remains the most-used tool in business analytics — and the right formulas can save you hours every week. In this comprehensive guide, we cover the 20 essential Excel formulas every data analyst must know in 2026, with practical examples and pro tips.

📋 Quick Reference Table

🔍 Lookup Formulas (The Power Players)

1. XLOOKUP — The Modern Standard

XLOOKUP replaces VLOOKUP, HLOOKUP, and INDEX/MATCH in one cleaner function. If you have Excel 2021 or Microsoft 365, use this.

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

Example: Look up the price of "Apple" in a product table:

=XLOOKUP("Apple", A2:A100, C2:C100, "Not Found")

Why it’s better than VLOOKUP:

• Searches left or right (VLOOKUP only goes right)
• Built-in error handling with [if_not_found]
• Supports exact, approximate, and wildcard matches
• Can return multiple values

2. VLOOKUP — Still Everywhere

If you don’t have XLOOKUP, VLOOKUP is your best friend:

=VLOOKUP(lookup_value, table_array, col_index, [exact_match])

Example: Get the price of a product:

=VLOOKUP("Apple", A2:D100, 3, FALSE)

Pro tips:

• Always use FALSE for exact match unless you’re sure you need approximate
• Wrap with IFERROR to handle missing values: =IFERROR(VLOOKUP(...), "Not Found")
• Major limitation: Can only look up to the right of the search column

3. INDEX/MATCH — The Flexible Alternative

Before XLOOKUP, this was the pro analyst’s combo:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example: Same as VLOOKUP, but works in any direction:

=INDEX(C2:C100, MATCH("Apple", A2:A100, 0))

Use INDEX/MATCH if you’re on older Excel without XLOOKUP and need to look up to the left.

➕ Aggregation Formulas (For Summary Tables)

4. SUMIFS — Sum with Multiple Conditions

The most powerful aggregator. Sum values that meet multiple criteria:

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

Example: Total Q4 sales for the East region:

=SUMIFS(D2:D1000, B2:B1000, "East", C2:C1000, "Q4")

Pro tip: Use comparison operators in criteria:

=SUMIFS(D2:D1000, A2:A1000, ">2025-01-01", D2:D1000, ">100")

5. COUNTIFS — Count with Multiple Conditions

Same logic as SUMIFS but for counting:

=COUNTIFS(B2:B1000, "East", C2:C1000, "Q4")

Returns the number of rows matching all conditions.

6. AVERAGEIFS — Average with Conditions

=AVERAGEIFS(D2:D1000, B2:B1000, "East", C2:C1000, "Q4")

Returns the average of sales for East region in Q4.

🧠 Logic Formulas

7. IF — The Building Block

=IF(logical_test, value_if_true, value_if_false)

Example: Categorize sales:

=IF(D2>1000, "High", "Low")

Nested IFs (when you have multiple conditions):

=IF(D2>10000, "VIP", IF(D2>1000, "Premium", IF(D2>100, "Standard", "Basic")))

8. IFS — Cleaner Nested IFs

Excel 2019+ has IFS for cleaner multi-condition logic:

=IFS(D2>10000, "VIP", D2>1000, "Premium", D2>100, "Standard", TRUE, "Basic")

Much easier to read than nested IFs.

9. IFERROR — Graceful Error Handling

Wrap any formula that might error:

=IFERROR(A2/B2, 0)

Returns 0 if B2 is 0 (instead of #DIV/0! error). Essential for clean dashboards.

📝 Text Formulas

10. CONCATENATE / TEXTJOIN — Combining Text

Old way (Excel 2016 and earlier):

=CONCATENATE(A2, " ", B2)

Modern way (Excel 2019+):

=TEXTJOIN(" ", TRUE, A2, B2, C2)

The TRUE argument skips empty cells. Much more flexible:

=TEXTJOIN(", ", TRUE, A2:A10)

Joins a range of cells with commas, skipping blanks.

11. LEFT / RIGHT / MID — Substring Extraction

Extract portions of text:

=LEFT(A2, 3)        # First 3 characters
=RIGHT(A2, 4)       # Last 4 characters
=MID(A2, 5, 3)      # 3 characters starting at position 5

Real example: Extract area code from phone number (555) 123-4567:

=MID(A2, 2, 3)      # Returns "555"

12. LEN — String Length

=LEN(A2)

Useful for validating data (e.g., phone numbers should be 10 digits).

13. TRIM — Remove Extra Spaces

=TRIM(A2)

Critical when importing data from PDFs or web sources where extra spaces sneak in. TRIM(" Hello World ") returns "Hello World".

14. UPPER / LOWER / PROPER — Change Case

=UPPER(A2)          # "HELLO WORLD"
=LOWER(A2)          # "hello world"
=PROPER(A2)         # "Hello World" (each word capitalized)

📅 Date Formulas

15. DATEDIF — Hidden but Powerful

Calculate the difference between two dates:

=DATEDIF(start_date, end_date, "Y")   # Years
=DATEDIF(start_date, end_date, "M")   # Months
=DATEDIF(start_date, end_date, "D")   # Days
=DATEDIF(start_date, end_date, "YM")  # Months ignoring years

Example: Calculate age from birthdate:

=DATEDIF(A2, TODAY(), "Y")

⚠️ Quirk: Excel doesn’t show DATEDIF in the autocomplete. Just type it.

16. EOMONTH — End of Month

=EOMONTH(date, months_offset)

Examples:

=EOMONTH(TODAY(), 0)     # Last day of current month
=EOMONTH(TODAY(), 1)     # Last day of next month
=EOMONTH(TODAY(), -3)    # Last day of 3 months ago

Essential for monthly reporting.

17. WEEKDAY — Day of Week

=WEEKDAY(date, [return_type])

=WEEKDAY(A2, 2)     # Monday=1, Sunday=7

Combine with TEXT to get the day name:

=TEXT(A2, "dddd")   # Returns "Monday"

🚀 Dynamic Array Formulas (Game Changers in Microsoft 365)

These functions revolutionized Excel — they auto-expand to fill multiple cells.

18. FILTER — Filter Without Macros

=FILTER(array, include, [if_empty])

Example: Get all East region sales:

=FILTER(A2:D1000, B2:B1000="East", "No data")

Returns multiple rows automatically — no need for AutoFilter or formulas-per-row.

19. UNIQUE — Get Unique Values

=UNIQUE(range)

Example: Get list of distinct customers:

=UNIQUE(B2:B1000)

Combined with SORT, it’s incredibly powerful:

=SORT(UNIQUE(B2:B1000))

20. SORT — Sort Without Right-Clicking

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

Example: Sort customer list by revenue (descending):

=SORT(A2:C100, 3, -1)

Sorts by column 3, descending. Updates automatically when data changes.

💪 Bonus: Combining Formulas for Power Moves

Dynamic Dashboard with FILTER + SORT + UNIQUE

=SORT(FILTER(A2:D1000, C2:C1000="2026"), 4, -1)

Returns 2026 records, sorted by column 4 descending. Updates live.

Top 5 Customers with LARGE + INDEX

=INDEX(B2:B1000, MATCH(LARGE(D2:D1000, 1), D2:D1000, 0))

Returns the customer with the highest sales.

Conditional Formatting with COUNTIFS

For highlighting duplicates across columns:

=COUNTIFS($A:$A, A2, $B:$B, B2)>1

🎯 Best Practices for Excel Analysts

1. Use Named Ranges: Instead of D2:D1000, name the range Sales. Formula becomes =SUMIFS(Sales, Region, "East").

2. Convert to Tables: Press Ctrl+T to convert your data range to an Excel Table. Formulas auto-expand as you add rows.

3. Always check for errors: Wrap critical formulas with IFERROR() to avoid breaking dashboards.

4. Use absolute vs relative references: $A$1 (absolute), A1 (relative), $A1 (column locked). Press F4 to cycle.

5. Document complex formulas: Add a comment with Ctrl+Shift+A or put the explanation in a nearby cell.

6. Avoid volatile functions in big sheets: NOW(), TODAY(), OFFSET(), INDIRECT() recalculate on every change — they slow down large workbooks.

7. Learn keyboard shortcuts: F2 (edit cell), Ctrl+Shift+Enter (array formula in old Excel), Alt+= (AutoSum), Ctrl+T (Table).

🛠️ What About Power Query and Power Pivot?

Once you’ve mastered these 20 formulas, level up by learning:

• Power Query (Get & Transform): The right tool for cleaning messy data from multiple sources. Replaces 90% of manual data prep.
• Power Pivot: For building data models with millions of rows. Use DAX formulas instead of regular Excel formulas.

These are part of Excel and don’t require additional licenses, yet most analysts never touch them. Learning them is a major career boost.

📚 What’s Next?

This guide covers the absolute essentials. To go deeper:

1. Practice with real datasets: Kaggle has free datasets perfect for Excel practice
2. Build a portfolio: 3-5 Excel dashboards on GitHub show recruiters you’re serious
3. Learn VBA: For automating repetitive tasks
4. Bridge to Python: Use openpyxl to manipulate Excel files programmatically

❓ Frequently Asked Questions

Should I learn VLOOKUP or XLOOKUP first?

If your version of Excel supports it (Office 365 or 2021+), learn XLOOKUP. It’s simpler, more flexible, and the future. Learn VLOOKUP only if you’re stuck with Excel 2019 or earlier.

What’s the difference between SUMIF and SUMIFS?

• SUMIF takes one condition: =SUMIF(range, criteria, sum_range)
• SUMIFS takes multiple conditions: =SUMIFS(sum_range, range1, criteria1, range2, criteria2)

Always use SUMIFS — it’s more flexible and the parameter order is more intuitive.

How do I avoid #N/A errors with lookup formulas?

Wrap with IFERROR:

=IFERROR(VLOOKUP(...), "Not Found")
=IFERROR(INDEX(...MATCH(...)), 0)

XLOOKUP has built-in error handling: =XLOOKUP(value, lookup, return, "Not Found")

When should I use array formulas vs regular formulas?

With Microsoft 365’s dynamic arrays (FILTER, UNIQUE, SORT, SEQUENCE, etc.), array formulas are now the default. They’re cleaner and more powerful. Avoid CSE (Ctrl+Shift+Enter) array formulas unless absolutely needed.

Is Excel still relevant for data analysts in 2026?

Absolutely. Excel remains the most-used tool in business analytics. Knowing Python and SQL is essential, but you’ll write Excel formulas your entire career. The combination of all three makes you unstoppable.

What’s the fastest way to learn these formulas?

Practice on real data. Take a dataset (Kaggle, your company’s data, or scraped data), and try to answer 10 questions about it using only formulas. You’ll learn faster than any tutorial.

Happy analyzing! 📊

Ahmed Elhozayen

See Full Bio