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
The most common mistake is copying a formula down without putting $ on the rate or target cell – then the rows below refer to P2, P3… and the result comes out as 0. Before copying a formula, ask yourself: "Should this cell move when I copy?" If not, press F4.
Ravindra Bagale's Tip – मराठी
सगळ्यात common चूक म्हणजे rate किंवा target cell ला $ न लावता formula खाली copy करणं – मग खालच्या rows मध्ये P2, P3… असा refer होतो आणि result 0 येतो. Formula copy करण्याआधी स्वतःला विचारा: "copy केल्यावर हा cell हलला पाहिजे का?" नसेल तर F4 दाबा.
Ravindra Bagale's Tip – हिंदी
सबसे common गलती है rate या target cell पर $ लगाए बिना formula नीचे copy करना – फिर नीचे की rows में P2, P3… refer होने लगता है और result 0 आता है. Formula copy करने से पहले खुद से पूछो: "copy करने पर क्या यह cell खिसकना चाहिए?" अगर नहीं, तो F4 दबाओ.
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 %.