1.8 Named Ranges
A named range gives a cell or range a meaningful name, like GST_Rate instead of $P$1.
Steps in Excel
- Quick way: select P1 › click the Name Box › type
GST_Rate› Enter. - Define Name: Formulas › Defined Names › Define Name › Name, Scope (Workbook/sheet), Refers to.
- From headers: select a table with headers › Formulas › Defined Names › Create from Selection (Ctrl + Shift + F3) › tick Top row.
- Manage/edit/delete: Formulas › Defined Names › Name Manager (Ctrl + F3).
- Use:
=K2*GST_Rate. Press F3 while typing a formula to paste a name.
Rules: names start with a letter or underscore, have no spaces (use _), and cannot look like a cell address (GST1 is fine, A1 is not).
Worked example. In Stores, Rani names B2:B16 as StoreCity and C2:C16 as StoreArea. The formula =COUNTIF(StoreCity,"Pune") returns 5 dark stores in Pune (Kothrud, Hinjewadi, Baner, Hadapsar, Wakad). Names are absolute by default, so copying the formula is safe.
Ravindra Bagale's Tip
Khup students range la nav detat, pan navin rows add kelyavar range vadhat nahi aani navin data formula madhe yet nahi. Growing data sathi aadhi Excel Table (Module 6) banva aani tyacha structured reference vapra, kiwa Name Manager madhun range update kara. Nav pan arthapurna theva – x1, abc nako.
Practice task
Name the GST cell GST_Rate and the Amount column OrderAmount. Write =SUM(OrderAmount)*GST_Rate. Open Name Manager and change the scope of one name to the Orders sheet only.
Thodkyaat sangaycha tar (quick recap)
Formula bar madhe khara content bagha, $ ne references lock kara (F4), ₹ aani dates format ne dakhva – type karu naka, custom format ne value na badalta display badla, Freeze Panes ne header dakhva aani named ranges ne formulas vachayla sope kara. Aata pudhe jaauya – data entry tools kade.