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
Many students give a range a name, but when new rows are added the range doesn't grow and the new data doesn't reach the formula. For growing data, first make an Excel Table (Module 6) and use its structured reference, or update the range from Name Manager. Keep the names meaningful too – not x1 or abc.
Ravindra Bagale's Tip – मराठी
बरेच students range ला नाव देतात, पण नवीन rows add केल्यावर range वाढत नाही आणि नवीन data formula मध्ये येत नाही. वाढणाऱ्या data साठी आधी Excel Table (Module 6) बनवा आणि त्याचा structured reference वापरा, किंवा Name Manager मधून range update करा. नावंही अर्थपूर्ण ठेवा – x1, abc नको.
Ravindra Bagale's Tip – हिंदी
बहुत से students range को नाम दे देते हैं, पर नई rows जोड़ने पर range नहीं बढ़ती और नया data formula में नहीं आता. बढ़ने वाले data के लिए पहले Excel Table (Module 6) बनाओ और उसका structured reference इस्तेमाल करो, या Name Manager से range update करो. नाम भी मतलब वाले रखो – x1, abc नहीं.
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.