Absolute references ($) in Google Sheets - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to calculate the total sales for each product after applying a fixed tax rate. The tax rate is the same for all products and is stored in a single cell.

📊 Data: You have a table with product names, units sold, and unit price. The tax rate is given in a separate cell.

🎯 Deliverable: Create a new column that calculates the total sales including tax for each product using absolute references to the tax rate cell.

Sample Data

Product	Units Sold	Unit Price
Apples	100	0.50
Bananas	150	0.30
Cherries	200	0.20
Dates	120	0.40
Elderberries	80	0.60

Tax Rate: 10% (stored in cell E1)

1

Step 1: Enter the tax rate 0.10 in cell E1.

Type 0.10 in cell E1

Expected Result

Cell E1 shows 0.10

2

Step 2: In cell D2, calculate total sales including tax for the first product using the formula that multiplies Units Sold by Unit Price and then applies the tax rate from cell E1 using an absolute reference.

=B2*C2*(1+$E$1)

Expected Result

Cell D2 shows 55 (100*0.50*1.10)

3

Step 3: Copy the formula from D2 down to cells D3 to D6 to calculate total sales including tax for all products.

Drag the fill handle from D2 down to D6

Expected Result

Cells D3 to D6 show 49.5, 49.5, 52.8, 52.8 respectively

Final Result

Product     Units Sold  Unit Price  Total Sales with Tax
-----------------------------------------------------
Apples      100         0.50        55.00
Bananas     150         0.30        49.50
Cherries    200         0.20        44.00
Dates       120         0.40        52.80
Elderberries 80         0.60        52.80

✓Using absolute references ($E$1) keeps the tax rate fixed when copying formulas.

✓Total sales including tax are correctly calculated for each product.

✓This method saves time and avoids errors when applying the same rate to multiple rows.

Bonus Challenge

Modify the sheet to allow changing the tax rate in cell E1 and see all total sales update automatically.

Show Hint

Use the same absolute reference $E$1 in your formulas so they always refer to the tax rate cell.