Relative references (A1) in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales assistant at a retail store.

📋 Request: Your manager wants you to calculate the total price for each product sold by multiplying the quantity sold by the unit price.

📊 Data: You have a list of products with their quantities sold and unit prices in a table.

🎯 Deliverable: Create a new column that calculates the total price for each product using relative cell references.

Sample Data

Product	Quantity Sold	Unit Price
Apples	10	0.50
Bananas	5	0.30
Cherries	20	1.00
Dates	7	1.50
Elderberries	3	2.00

1

Step 1: Insert a new column to the right of 'Unit Price' and name it 'Total Price'.

Expected Result

A new column named 'Total Price' appears next to 'Unit Price'.

2

Step 2: In the first cell under 'Total Price' (D2), enter a formula to multiply the quantity sold by the unit price using relative references.

=B2*C2

Expected Result

The cell D2 shows 5 (10 * 0.50).

3

Step 3: Copy the formula from D2 down to all rows in the 'Total Price' column to calculate totals for each product.

Drag the fill handle from D2 down to D6.

Expected Result

Cells D3 to D6 show the total prices: 1.5, 20, 10.5, and 6 respectively.

Final Result

Product     Quantity Sold  Unit Price  Total Price
-------------------------------------------------
Apples      10             0.50        5.00
Bananas     5              0.30        1.50
Cherries    20             1.00        20.00
Dates       7              1.50        10.50
Elderberries 3             2.00        6.00

✓Using relative references allows the formula to adjust automatically for each row.

✓Total price is correctly calculated by multiplying quantity sold by unit price for each product.

Bonus Challenge

Format the 'Total Price' column to show currency with two decimal places.

Show Hint

Select the 'Total Price' cells, right-click, choose 'Format Cells', then select 'Currency' with 2 decimal places.