Excel for Data Analytics • Lesson 2
Excel Cell References for Data Analytics
Learn how Excel identifies cells and how data analysts use cell references to create and copy formulas across a dataset.
What You Will Learn
In the previous lesson, you learned how Excel datasets are organized. Now you will learn how to refer to specific cells and use those references inside formulas.
STEP 1
Understanding an Excel Cell
Every value in an Excel worksheet is stored inside a cell. A cell is identified using a column letter and a row number.
Column
B
Row
4
Cell Reference
B4
CHECK YOUR UNDERSTANDING
What does the cell reference C5 mean?
STEP 2
Find Data Using Cell References
Consider this small sales dataset. The first row contains the column headings and the data begins from row 2.
| A: Product | B: Region | C: Units | D: Price |
|---|---|---|---|
| Laptop | North | 3 | ₹50,000 |
| Mouse | South | 10 | ₹1,000 |
| Keyboard | East | 5 | ₹2,000 |
| Monitor | West | 4 | ₹15,000 |
CHECK YOUR UNDERSTANDING
Where is the Units value for the Keyboard record?
STEP 3
Relative Cell References
A relative cell reference changes when you copy a formula to another row or column.
Example
Suppose Units are in column C and Price is in column D. Revenue is calculated in column E.
When the formula is copied down, Excel automatically adjusts the row numbers.
CHECK YOUR UNDERSTANDING
If E2 contains =C2*D2 and you copy it to E5, what will Excel use?
FORMULA CHALLENGE
Units are in C6 and Price is in D6. Write the Excel formula for Revenue.
STEP 4
Why Cell References Matter in Data Analytics
A real dataset can contain hundreds, thousands or even millions of records. Analysts need formulas that can be reused efficiently.
Example
Instead of calculating revenue manually for every sales record, create the formula once:
Then copy the formula down the dataset.
CHECK YOUR UNDERSTANDING
What is the main advantage of using relative references when analyzing many rows of data?
STEP 5
Absolute Cell References
Sometimes one value should remain fixed while a formula is copied. An absolute reference allows us to do this.
Business Example: Tax Rate
Suppose the company's tax rate is stored in B1:
Sales amount is stored in C2.
The $ signs tell Excel to keep B1 fixed when the formula is copied.
CHECK YOUR UNDERSTANDING
Why is $B$1 used in the tax formula?
FORMULA CHALLENGE
Sales are in C2 and the fixed tax rate is in B1. Write the formula for the tax amount.
STEP 6
Mixed References
Mixed references allow you to fix either the row or the column.
$B$1
Row and column are fixed.
$B1
Column is fixed.
B$1
Row is fixed.
CHECK YOUR UNDERSTANDING
Which reference keeps row 1 fixed but allows the column to change?
Data Analytics Practice
Apply Cell References to Business Data
A company has a standard tax rate of 18%. The tax rate is stored once in B1, while sales amounts are stored in column C.
Tax Rate
B1 = 18%
Sales
C2 = ₹50,000
Tax
₹9,000
CHECK YOUR UNDERSTANDING
If sales are ₹50,000 and the tax rate is 18%, what is the tax amount?
Final Challenge
Build the Formula
You are preparing a sales report. Sales amount is in C2 and the company's tax rate is stored in B1.
Your requirements
- • C2 should change when the formula is copied.
- • B1 must remain fixed.
- • The formula should work for every sales row.
FORMULA CHALLENGE
Write the Excel formula for the tax amount in row 2.
CHECK YOUR UNDERSTANDING
If you copy =C2*$B$1 from row 2 to row 5, what formula should Excel create?
Lesson 2 Complete
You now understand how Excel identifies cells and how relative and absolute references behave when formulas are copied.