Ravindra BagaleCourses & study guides मराठी Track your progress

1. Excel Basics

1.8 Named Ranges

By Last updated:

Named range with example — in 30 seconds

What: A named range gives a cell or range a friendly name, like GST_Rate, that you can use in formulas.

Why: =B2*GST_Rate is easier to read and check than =B2*Settings!$B$1. If the rate changes, you change one cell.

Tiny example: GST_Rate holds 5%. For an order of ₹270, =B2*GST_Rate gives ₹13.50.

First look at the data (before → after):

Named range with example: before and after tables, fictional FreshGo Maharashtra data

Expected result — GST at 5% in column C

A: Order ID B: Amount (₹) C: GST (₹)
BLK-1001 64 3.20
BLK-1002 270 13.50
BLK-1007 1299 64.95

About this data: Teaching sample for a fictional shop “HomeNest Supplies”. Figures and names (e.g. Priya Deshmukh) are made up for this lesson on named ranges.

Before table and formula as text

Before — Orders, and the rate cell Settings!B1 = 5%

A: Order ID B: Amount (₹)
BLK-1001 64
BLK-1002 270
BLK-1007 1299

About this data: Demo table only — not real customer or company data. Built to practise named ranges.

Formula / click path

Formulas › Define Name › Name: GST_Rate · Refers to: =Settings!$B$1
C2: =B2*GST_Rate   → copy down

Do it step by step:

  1. Make a sheet named Settings and type 5% in B1.
  2. Click B1, then Formulas › Define Name.
  3. Type GST_Rate in Name and click OK.
  4. Go to the Orders sheet and type =B2*GST_Rate in C2.
  5. Copy it down. Change B1 to 12% once and watch every row update.

A named range gives a cell or range a meaningful name, like GST_Rate instead of $P$1.

Steps in Excel

  1. Quick way: select P1 › click the Name Box › type GST_Rate › Enter.
  2. Define Name: Formulas › Defined Names › Define Name › Name, Scope (Workbook/sheet), Refers to.
  3. From headers: select a table with headers › Formulas › Defined Names › Create from Selection (Ctrl + Shift + F3) › tick Top row.
  4. Manage/edit/delete: Formulas › Defined Names › Name Manager (Ctrl + F3).
  5. 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.

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.

Loading practice tasks...

  1. Total parcels (Easy): Add up the "Parcels" column.
  2. Average cost (Easy): Average of the Cost (£) column, rounded to 2 decimals.
  3. Rows in Royal Mail (Easy): How many rows have Carrier "Royal Mail"?
  4. Largest parcels (Easy): The largest value in the "Parcels" column.
  5. Parcels for Royal Mail (Medium): Total of the Parcels column where Carrier is "Royal Mail".
  6. Royal Mail in Bristol (Medium): Total of the Parcels column where Carrier is "Royal Mail" and City is "Bristol".
  7. Best in Royal Mail (Medium): Largest value in the "Cost (£)" column where "Carrier" is "Royal Mail".
  8. Value of row 2 (Medium): Multiply the first-row Parcels by the first-row Cost (£).
  9. Look up Ishaan Verma (Medium): The Parcels for "Ishaan Verma" (use a lookup on the "Name" column).
  10. Total value (Hard): Sum of "Parcels" times "Cost (£)" for all rows.
  11. Share of Royal Mail (Hard): Share of the "Parcels" column that is "Royal Mail", rounded to 3 decimals.
  12. Share of the total "Cost (£)" with a locked range (Hard): Find what share of the total "Cost (£)" the first row has. Round it to 3 decimals.
  13. Above average? (Hard): "Above" if the "Cost (£)" value in the first row is above the average of the "Cost (£)" column, else "Below".

See all practice tasks in the Task Hub →

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.

Revision card 1: What is Named Ranges?
Card 1: What is Named Ranges? · Download PNG
Revision card 2: Tiny example — Named Ranges
Card 2: Tiny example — Named Ranges · Download PNG
Revision card 3: Checklist — Named Ranges
Card 3: Checklist — Named Ranges · Download PNG