Topic 3 of 12

Excel for Data Analysts — Core Functions

Excel is the gateway to data analytics. Master these 10 functions and you'll be ahead of 80% of beginners.

📚Beginner
⏱️15 min
✅5 quizzes

Why Excel for Data Analysts?

Despite the rise of Python and SQL, Excel remains the #1 tool for data analysts. Here's why:

✅ Universal: Every company uses it ✅ No coding required: Great for beginners ✅ Fast prototyping: Test ideas quickly ✅ Easy sharing: Everyone can open an Excel file ✅ Visual: See your data as you work

Industry Reality: Even in tech companies, 70% of data tasks start in Excel.

The 10 Must-Know Excel Functions

1. VLOOKUP — Find Data from Another Table

What it does: Looks up a value in the first column of a table and returns a value from another column.

Syntax:

code.txtEXCEL
=VLOOKUP(lookup_value, table_range, column_number, [exact_match])

Example:

Table 1: Sales Data | Order ID | Amount | |----------|--------| | 1001 | 5000 | | 1002 | 3000 |

Table 2: Order Details | Order ID | Customer | |----------|----------| | 1001 | Rahul | | 1002 | Priya |

Formula to get customer name:

code.txtEXCEL
=VLOOKUP(A2, OrderDetails!A:B, 2, FALSE)

When to use: Matching data from different sheets, looking up prices, finding employee info.

Pro Tip: Use FALSE for exact match (most common). Use TRUE for approximate match (rare).

2. SUMIF / SUMIFS — Conditional Sums

What it does: Sums values that meet specific criteria.

SUMIF Syntax:

code.txtEXCEL
=SUMIF(range, criteria, sum_range)

Example:

code.txtEXCEL
=SUMIF(B:B, "Electronics", C:C)
// Sums sales where category = "Electronics"

SUMIFS (multiple conditions):

code.txtEXCEL
=SUMIFS(C:C, B:B, "Electronics", D:D, ">1000")
// Sums sales where category = "Electronics" AND amount > 1000

When to use: Calculating totals by category, region, or any filter.

3. COUNTIF / COUNTIFS — Count with Conditions

What it does: Counts cells that meet criteria.

Example:

code.txtEXCEL
=COUNTIF(A:A, "Delivered")
// Counts how many orders have status "Delivered"

Multiple conditions:

code.txtEXCEL
=COUNTIFS(A:A, "Delivered", B:B, "Mumbai")
// Counts delivered orders in Mumbai

When to use: Frequency analysis, counting categories, checking data quality.

4. AVERAGEIF — Conditional Average

What it does: Calculates average of cells that meet a condition.

Example:

code.txtEXCEL
=AVERAGEIF(B:B, "Premium", C:C)
// Average order value for "Premium" customers

When to use: Finding average sales by product, average rating by category.

5. IF Statements — Conditional Logic

What it does: Returns one value if a condition is true, another if false.

Syntax:

code.txtEXCEL
=IF(condition, value_if_true, value_if_false)

Examples:

Simple:

code.txtEXCEL
=IF(A2 > 10000, "High Value", "Low Value")

Nested (multiple conditions):

code.txtEXCEL
=IF(A2 > 50000, "Platinum", IF(A2 > 20000, "Gold", "Silver"))

When to use: Categorizing data, flagging outliers, creating custom segments.

6. TEXT Functions — Clean and Format

LEN — Count characters:

code.txtEXCEL
=LEN(A2)
// Returns length of text in A2

LEFT / RIGHT / MID — Extract parts:

code.txtEXCEL
=LEFT(A2, 3)    // First 3 characters
=RIGHT(A2, 4)   // Last 4 characters
=MID(A2, 5, 2)  // 2 characters starting from position 5

CONCATENATE / CONCAT — Join text:

code.txtEXCEL
=CONCATENATE(A2, " ", B2)
// Or simply: =A2&" "&B2

TRIM — Remove extra spaces:

code.txtEXCEL
=TRIM(A2)

UPPER / LOWER / PROPER — Change case:

code.txtEXCEL
=UPPER(A2)   // ALL CAPS
=LOWER(A2)   // all lowercase
=PROPER(A2)  // Title Case

When to use: Data cleaning, formatting names, extracting codes.

7. Pivot Tables — The Swiss Army Knife

What it does: Summarizes large datasets into interactive tables.

How to create:

  1. Select your data
  2. Insert → Pivot Table
  3. Drag fields to Rows, Columns, Values
  4. Choose aggregation (Sum, Count, Average)

Example Use Cases:

  • Sales by region and product
  • Monthly revenue trends
  • Top 10 customers by purchase value
  • Employee count by department

Pro Tip: Learn to use slicers (filters) to make pivot tables interactive.

8. Data Validation — Prevent Errors

What it does: Restricts what users can enter in a cell.

How to set up:

  1. Select cells
  2. Data → Data Validation
  3. Choose criteria (List, Number range, Date range)

Example:

  • Dropdown list: "Pending, In Progress, Completed"
  • Only numbers between 1-100
  • Only future dates

When to use: Creating forms, preventing typos, standardizing entries.

9. Conditional Formatting — Visual Insights

What it does: Automatically formats cells based on their values.

Common Uses:

Highlight top 10:

  • Select range → Conditional Formatting → Top 10

Color scale:

  • Green for high values, red for low values

Data bars:

  • Visual bars inside cells showing relative size

Icon sets:

  • Traffic lights, arrows, stars

When to use: Quickly spotting trends, highlighting outliers, making dashboards.

10. INDEX-MATCH — VLOOKUP's Powerful Alternative

Why it's better than VLOOKUP:

  • Can look left (VLOOKUP can't)
  • Faster for large datasets
  • More flexible

Syntax:

code.txtEXCEL
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example:

code.txtEXCEL
=INDEX(B:B, MATCH(A2, C:C, 0))
// Finds A2 in column C, returns corresponding value from column B

When to use: Advanced lookups, two-way lookups, dynamic references.

Essential Excel Shortcuts

| Action | Shortcut (Windows) | Why You Need It | |--------|-------------------|----------------| | Auto-fill formula down | Ctrl + D | Copy formula to all rows | | Insert current date | Ctrl + ; | Quick timestamping | | Insert current time | Ctrl + Shift + ; | Time tracking | | Format as table | Ctrl + T | Organized data | | Open Find & Replace | Ctrl + H | Batch edits | | Select entire column | Ctrl + Space | Quick selection | | Select entire row | Shift + Space | Quick selection | | Go to end of data | Ctrl + Arrow | Navigate large sheets | | Create chart | Alt + F1 | Instant visualization |

Data Cleaning Workflow in Excel

Step 1: Remove Duplicates

  • Data → Remove Duplicates
  • Select columns to check
  • Click OK

Step 2: Find & Replace

  • Ctrl + H
  • Replace typos, fix formatting
  • Example: Replace "Banglore" with "Bangalore"

Step 3: Text to Columns

  • Split "Amit Kumar" into "Amit" | "Kumar"
  • Data → Text to Columns
  • Choose delimiter (space, comma, etc.)

Step 4: Remove Leading/Trailing Spaces

code.txtEXCEL
=TRIM(A2)

Step 5: Convert Text to Numbers

  • Select cells
  • Click warning icon → Convert to Number
  • Or use =VALUE(A2)

Pivot Table Deep Dive

Anatomy of a Pivot Table

Filters: Top-level filters (Year, Region) Rows: Categories on the left (Product, Customer) Columns: Categories across the top (Month, Quarter) Values: The numbers being calculated (Sum of Sales, Count of Orders)

Example: Sales Analysis

Data: | Date | Region | Product | Sales | |------|--------|---------|-------| | 2026-01 | North | Laptop | 50000 | | 2026-01 | South | Mouse | 500 |

Pivot Table Setup:

  • Rows: Product
  • Columns: Region
  • Values: Sum of Sales

Result: | | North | South | Total | |---|-------|-------|-------| | Laptop | 50000 | 30000 | 80000 | | Mouse | 500 | 400 | 900 |

Common Excel Mistakes & Fixes

❌ Mistake 1: Not Locking Cell References

Problem: Formula breaks when copied Fix: Use $ to lock: $A$1 (absolute), $A1 (lock column), A$1 (lock row)

❌ Mistake 2: Storing Dates as Text

Problem: Can't calculate age, duration, or sort properly Fix: Use =DATEVALUE("21-03-2026")

❌ Mistake 3: Merging Cells

Problem: Breaks sorting, filtering, formulas Fix: Use "Center Across Selection" instead

❌ Mistake 4: Not Converting Ranges to Tables

Problem: Formulas don't auto-extend, hard to reference Fix: Ctrl + T to convert to a table

Real-World Example: Sales Dashboard

Goal: Track monthly sales by product and region

Steps:

  1. Import data (1000+ rows of sales transactions)
  2. Clean data:
    • Remove duplicates
    • Fix date formats
    • Trim whitespace
  3. Create calculated columns:
    code.txtEXCEL
    =IF(Sales > 50000, "High", IF(Sales > 20000, "Medium", "Low"))
  4. Build pivot table:
    • Rows: Product
    • Columns: Month
    • Values: Sum of Sales
  5. Add conditional formatting (heat map)
  6. Create charts (column chart for trends)
  7. Add slicers (filter by region)

Result: Interactive dashboard showing sales trends at a glance.

When to Graduate from Excel

Excel is powerful, but it has limits:

Stick with Excel when:

  • Dataset < 100,000 rows
  • Simple calculations
  • Sharing with non-technical people
  • Quick ad-hoc analysis

Move to SQL when:

  • Dataset > 100,000 rows
  • Need to join multiple large tables
  • Working with databases
  • Automating regular reports

Move to Python when:

  • Advanced statistical analysis
  • Machine learning
  • Web scraping
  • Complex automation

Pro Tip: Most analysts use all three tools depending on the task.

Summary

✅ 10 core functions: VLOOKUP, SUMIF, COUNTIF, AVERAGEIF, IF, Text functions, Pivot Tables, Data Validation, Conditional Formatting, INDEX-MATCH ✅ Data cleaning workflow: Remove duplicates, find & replace, trim, text to columns ✅ Pivot tables are your best friend for quick insights ✅ Shortcuts save hours over time ✅ Know when to move to SQL or Python for larger tasks

Next Topic: Introduction to SQL

Ready to level up? Let's learn how to query databases! 🚀

←
Previous
Types of Data & Data Structures
Next
Introduction to SQL
→