Using Named Ranges in Excel: Simplify Your Formulas

Named ranges in Excel help us in assigning meaningful names to a range of cells. We often see formulas like =SUM(B2:B20)and wonder what is the content of range B2:B20. The user who wrote this formula may know but the other users will have to spend some time to figure this. However, if we give a name like ‘Sales_revenue’ or ‘Salary’ to this range, other users too will find it readable and easy to navigate.

We will discuss how to give names to ranges in this guide. We will also give real-world examples and practice questions. This guide is specially helpful for beginners, who have just started writing formulas, as using named ranges in Excel will help them clean up the spreadsheet and supercharge the formulas.

🚀 What is a Named Range?

As the name suggests, a named range in Excel is a descriptive name that we can assign to a range of cells for making it more readable. For e.g., instead of writing A2:A10 in the formula, we can name this range ‘Items_Sold’ and then use this name in the formulas.

Compare yourself as which one is more readable –

=SUM(A2:A10)

The second one instantly suggests that we are doing the sum of number of items sold. In the first one, we will have to look up what A2:A10 actually holds.

🛠️ How to Create a Named Range

We can create named ranges in Excel in many ways. Let’s have a look at them –

Method 1: Using the Name Box

1. First select the range of cells where we want to assign a name (For e.g., B1:B10).

2. Then, click on the Name Box. You can find it on the top-left corner near the formula bar.

3. Now type a name like Sales and hit Enter.

✅ That’s it! Now the range B1:B10 has a name – ‘Sales’.

Method 2: Using the “Define Name” Dialogue Box

1. Locate the Formulas tab on the ribbon.

2. Click on Define Name. It will open a dialogue box.

3. Now enter the values like name and the range. For e.g., Expensesin name and =Sheet1!B2:B20in the range. Click OK.

Using this method, you get more control like adding comments and specifying the scope (workbook or worksheet).

🧠 Naming Rules You Must Follow

We have to take care of following small but important rules while naming the ranges –

• We must start the name with a letter, underscore (_), or backslash (\).

• We can not use spaces. However, we can use letters, numbers, underscores. So far naming a range as ‘Sales Revenue’, we may write ‘Sales_Revenue’.

• We can not name a range A1 or B5, meaning no cell reference names are allowed.

• The name can have max 255 characters.

✅ Why Use Named Ranges?

1. Better Readability: =SUM(Sales2024) is certainly easier to understand than =SUM(B2:B20)
2. Fewer Errors: As the defined name pops up, you can avoid selecting the wrong range
3. Easier Updates: You just need to change the range in one place and it will auto-update across your sheet

💡 Examples of Using Named Range in Excel –

1. Basic SUM

2. Conditional Formula

Let’s say we have a range named Marks.Now we can use this range in the conditional formula as below –

3. VLOOKUP with Named Range

If you’ve named your table range as EmployeeData:

🧪 Practice Questions on Named Ranges in Excel –

Thus, it is time to define names ranges on your own for these practice questions-

1. We have an expense column in the sheet. Give it a name and then calculate the total expense using SUM().
2. We have a column ‘marks’. Now name it as Scoresand then write a formula to count how many scores are above 80.
3. Try renaming a named range from Data1 to ClientData. (Tip: Use the Name Manager.)
4. Use a named range in a VLOOKUP to find employee salaries.

If you get stuck in any of these, please drop your query in comments.

📝 Pro Tips

• We can hit F3 to select and enter a named range into the formula.

• (Ctrl + F3) opens up Name Manager, which helps us to quickly view, edit, and eventually manage all our named ranges.

• We can also use named ranges in Data Validation, Charts, and Pivot Tables.

📌 Summary

Named ranges in Excel may seem like a small feature. However, if used properly, they can deliver a professional look to our sheet and enhance readability. Named ranges also help in reducing errors and simplifying formulas,

Thus, use named ranges the next time you’re working on an Excel sheet and you will definitely see the change.

Also check out our other articles on –

Waterfall, Pareto, and Funnel Charts in Excel: A Beginner’s Guide

Gantt Charts in Excel: Step‑by‑Step Tutorial + Free Template 

How to Use Flash Fill in Excel: Save Time on Data Entry

Microsoft’s Official Guide on Named Ranges