Copy formulas while keeping shared inputs fixed

Use relative, absolute, and mixed references to apply one discount rule across several rows and columns.

Before you start

Windows desktop: Excel 2016, 2019, 2021, 2024, and Microsoft 365. Mac/web menus and shortcuts differ. No dynamic arrays required.

Useful first: Build your first formulas from cell references

By the end, you can…

Keep row-specific references relative when filling down.
Lock a shared rate with an absolute reference.
Use a mixed reference for a small two-dimensional calculation grid.

YOUR PRACTICE FILE

Small data. A result you can check.

Data only. Paste at A1 on a fresh sheet. Enter the rate and the separate scenario grid manually at the exact cells in the lesson.

Download practice data

View the raw practice data

Item	Base amount
Lamp	100
Tray	200
Hook	50

01One row changes; one rate stays shared

Create a References sheet and paste at A1. Add Discount in C1 and Net amount in D1. Enter Rate in E1 and 10% in E2. The shared rate is numeric 0.1.

In C2, enter =B2*$E$2. The expected discount is 10. B2 belongs to the current item, while E2 is a shared setting. The dollar signs lock both the column and row of the rate reference when the formula is copied.

=B2*$E$2

Enter in C2, then select C2:C4 and use Home > Fill > Down. Expected discounts: 10, 20, 5.

02Inspect a copied formula, not just its answer

After filling down, C3 should contain =B3*$E$2 and return 20. C4 should contain =B4*$E$2 and return 5. The price row advances, but the rate remains E2. In D2 enter =B2-C2, then fill D2:D4. Net amounts are 90, 180, and 45.

If C2 instead used =B2*E2, copying it down would move the rate reference to E3 and then E4. Those cells are blank in this example, so the later discounts would misleadingly become zero. If you locked $B$2 too, every item would use the first price. Decide what should move before adding dollar signs.

03Read the four reference shapes

B2 is relative in both directions. $B$2 fixes both parts. $B2 fixes the column but allows the row to change; B$2 fixes the row but allows the column to change. These rules describe copying and filling, not every possible structural edit to a worksheet.

While editing a reference on Windows, F4 cycles reference styles; some keyboards need Fn too. Typing dollar signs always avoids shortcut ambiguity. Mac and web keyboard behavior differs.

Reference	When copied right one column and down one row
B2	C3
$B$2	$B$2
$B2	$B3
B$2	C$2

04Use mixed references for a scenario grid

For a second practice area, enter 10% in H4, 20% in I4, 100 in G5, and 200 in G6. In H5 enter =$G5*H$4. Fill H5:I5 to the right, then select H5:I6 and fill down.

The fixed G column supplies each price; fixed row 4 supplies each rate. Row 5 returns 10 and 20; row 6 returns 20 and 40. The grid compares rates without hard-coding each calculation.

PUT IT INTO PRACTICE

Create reusable row discounts and a two-rate scenario grid.

Prepare B2:B4 and E2 as described, calculate C2:C4 and D2:D4.
Change E2 from 10% to 20%, without editing any result formula.
Create the separate H5:I6 grid using the specified headers and mixed reference.
Inspect C4 and I6 in the formula bar.

Show the worked answer

At 20%, C2:C4 become 20, 40, 10, and D2:D4 become 80, 160, 40.

C4 remains =B4*$E$2.

The separate scenario grid remains 10/20 and 20/40 because it uses H4:I4, not E2.

I6 contains =$G6*I$4 and returns 40.

Check your work

Each row uses its own base amount.
Every main discount uses only the shared E2 rate.
The grid locks the correct row and column independently.

CHECK YOUR UNDERSTANDING

One question before you move on.

You copy =B2*$E$2 from C2 down one row. What should C3 contain?=B2*$E$2=B3*$E$3=B3*$E$2

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.

NEXT LESSONChoose the right total, average, and count

Keep the skill close.

FIELD GUIDEVLOOKUP Between Two Sheets in Excel: Exact Matches and Missing IDs FIELD GUIDEExcel SUMIFS Date Range: Include the Whole End Date, Even with Times

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.