Every row uses the first item’s price
Locking B2 as $B$2 prevents the base row from changing. Use $B2.
Fill formulas across and down with fixed references
About this lesson
Build a price scenario table while keeping the base-price column, rate row and fixed fee in the right places.
Task requirements
Fill C2:E4 with base price × (1 + rate) + fixed fee. Use the base in column B, the rate in row 1 and the fee in G2.
Teaching · read-only worksheet ·
Step 1 / 5Start with one scenario
More
Inspect the highlighted cells. This step has no automatic change to apply. Teaching is read-only; switch to Your turn to edit.
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: C2, D2, E2, C3, D3, E3, C4, D4, E4. Browser calculations may differ from your Excel version.
Apply your first formula, then fill the answer range. Relative references move; $-locked references stay put.
Copy one formula and compare
Choose two result cells. Copy moves an unlocked row or column by the distance between them; a dollar sign locks the part that follows it. This replaces the destination formula. The table shows applied formulas and calculated values from the current worksheet.
A1 moves both parts; $A$1 locks both; $A1 locks the column; A$1 locks the row. Try a rightward copy and a downward copy before filling the whole table. Equivalent formulas are welcome; passing practice checks the complete report against other sources.
| Cell | Applied formula | Calculated value |
|---|---|---|
| C2 | No formula | — |
| D2 | No formula | — |
| E2 | No formula | — |
| C3 | No formula | — |
| D3 | No formula | — |
| E3 | No formula | — |
| C4 | No formula | — |
| D4 | No formula | — |
| E4 | No formula | — |
Change a source value
Rates use decimals: 0.05 means 5%. Apply a numeric literal to recalculate the current formulas.
Applied value:
Practice stays on this device. No worksheet data is uploaded.
Choose the result you need
The seed in C2 uses $B2, C$1 and $G$2 together. Copying right to E2 changes only the rate column: =$B2*(1+E$1)+$G$2, giving 27. Copying down to C4 changes only the base row, giving 87. At E4 both permitted parts have moved, giving 99. Making B2 fully absolute would incorrectly reuse the first item price; leaving the fee relative would let it drift.
=$B2*(1+C$1)+$G$2A1 can move in both directions; $A$1 fixes both; $A1 fixes the column; A$1 fixes the row. Start in C2, copy across, then down through C2:E4. The base column stays B, the rate row stays 1, and the fee stays G2. Rates are numeric decimals: 0.1 means 10%. Add the fee once, after multiplying. All nine formulas are in the download and the result-formula list. Reference results use the stated decimal values; recalculation can retain tiny binary floating-point residues. Native Excel and Sheets recalculation of these files remains unverified.
All result formulas
- C2
=$B2*(1+C$1)+$G$2- D2
=$B2*(1+D$1)+$G$2- E2
=$B2*(1+E$1)+$G$2- C3
=$B3*(1+C$1)+$G$2- D3
=$B3*(1+D$1)+$G$2- E3
=$B3*(1+E$1)+$G$2- C4
=$B4*(1+C$1)+$G$2- D4
=$B4*(1+D$1)+$G$2- E4
=$B4*(1+E$1)+$G$2
| Row | A | B | C | D | E | G |
|---|---|---|---|---|---|---|
| 1 | "Item" | "Base price" | 0.05 | 0.1 | 0.2 | "Fixed fee" |
| 2 | "Notebook" | 20 | 24 | 25 | 27 | 3 |
| 3 | "Bag" | 50 | 55.5 | 58 | 63 | — |
| 4 | "Lamp" | 80 | 87 | 91 | 99 | — |
These are reference examples with matching downloads. The primary worksheet has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.
Open the primary practiceMicrosoft reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.
All worked examples
Expand an example to read its complete source data and checked results, then download the matching workbook. Cell references belong to that example. Opening it leaves the primary practice and your progress unchanged.
Copy across and down
The seed in C2 uses $B2, C$1 and $G$2 together. Copying right to E2 changes only the rate column: =$B2*(1+E$1)+$G$2, giving 27. Copying down to C4 changes only the base row, giving 87. At E4 both permitted parts have moved, giving 99. Making B2 fully absolute would incorrectly reuse the first item price; leaving the fee relative would let it drift.
=$B2*(1+C$1)+$G$2Enter in C2 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | E | G |
|---|---|---|---|---|---|---|
| 1 | "Item" | "Base price" | 0.05 | 0.1 | 0.2 | "Fixed fee" |
| 2 | "Notebook" | 20 | 24 | 25 | 27 | 3 |
| 3 | "Bag" | 50 | 55.5 | 58 | 63 | — |
| 4 | "Lamp" | 80 | 87 | 91 | 99 | — |
- C2
- 24
- D2
- 25
- E2
- 27
- C3
- 55.5
- D3
- 58
- E3
- 63
- C4
- 87
- D4
- 91
- E4
- 99
A1 can move in both directions; $A$1 fixes both; $A1 fixes the column; A$1 fixes the row. Start in C2, copy across, then down through C2:E4. The base column stays B, the rate row stays 1, and the fee stays G2. Rates are numeric decimals: 0.1 means 10%. Add the fee once, after multiplying. All nine formulas are in the download and the result-formula list. Reference results use the stated decimal values; recalculation can retain tiny binary floating-point residues. Native Excel and Sheets recalculation of these files remains unverified.
Download this example's workbookChange prices and rates
The same copied formulas use new prices, a zero rate in C1 and a fixed fee of 5. C2 is 17: a zero rate keeps the base price of 12, then adds the fee. D2 is 17.96 and E4 is 131.5. Reading the rate from the current result row, or typing the first table results as constants, would not respond to these changed inputs.
=$B2*(1+C$1)+$G$2Enter in C2 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | E | G |
|---|---|---|---|---|---|---|
| 1 | "Item" | "Base price" | 0 | 0.08 | 0.15 | "Fixed fee" |
| 2 | "Mat" | 12 | 17 | 17.96 | 18.8 | 5 |
| 3 | "Stand" | 35 | 40 | 42.8 | 45.25 | — |
| 4 | "Shelf" | 110 | 115 | 123.8 | 131.5 | — |
- C2
- 17
- D2
- 17.96
- E2
- 18.8
- C3
- 40
- D3
- 42.8
- E3
- 45.25
- C4
- 115
- D4
- 123.8
- E4
- 131.5
A1 can move in both directions; $A$1 fixes both; $A1 fixes the column; A$1 fixes the row. Start in C2, copy across, then down through C2:E4. The base column stays B, the rate row stays 1, and the fee stays G2. Rates are numeric decimals: 0.1 means 10%. Add the fee once, after multiplying. All nine formulas are in the download and the result-formula list. Reference results use the stated decimal values; recalculation can retain tiny binary floating-point residues. Native Excel and Sheets recalculation of these files remains unverified.
Download this example's workbookKeep zero and discount rates
The first price and the fixed fee are both numeric zero. Their results remain zero, rather than blank or missing. A rate of -0.1 applies a 10% discount: the price of 25 becomes 22.5 and the price of 200 becomes 180. The zero-rate column keeps 25 and 200, while the 0.25 column gives 31.25 and 250. The locks stay the same for all three conditions.
=$B2*(1+C$1)+$G$2Enter in C2 in this example's workbook. The download contains the complete source and all result formulas.
| Row | A | B | C | D | E | G |
|---|---|---|---|---|---|---|
| 1 | "Item" | "Base price" | -0.1 | 0 | 0.25 | "Fixed fee" |
| 2 | "Sample" | 0 | 0 | 0 | 0 | 0 |
| 3 | "Cable" | 25 | 22.5 | 25 | 31.25 | — |
| 4 | "Desk" | 200 | 180 | 200 | 250 | — |
- C2
- 0
- D2
- 0
- E2
- 0
- C3
- 22.5
- D3
- 25
- E3
- 31.25
- C4
- 180
- D4
- 200
- E4
- 250
A1 can move in both directions; $A$1 fixes both; $A1 fixes the column; A$1 fixes the row. Start in C2, copy across, then down through C2:E4. The base column stays B, the rate row stays 1, and the fee stays G2. Rates are numeric decimals: 0.1 means 10%. Add the fee once, after multiplying. All nine formulas are in the download and the result-formula list. Reference results use the stated decimal values; recalculation can retain tiny binary floating-point residues. Native Excel and Sheets recalculation of these files remains unverified.
Download this example's workbookHow it works
Four reference forms
A1 can move in both directions. $A$1 cannot move in either. $A1 fixes the column but allows the row to move. A$1 fixes the row but allows the column to move. Pick the form based on where the formula will be copied.
Copying is part of the task
A formula can be correct in C2 and fail after filling. Test one cell to the right and one cell below before filling a larger table. A correct scenario table changes when any base price, rate or the fixed fee is edited.
Read the input row
C1:E1 hold numeric rates even though they sit on the top row. Enter 0.05 for 5%, not 5. The fee is added once after applying the rate to the base price.
Read the full step-by-step walkthrough
- Start with one scenario
C2 uses the base price in B2, the rate in C1 and the fee in G2. Rates are decimals: 0.1 means 10%.
B2*(1+C1)+G2 - Keep the base-price column
Use $B2. Across the row, the B stays fixed; down the table, row 2 is allowed to change.
$B2 - Keep the rate row
Use C$1. Down the table, row 1 stays fixed; across the table, C can become D or E.
C$1 - Keep the one fee cell
Every scenario uses G2. Lock its column and row so neither changes during copying.
$G$2 - Fill across, then down
Copy C2 into D2:E2, then fill the row through row 4. Compare formulas as well as values. Each cell should use its own base row and rate column.
=$B2*(1+C$1)+$G$2
Things to watch
The rate moves into the result area
Lock the rate row with C$1 before filling down.
The fee drifts to a blank cell
G2 must be fully absolute: $G$2.
About this example
Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.
Content version 2. Practice checks compare calculated results across different datasets; they do not certify every possible formula.