1.3 Relative, Absolute and Mixed References
Absolute reference ($) with example — in 30 seconds
What: A $ sign locks a cell address, so it does not move when you copy the formula down.
Why: A share-of-total formula must divide every row by the same total cell. Without $ the total cell slides down and you get #DIV/0! or wrong answers.
Tiny example: Pune ₹544 out of a total of ₹2,203 is 24.7%. The total sits in B5, so we write =B2/$B$5.
Absolute reference ($) with example — 30 सेकंदात
काय: $ चिन्ह cell address ला lock करतं, म्हणजे formula खाली copy केला तरी तो address हलत नाही.
का: Share of total काढताना प्रत्येक row ला एकाच total ने भागायचं असतं. $ नसेल तर total cell खाली सरकते आणि #DIV/0! किंवा चुकीचं उत्तर येतं.
छोटं उदाहरण: एकूण ₹2,203 पैकी Pune चे ₹544 म्हणजे 24.7%. Total B5 मध्ये आहे, म्हणून आपण =B2/$B$5 लिहितो.
Absolute reference ($) with example — 30 सेकंड में
क्या: $ चिह्न cell address को lock करता है, यानी formula नीचे copy करने पर भी वह address नहीं हिलता.
क्यों: Share of total निकालते समय हर row को एक ही total से भाग देना होता है. $ न हो तो total cell नीचे खिसक जाता है और #DIV/0! या गलत जवाब आता है.
छोटा उदाहरण: कुल ₹2,203 में से Pune के ₹544 यानी 24.7%. Total B5 में है, इसलिए हम =B2/$B$5 लिखते हैं.
First look at the data (before → after):
Expected result — Share of total in column C (format %)
| A: City | B: Amount (₹) | C: Share |
|---|---|---|
| Pune | 544 | 24.7% |
| Nashik | 360 | 16.3% |
| Solapur | 1299 | 59.0% |
| Total | 2203 | 100.0% |
About this data: Teaching sample for a fictional shop “Skyline Clinics”. Figures and names (e.g. Rohan Patil) are made up for this lesson on relative absolute and mixed references.
Before table and formula as text
Before — City sales (B5 holds the total)
| Row | A: City | B: Amount (₹) |
|---|---|---|
| 2 | Pune | 544 |
| 3 | Nashik | 360 |
| 4 | Solapur | 1299 |
| 5 | Total | 2203 |
About this data: Demo table only — not real customer or company data. Built to practise relative absolute and mixed references.
Formula / click path
C2: =B2/$B$5 → copy down to C4
Wrong: =B2/B5 becomes =B3/B6 in C3 (B6 is empty → #DIV/0!)
Do it step by step:
- Type the three city amounts in B2:B4 and
=SUM(B2:B4)in B5. - Click C2 and type
=B2/B5. Do not press Enter yet. - Click inside B5 in the formula and press F4 once. It becomes
$B$5. - Press Enter, then double-click the fill handle to copy down to C4.
- Select C2:C4 and click Home › Number › Percent Style.
- Check: the three shares add up to 100.0%.
स्टेप बाय स्टेप करून बघा:
- तीन शहरांचे amount B2:B4 मध्ये टाका आणि B5 मध्ये
=SUM(B2:B4)टाका. - C2 वर click करा आणि
=B2/B5type करा. अजून Enter दाबू नका. - Formula मधल्या B5 वर cursor ठेवा आणि F4 एकदा दाबा. ते
$B$5होईल. - Enter दाबा, मग fill handle वर double-click करून C4 पर्यंत copy करा.
- C2:C4 select करा आणि Home › Number › Percent Style वर click करा.
- Check करा: तिन्ही share मिळून 100.0% व्हायला हवेत.
स्टेप बाय स्टेप करके देखें:
- तीन शहरों के amount B2:B4 में डालें और B5 में
=SUM(B2:B4)डालें. - C2 पर click करें और
=B2/B5type करें. अभी Enter न दबाएँ. - Formula में B5 पर cursor रखें और F4 एक बार दबाएँ. यह
$B$5बन जाएगा. - Enter दबाएँ, फिर fill handle पर double-click करके C4 तक copy करें.
- C2:C4 select करें और Home › Number › Percent Style पर click करें.
- Check करें: तीनों share मिलाकर 100.0% होने चाहिए.
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 |
About this data: Made-up rows so you can copy the pattern for relative absolute and mixed references. Currencies may use ₹, £, € or $.
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 |
About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.
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 %.
Loading practice tasks...
- Total deals won (Easy): Add up Deals won in the "Deals won" column.
- Average avg deal value (Easy): Average of the Avg deal value (£) column, rounded to 2 decimals.
- Rows in North (Easy): How many rows have Region "North"?
- Largest deals won (Easy): The largest value in the "Deals won" column.
- Deals won for North (Medium): Total of the Deals won column where Region is "North".
- North in Leeds (Medium): Total of the Deals won column where Region is "North" and City is "Leeds".
- Best in North (Medium): Largest value in the "Avg deal value (£)" column where "Region" is "North".
- Value of row 2 (Medium): Multiply the first-row Deals won by the first-row Avg deal value (£).
- Look up Liam O'Brien (Medium): The Deals won for "Liam O'Brien" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Deals won" times "Avg deal value (£)" for all rows.
- Share of North (Hard): Share of the "Deals won" column that is "North", rounded to 3 decimals.
- Share of the total "Avg deal value (£)" with a locked range (Hard): Find what share of the total "Avg deal value (£)" the first row has. Round it to 3 decimals.
- Above average? (Hard): "Above" if the "Avg deal value (£)" value in the first row is above the average of the "Avg deal value (£)" column, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
