Top 10 Excel Functions Every Data Analyst Must Master

Aug 11, 2025

—

by

When Rohan, a 26-year-old commerce graduate from Pune, started preparing for his first data analyst interview, he quickly realized one thing – Excel is not just a spreadsheet tool, it’s a career-making skill.

He had always used Excel for basic sums and formatting, but during mock interviews, he froze when asked,

“Can you combine INDEX and MATCH to find a sales figure for a product in a given month?”

That day, Rohan decided – No more guesswork. I will master the top Excel functions recruiters expect.
Here’s what he learned, with examples from his practice sessions.

1. VLOOKUP / XLOOKUP – Rohan’s ‘Data Detective’ Tool

One day, Rohan had two datasets – one with Product Names, another with Sales Values.
Instead of scrolling endlessly, he used:

=XLOOKUP("Mango Juice", A2:A100, B2:B100, "Not Found")

Result: Sales value for Mango Juice in seconds.
Lesson: Lookup functions save hours in data matching.

2. INDEX + MATCH – Rohan’s Upgrade

During an interview test, the product name was in column C, and sales were in column A.
VLOOKUP couldn’t help (it needs the lookup column first).
Rohan used:

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

Lesson: INDEX+MATCH works in any direction and is interview gold.

3. TEXT Functions – Cleaning Rohan’s Messy Data

His dataset had customer IDs like " AB1234 " with spaces.
He cleaned it using:

=TRIM(A2)

And extracted first 2 letters for state code:

=LEFT(A2, 2)

Lesson: TEXT functions like LEFT, RIGHT, MID, TRIM, and LEN are must-haves for messy datasets.

4. IF + IFS – Decision Maker

When given sales targets, Rohan categorized them:

=IF(B2>=100000, "Top Performer", "Needs Improvement")

For multiple conditions:

=IFS(B2>=100000, "Top Performer", B2>=50000, "Average", TRUE, "Low")

Lesson: IF helps classify data instantly.

5. SUMIF / SUMIFS – Finding Patterns

To know the total sales for “Mango Juice” in the “East” region:

=SUMIFS(Sales, Product, "Mango Juice", Region, "East")

Lesson: SUMIFS is perfect for quick conditional aggregations.

6. COUNTIF / COUNTIFS – Counting What Matters

In one dataset, Rohan needed to know how many orders were above ₹5,000:

=COUNTIF(Sales, ">5000")

Lesson: COUNT functions are quick ways to spot trends in large datasets.

7. FILTER – Rohan’s Shortcut to Relevant Data

Instead of applying Excel’s manual filter, Rohan extracted all sales for the “North” region with:

=FILTER(A2:D100, Region="North")

Lesson: Dynamic, criteria-based extraction beats manual filtering.

8. UNIQUE – Finding Distinct Customers

When asked for the number of unique buyers, Rohan did:

=UNIQUE(CustomerName)

Lesson: UNIQUE quickly deduplicates lists for better analysis.

9. Date Functions – Time Travel in Excel

Rohan needed monthly trends. He used:

=TEXT(OrderDate, "MMM-YYYY")

For month-end date:

=EOMONTH(OrderDate, 0)

Lesson: Date functions help slice and dice time-based data.

10. Power Query + Power Pivot – Rohan’s Secret Weapon

By now, Rohan could clean data in Power Query, load millions of rows, and use DAX for calculated measures.
In one interview, he impressed the panel by transforming raw CSV files into a dashboard-ready table in 10 minutes.