Absolute references ($A$1) in Excel - 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 by multiplying the quantity sold by the fixed price per unit. The price per unit is the same for all products and is stored in a single cell.

📊 Data: You have a table with product names, quantities sold, and a separate cell with the price per unit.

🎯 Deliverable: Create a new column that calculates total sales for each product using an absolute reference to the price per unit cell.

Sample Data

Product	Quantity Sold
Apples	10
Bananas	15
Cherries	7
Dates	12
Elderberries	5

Price per unit is in cell D1: 2.50

1

Step 1: Enter the price per unit in cell D1.

Type 2.50 in cell D1.

Expected Result

Cell D1 shows 2.50.

2

Step 2: Create a new column header 'Total Sales' in cell C1.

Type 'Total Sales' in cell C1.

Expected Result

Cell C1 shows 'Total Sales'.

3

Step 3: In cell C2, enter a formula to multiply quantity sold in B2 by the price per unit in D1 using an absolute reference.

=B2*$D$1

Expected Result

Cell C2 shows 25.00 (10 * 2.50).

4

Step 4: Copy the formula from cell C2 down to cells C3 to C6 to calculate total sales for all products.

Drag the fill handle from C2 down to C6.

Expected Result

Cells C3 to C6 show 37.50, 17.50, 30.00, and 12.50 respectively.

Final Result

Product     Quantity Sold     Total Sales
Apples           10              25.00
Bananas          15              37.50
Cherries          7              17.50
Dates            12              30.00
Elderberries      5              12.50

✓Using absolute references allows the price per unit to stay fixed when copying formulas.

✓Total sales are correctly calculated for each product by multiplying quantity by the fixed price.

Bonus Challenge

Modify the sheet so that if the price per unit changes in cell D1, all total sales update automatically.

Show Hint

The absolute reference formula already supports this. Try changing the value in D1 and observe the results.