Session 10 · Phase 2: Excel

Excel Fundamentals, Referencing & Data Cleaning

Excel is the analyst's everyday tool. Master the fundamentals — cells, cell referencing, formulas — and learn to clean messy real-world data fast.

⏱ ~2 hrs 📚 Core content 🎯 High priority

Learning Objectives

Explain the building blocks of Excel: workbook, worksheet, cell, and cell address.
Distinguish relative, absolute, and mixed cell references.
Understand the difference between a formula and a function, and the order of operations (PEMDAS).
Clean data using Remove Duplicates, Text to Columns, TRIM, Find & Replace, and Flash Fill.
Control data entry with Data Validation.

1. What Excel Is & Why It Matters

Excel is spreadsheet software that turns raw data into readable, analyzable tables. For a data analyst it's the everyday workhorse — quick analysis, reporting, cleaning, and prototyping before anything moves to a database or dashboard.

🌐

Real World: A manager emails you a messy spreadsheet of branch-wise sales and asks for a summary "by tomorrow." You won't build a database for that — you'll clean it and pivot it in Excel in under an hour. That's why Excel is on every analyst job description.

2. The Building Blocks — Workbook, Sheet, Cell

Term	What it is
Workbook	the whole Excel file (extension `.xlsx`)
Worksheet	one sheet/tab inside a workbook
Cell	the intersection of a row and a column — the basic unit
Cell address	column letter + row number, e.g. `A1`, `D7`
Range	a group of cells, e.g. `A1:A10`

Every cell holds one piece of "cell content" — a number, text, a formula, or a note.

3. Cell Referencing — Relative, Absolute, Mixed

This is the single most-asked basic Excel question. When you copy a formula, its references can either change or stay fixed — depending on the $ symbol.

Type	Example	When copied	Use case
Relative	`A1`	changes	default — repeat a calc across rows
Absolute	`$A$1`	stays fixed	lock a constant (tax rate, rate)
Mixed	`$A1` or `A$1`	one part fixed	lock column or row only

-- A relative formula, when copied one row down, becomes =A2+B2
=A1+B1

-- An absolute reference never changes, no matter where you copy it
=$A$1*B1   -- always multiplies by the value in A1

💡

Shortcut: select a reference inside a formula and press F4 to cycle through A1 → $A$1 → A$1 → $A1. This is the fastest way to lock references.

4. Formulas vs Functions, and Order of Operations

Formula vs function

A formula is any calculation you write (starts with =). A function is a built-in, pre-written formula — like SUM, AVERAGE, or COUNT.

=C3+C4+C5        -- a formula (typed manually)
=SUM(C3:C5)      -- a formula that uses the SUM function

Order of operations (PEMDAS)

Excel calculates in this order — get this wrong and your formula silently returns the wrong number:

Parentheses ()
Exponents ^
Multiplication * and Division /
Addition + and Subtraction -

=(A1*10+5)/2   -- multiply first, then add, then divide (parentheses control it)

5. Data Cleaning Essentials

Real-world data is messy — duplicates, stray spaces, merged names, inconsistent formats. These are the tools you'll reach for constantly.

5.1 Remove Duplicates

Data → Data Tools → Remove Duplicates. Permanently deletes duplicate rows. Choose which columns define a "duplicate."

5.2 Text to Columns

Data → Data Tools → Text to Columns. Splits one column into several using a delimiter (comma, space, tab). Classic use: splitting "Aarav Sharma" into first name and last name.

5.3 TRIM — remove extra spaces

=TRIM(A1) removes leading, trailing, and extra internal spaces. Names copied from emails or web forms almost always need this.

5.4 Find & Replace

Ctrl+H. Replace a value everywhere — e.g. turn "Mumbai " into "Mumbai", or standardize "Delhi" vs "DELHI".

5.5 Flash Fill

Ctrl+E. Type one example of the pattern you want, then Excel auto-fills the rest. Great for extracting initials, formatting numbers, or splitting names without formulas.

5.6 Data Validation

Data → Data Tools → Data Validation. Restricts what can be entered into a cell — e.g. a drop-down list, or numbers only between 1 and 100. Prevents dirty data at the source.

⚠️

Handling missing data: Before you fill or delete blanks, understand why they're missing. A blank "city" might mean "not recorded" (leave it) or "no city" (fill with "Unknown"). Don't blindly replace NULL/blank values — ask what the business wants.

📋 Stable content — Reviewed: August 2026

6. Interview Questions (with Model Answers)

The Excel fundamentals and data-cleaning questions interviewers ask freshers. Self-test before revealing.

IQ1. What is the difference between relative, absolute, and mixed cell references?

Model answer: "A relative reference like A1 changes when copied. An absolute reference like $A$1 stays fixed. A mixed reference locks only one part — $A1 locks the column, A$1 locks the row. I use absolute references to lock a constant like a tax rate."

IQ2. What is the order of operations in Excel?

Model answer: "PEMDAS — parentheses, exponents, multiplication/division, addition/subtraction. I use parentheses to make sure the calculation happens in the order I intend."

IQ3. What's the difference between a formula and a function?

Model answer: "A formula is any calculation I write, starting with an equals sign. A function is a built-in, pre-written formula like SUM or AVERAGE. =C3+C4+C5 is a formula; =SUM(C3:C5) is a formula that uses a function."

IQ4. How do you handle missing or duplicate data in Excel?

Model answer: "For duplicates, I use Remove Duplicates, choosing the columns that define a duplicate. For missing data, I first understand why it's missing, then either fill it with a sensible default or leave it — depending on the business context."

IQ5. How do you clean messy text data, like names with extra spaces?

Model answer: "I use TRIM to remove leading, trailing, and extra spaces, and Find & Replace to standardize inconsistent values. For splitting combined values, I use Text to Columns or Flash Fill."

IQ6. How do you split a full name into first and last name?

Model answer: "Two ways. Text to Columns with a space delimiter splits it into columns, or a formula like =LEFT(A1, FIND(" ", A1)-1) extracts the first name. Flash Fill also works if I type one example."

IQ7. How do you find duplicates without deleting them?

Model answer: "I use Conditional Formatting → Highlight Cell Rules → Duplicate Values to highlight them visually, so I can review before deleting. Remove Duplicates deletes permanently, so I highlight first when I need to inspect."

IQ8. What is Flash Fill, and when would you use it?

Model answer: "Flash Fill (Ctrl+E) detects a pattern from one typed example and fills the rest. I use it to quickly extract initials, reformat numbers, or split names without writing a formula."

IQ9. What is Data Validation used for?

Model answer: "It restricts what can be entered into a cell — like a drop-down list, or numeric ranges. It prevents dirty data at the point of entry rather than cleaning it later."

Hands-On Project: Clean a Messy Dataset

Imagine you receive a messy "customers" sheet with: duplicate rows, names with extra spaces, a combined "full name" column, and inconsistent city values. Clean it using the tools from this session.

Steps

Remove duplicate rows (using the customer ID as the key).
Split "Full Name" into "First Name" and "Last Name" using Text to Columns.
Use TRIM on the name columns to remove extra spaces.
Use Find & Replace to standardize city values (e.g. "mumbai" → "Mumbai").
Add a Data Validation drop-down to the "account_type" column (Savings, Current, Salary).
Use Conditional Formatting to highlight any remaining duplicate customer IDs.

View Solution / Walkthrough

Remove Duplicates: select the data → Data → Remove Duplicates → tick only the customer ID column → OK.
Text to Columns: select the "Full Name" column → Data → Text to Columns → Delimited → choose Space → Finish.
TRIM: in a new column, =TRIM(B2) → copy down → (optional) paste as values.
Find & Replace: Ctrl+H → find "mumbai" → replace "Mumbai" → Replace All (repeat for each city).
Data Validation: select the account_type column → Data → Data Validation → Allow: List → Source: Savings,Current,Salary.
Conditional Formatting: select the ID column → Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values.

A clean, consistent dataset ready for analysis — this exact workflow is what interviewers picture when they ask about "data cleaning."

Key Takeaways

Relative references change when copied; absolute ($A$1) never; mixed locks one part.

Excel follows PEMDAS — use parentheses to control calculation order.

Remove Duplicates deletes; Conditional Formatting highlights for review first.

TRIM, Text to Columns, and Flash Fill are the "messy data" toolkit.

Data Validation prevents dirty data at the source.

Learning Objectives

1. What Excel Is & Why It Matters

2. The Building Blocks — Workbook, Sheet, Cell

3. Cell Referencing — Relative, Absolute, Mixed

4. Formulas vs Functions, and Order of Operations

Formula vs function

Order of operations (PEMDAS)

5. Data Cleaning Essentials

5.1 Remove Duplicates

5.2 Text to Columns

5.3 TRIM — remove extra spaces

5.4 Find & Replace

5.5 Flash Fill

5.6 Data Validation

6. Interview Questions (with Model Answers)

Hands-On Project: Clean a Messy Dataset

Steps

Key Takeaways

Objective Questions — Test Your Understanding