If you’re planning to start a career in data analysis, Excel is one tool you cannot escape and for good reason. It’s simple, powerful, and used by almost every company in the world. Even with the rise of Python, SQL, and Power BI, Excel remains the first tool analysts touch.

But here’s the truth:
Most people only know 10% of what Excel can actually do.

This guide will walk you through the essential Excel formulas every data analyst must know, with clear explanations and practical examples.

Let’s dive in.

1. VLOOKUP (and the better version: XLOOKUP)

VLOOKUP

Used to fetch values from another table.

Example

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

Why analysts use it:

• Quick merging of datasets
• Easy lookup of IDs, names, categories

But wait, you can use XLOOKUP instead.

XLOOKUP

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B)

✔ faster
✔ no column number
✔ works left + right
✔ less error-prone

2. INDEX-MATCH

If XLOOKUP is unavailable, INDEX-MATCH is the best choice.

Example

=INDEX(B:B, MATCH(A2, A:A, 0))

Why analysts love it:

• More flexible than VLOOKUP
• Works even when columns are rearranged

3. IF and Nested IF Statements

Decision-making in Excel starts here.

Basic IF

=IF(A2 > 100, "High", "Low")

Nested IF

=IF(A2>90,"A",IF(A2>80,"B","C"))

Great for:

• Categorising values
• Creating flags
• Data quality checks

4. COUNT, COUNTA, COUNTIF, COUNTIFS

These are essential for counting rows with conditions.

COUNTIF

=COUNTIF(A:A, "Male")

COUNTIFS (multiple conditions)

=COUNTIFS(A:A,"Male",B:B,">30")

Great for:

• Data summaries
• Validations
• Quick filtering insights

5. SUM, SUMIF, SUMIFS

Just like COUNTIF but for totals.

SUMIFS example

=SUMIFS(C:C, A:A,"Product A", B:B,">=2024")

Great for:

• Sales analysis
• Revenue reporting
• KPI dashboards

6. TEXT Functions for Cleaning Data

LEFT

=LEFT(A2, 4)

RIGHT

=RIGHT(A2, 3)

MID

=MID(A2,2,5)

TRIM

=TRIM(A2)

These help you fix:

• Inconsistent strings
• Leading/trailing spaces
• Extracting IDs or codes

7. CONCAT, CONCATENATE, TEXTJOIN

CONCAT

=CONCAT(A2, " ", B2)

TEXTJOIN

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

Used for merging names, addresses, tags, etc.

8. UNIQUE + FILTER

UNIQUE

=UNIQUE(A2:A100)

FILTER

=FILTER(A2:C100, B2:B100="Nigeria")

Great for:

• Instant filtering
• Returning dynamic lists
• Cleaning duplicate data

9. DATE Functions

TODAY

=TODAY()

DATEDIF

=DATEDIF(A2, B2, "D")

Great for:

• Age calculation
• Tenure analysis
• Date differences

10. Pivot Table-Ready Calculations with AVERAGEIF(S)

AVERAGEIFS

=AVERAGEIFS(C:C, A:A,"Product A")

Great for:

• Performance tracking
• Customer segmentation
• Statistical analysis

If you want to succeed as a data analyst, Excel isn’t optional — it’s a foundational skill. Mastering these formulas gives you the confidence to clean, analyze, and manipulate data like a professional.

Once you’re comfortable with these, move on to:

• Pivot tables
• Power Query
• Excel automation
• Python + Excel workflows

Your career will thank you.

FAQ