Mixed references ($A1, 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 total sales for each product across different months, using a formula that can be copied easily without errors.

📊 Data: You have a table with product names in column A, months in row 1 starting from column B, and sales numbers in the intersecting cells.

🎯 Deliverable: Create a formula using mixed references to calculate total sales per product and total sales per month that can be copied across rows and columns correctly.

Sample Data

Product	Jan	Feb	Mar
Apples	100	120	130
Bananas	90	110	115
Cherries	80	95	100

1

Step 1: Add a new column after the March column to calculate total sales per product.

In cell E2, enter the formula: =SUM(B2:D2)

Expected Result

For Apples, total sales = 100 + 120 + 130 = 350

2

Step 2: Copy the total sales formula down for all products.

Copy the formula from E2 down to E3 and E4.

Expected Result

Bananas total = 90 + 110 + 115 = 315; Cherries total = 80 + 95 + 100 = 275

3

Step 3: Add a new row below the last product to calculate total sales per month.

In cell B5, enter the formula: =SUM(B2:B4)

Expected Result

Total sales in January = 100 + 90 + 80 = 270

4

Step 4: Copy the monthly total formula across to columns C and D.

Copy the formula from B5 to C5 and D5.

Expected Result

February total = 120 + 110 + 95 = 325; March total = 130 + 115 + 100 = 345

5

Step 5: Create a formula to calculate the percentage contribution of each product's sales in January relative to total January sales, using mixed references.

In cell F2, enter: =B2/B$5

Expected Result

For Apples in January: 100 / 270 ≈ 0.3704 (37.04%)

6

Step 6: Copy the percentage formula across for all months and down for all products.

Copy the formula from F2 across to H2 and down to F4:H4.

Expected Result

Bananas in February: 110 / 325 ≈ 0.3385 (33.85%)

7

Step 7: Explain why the mixed reference B$5 is used in the percentage formula.

The dollar sign fixes the row reference to the totals row, so when copying across the column adjusts to the correct monthly total, and when copying down it stays fixed for that month.

Expected Result

The formula correctly calculates percentages for each product and month using the appropriate monthly total.

Final Result

Product | Jan | Feb | Mar | Total | Jan % | Feb % | Mar %
---------------------------------------------------------
Apples  | 100 | 120 | 130 | 350   | 37.04%| 36.92%| 37.68%
Bananas |  90 | 110 | 115 | 315   | 33.33%| 33.85%| 33.33%
Cherries|  80 |  95 | 100 | 275   | 29.63%| 29.23%| 29.00%
---------------------------------------------------------
Total   | 270 | 325 | 345 |       |       |       |

✓Apples have the highest total sales among the products.

✓March has the highest total sales among the months.

✓Percentage formulas using mixed references allow easy copying without errors.

Bonus Challenge

Create a formula that calculates the percentage contribution of each product's total sales relative to the grand total of all sales, using mixed references.

Show Hint

Use a fixed reference to the grand total cell (sum of all sales) with dollar signs to lock the reference when copying.