Why reference types affect formula behavior in Google Sheets - Business Case Study

Scenario Mode

👤 Your Role: You are a data analyst at a retail company.

📋 Request: Your manager wants you to create a sales summary that correctly updates when copied to other cells.

📊 Data: You have a table with product names, unit prices, and quantities sold. You need to calculate total sales per product and then copy the formula down the column.

🎯 Deliverable: A sales summary column with formulas that calculate total sales per product and behave correctly when copied.

Sample Data

Product	Unit Price	Quantity Sold
Apples	2	10
Bananas	1.5	20
Cherries	3	15
Dates	4	5
Elderberries	5	8

1

Step 1: In cell D2, enter a formula to calculate total sales by multiplying unit price and quantity sold for the first product.

=B2*C2

Expected Result

20

2

Step 2: Copy the formula from D2 down to D6 to calculate total sales for all products.

Copy cell D2 and paste into cells D3:D6

Expected Result

D3=30, D4=45, D5=20, D6=40

3

Step 3: Explain why the formula works correctly when copied without using absolute references.

No formula change needed; explanation: Relative references adjust automatically when copied, so B2*C2 becomes B3*C3, B4*C4, etc.

Expected Result

Correct total sales calculated for each product.

4

Step 4: Now, suppose you want to apply a fixed tax rate of 10% stored in cell F1 to all total sales. Enter the tax rate 0.1 in cell F1.

Enter 0.1 in cell F1

Expected Result

Cell F1 shows 0.1

5

Step 5: In cell E2, calculate the tax amount for the first product by multiplying total sales by the tax rate. Use an absolute reference for the tax rate so it does not change when copied.

=D2*$F$1

Expected Result

2

6

Step 6: Copy the formula from E2 down to E6 to calculate tax for all products.

Copy cell E2 and paste into cells E3:E6

Expected Result

E3=3, E4=4.5, E5=2, E6=4

7

Step 7: Explain why the absolute reference $F$1 is necessary in the tax formula.

No formula change needed; explanation: The absolute reference $F$1 keeps the tax rate fixed when copying the formula, so all rows use the same tax rate.

Expected Result

Tax calculated correctly for all products.

Final Result

Product     Unit Price  Quantity Sold  Total Sales  Tax Amount
-------------------------------------------------------------
Apples      2           10             20           2
Bananas     1.5         20             30           3
Cherries    3           15             45           4.5
Dates       4           5              20           2
Elderberries5           8              40           4

✓Relative references (like B2 and C2) change automatically when formulas are copied, so total sales calculate correctly for each product.

✓Absolute references (like $F$1) stay fixed when formulas are copied, ensuring the tax rate is always taken from the same cell.

✓Using the right reference type is important to make formulas behave as expected when copied.

Bonus Challenge

Create a formula in column F that calculates the total price including tax for each product by adding total sales and tax amount. Use appropriate references so the formula works when copied.

Show Hint

Use relative references for total sales and tax amount cells, then copy the formula down.