1.3 Relative, Absolute and Mixed References
When you copy a formula, Excel adjusts the cell addresses inside it. How it adjusts depends on the $ signs – a relative reference changes, an absolute reference (स्थिर संदर्भ) stays fixed.
| Type | Example | When copied down | When copied right |
|---|---|---|---|
| Relative | A2 |
row changes (A3, A4…) | column changes (B2, C2…) |
| Absolute (स्थिर संदर्भ – copy केल्यावरही न बदलणारा) | $A$2 |
never changes | never changes |
| Mixed – column fixed | $A2 |
row changes | stays column A |
| Mixed – row fixed | A$2 |
stays row 2 | column changes |
Press F4 while the cursor is on a reference in the formula bar to cycle: A2 → $A$2 → A$2 → $A2 → A2.
Steps in Excel – GST on every order
- In
Orders, put the GST rate in a single cell: P1 =5%(fictional flat rate for the example). - In Q2 type
=K2*then click P1 and press F4 so it becomes$P$1. Formula:=K2*$P$1. - Press Enter, then double-click the fill handle (small square at the bottom-right of Q2) to copy down.
- Click Q5: the formula is
=K5*$P$1– the amount moved, the rate stayed fixed.
Worked example – mixed references in a price grid. Salman wants a table of Qty × Unit Price for quick billing at a Solapur store. Quantities 1–5 are in A2:A6, prices ₹30, ₹60, ₹90 in B1:D1. In B2 type:
=$A2*B$1
Copy B2 across to D2 and down to row 6. $A keeps the quantity column fixed; $1 keeps the price row fixed. One formula fills the whole grid.
| Qty | ₹30 | ₹60 | ₹90 |
|---|---|---|---|
| 1 | 30 | 60 | 90 |
| 2 | 60 | 120 | 180 |
| 3 | 90 | 180 | 270 |
Ravindra Bagale's Tip
Sagalyat common chuk mhanje rate kiwa target cell la $ na lavta formula khali copy karne – mag khalchya rows madhe P2, P3… asa refer hota aani result 0 yeto. Formula copy karaychya aadhi swatahala vichara: "copy kelyavar ha cell halla pahije ka?" Nasel tar F4 daba.
Practice task
Build a 5 × 5 multiplication grid (1–5 down, 1–5 across) with a single mixed-reference formula. Then calculate each order's share of the total: =K2/SUM($K$2:$K$9) and format as %.