Build formulas with reliable references

Use relative, absolute and mixed references in an order sheet and a price grid.

Content reviewed 13 October 2026 · How we check lessons

Loading your progress…

In this lesson

Fill a line calculation, fix a shared percentage and build a multiplication grid from one formula.

Example

One tax input for the whole order

Enter Item, Quantity and Price in A1:C4: Keyboard 2 30, Monitor 1 180 and Dock 3 40. Put the practice sales-tax rate 0.10 in F2. Every line should use that same cell, even when you copy the formula.

How it works

Relative references move in both directions. Absolute references fix the row and column; mixed references fix just one, which is useful for a grid with headers across the top and down the side.

Excel multiplies before it adds. Parentheses group the calculation so a percentage applies to the intended subtotal.

Steps

1
Enter the order records and type Gross in D1. Enter the formula below in D2, fill down to D4 and inspect each row reference in the formula bar.
```
=B2*C2
```
English function names. Formula settings →
Type WithTax in E1. Enter =D2*(1+$F$2) in E2 and fill down. Each formula should use its own D-row subtotal and the same $F$2 rate.
Change F2 from 0.10 to 0.20. Check that all three WithTax results change, then restore 0.10.
Away from the order list, enter prices 10, 20 and 30 in H1:J1 and quantities 1, 2 and 3 in G2:G4.
5
Enter the formula below in H2. Fill across to J2 and down through row 4. Inspect J4 to see which header references moved.
```
=H$1*$G2
```
English function names. Formula settings →
On desktop, select F2 and use Formulas > Define Name, where available, to call it TaxRate. Compare =D2*(1+TaxRate) with the formula using $F$2 and add the rate to your Readme.

Try it yourself

Add Headset with Quantity 4 and Price 15 as another purchasing row. Extend both formulas and calculate gross and with-tax grand totals without including the separate grid.
Copy the grid formula into J4 and explain why it reads =J$1*$G4. Predict its answer before looking at the result.

Show expected result

Initial gross totals are 60, 180 and 120; at ten percent tax the corresponding results are 66, 198 and 132, totaling 396.
The headset adds 60 gross and 66 with tax, making totals 420 and 462. Grid cell J4 returns 90 from quantity 3 and price 30.

If something goes wrong

The tax formula uses F3 or F4 after filling.: Replace the rate reference with $F$2 or TaxRate and fill down again. Check the formula bar in the final row.
Every grid row uses quantity 1.: Check the quantity reference. $G2 locks column G but lets row 2 move to row 3 or 4; $G$2 would fix both.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. Which reference fixes both the row and column?

F2$F$2F$2

2. What should J4 return in the quantity-price grid?

903060

3. Which formula applies ten percent to the whole subtotal D2?

=D2+1*F2=D2*(1+$F$2)=D2+F2

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Explain how H$1 and $G2 change when copied right and down. Check that the order formulas keep the rate in F2 fixed.