Using range names in Excel (2023)

  1. Newsletter

Using range names in Excel (1)

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA

Using range names in Excel allows you to quickly navigate to areas of your worksheet and makes formulas much easier to create. A range name is simply a name you assign to a range of data and is much easier to remember than a cell address.

How to name a range

Suppose you have the following data, and you would like to name cells B3 through D3 as Education.

Using range names in Excel (2)

To name a range, select the cells you would like to name. In this example, select cells B3:D3. Click the Name box to the left of the Formula bar and type "Education." Hit Enter to create the name.

Using range names in Excel (3)

Note that range names cannot have spaces.

Working with range names

You can now use the range name in a formula. In the formula, you will substitute the name of the range for the cells you would typically reference. Let's sum the Education range as an example. Click in cell E3 and type the formula =SUM(Education) and hit Enter.

Using range names in Excel (4)

The formula returned the sum of the range name named Education (B3:D3).

Viewing range names

If you have forgotten what range names you have used, you can view all the range names that are in your workbook. To do this, click the drop-down arrow in the Name box to see the named ranges in the workbook.

Using range names in Excel (5)

Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent, Ohio; Jennifer Cainas, CPA, DBA, is a clinical professor at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. Their website, Accounting is Analytics, has resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact Courtney Vien at


Using range names in Excel? ›

Select the range you want to name, including the row or column labels. Select Formulas > Create from Selection. In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top row,Left column, Bottom row, or Right column check box. Select OK.

How do I use named ranges in Excel? ›

Create a named range from selected cells in a worksheet
  1. Select the range you want to name, including the row or column labels.
  2. Click Formulas > Create from Selection.
  3. In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ...
  4. Click OK.

What is a benefit of using a range name in Excel? ›

Using range names in Excel allows you to quickly navigate to areas of your worksheet and makes formulas much easier to create. A range name is simply a name you assign to a range of data and is much easier to remember than a cell address.

How do you reference a named range? ›

Just select the name of interest in the Excel Name Manager, and type a new reference directly in the Refers to box, or click the button at the right and select the desired range on the sheet. After you click the Close button, Excel will ask if you want to save the changes, and you click Yes.

How do you use named ranges instead of cell references? ›

Follow these steps to change the range reference:
  1. On the Ribbon, click the Formulas tab.
  2. Click Name Manager.
  3. In the list, click on the name that you want to change.
  4. In the Refers To box, change the range reference, or drag on the worksheet, to select the new range.
  5. Click the check mark, to save the change.
Jun 22, 2022

Why would you use named ranges? ›

Conclusion: naming ranges in Excel has many advantages, making your workbooks easier to read, making formulas faster to type and debug, and allowing to create ranges with variable-size to make your spreadsheets dynamic and easy to update.

What are the disadvantages of Excel name ranges? ›

So, what are the disadvantages of name ranges? Well, at first, if you don't use them often, they can seem just a little bit fiddly. The second disadvantage of name ranges is that they don't lend themselves to copying relative references. However, there are some workarounds for this when we are creating name ranges.

What is the biggest benefit of creating a named range? ›

The advantages of using named ranges include:
  • You don't have to physically select the cell range each time you want to use it.
  • You don't have to remember the cell reference.
  • Named ranges can be managed easily by using Name Manager.
  • Navigation to your named range is much easier through the Name Box feature.
Oct 28, 2021

How do you use a range name in a formula? ›

Use names in formulas
  1. Select a cell and enter a formula.
  2. Place the cursor where you want to use the name in that formula.
  3. Type the first letter of the name, and select the name from the list that appears. Or, select Formulas > Use in Formula and select the name you want to use.
  4. Press Enter.

How do I find unique names in a range in Excel? ›

To filter for unique values, click Data > Sort & Filter > Advanced. To remove duplicate values, click Data > Data Tools > Remove Duplicates. To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.

How to use named range in data validation? ›

Go to Data > Data validation. On the sidebar, click “Add a rule”. Select “Dropdown (from a range)” under 'Criteria'. In the range selection box, type the name of your range and press 'Enter'.

What is an example of a named range being used? ›

After you define Excel names, you can use the names in a formula, instead of using a constant value or cell references. For example, a cell that contains the revenue for each state could be named Revenue or Rev. Then you could use functions and reference the named range. Example: =Sum(Revenue) or =Average(Revenue).

What is a named range vs named table? ›

A table is a defined grid of cells for data and formulas that automatically expands as you add to it and also automatically has the capacity to sort and filter. Creating a table also automatically adds a named range to your worksheet. A named range is just one or more cells to which you, or Excel, have assigned a name.

How do I assign a defined name to a range of cells in Excel? ›

Define a name for a cell or cell range on a worksheet
  1. Select the cell, range of cells, or nonadjacent selections that you want to name.
  2. Click the Name box at the left end of the formula bar. Name box.
  3. Type the name you want to use to refer to your selection. Names can be up to 255 characters in length.
  4. Press ENTER.

What is not allowed in range names? ›

The name string must begin with a letter or underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Spaces are not allowed, but two words can be joined with an underscore (_) or period (.).

Do named ranges slow down Excel? ›

The only problem is that named ranges, especially dynamic ranges, consume a considerable amount of working memory. Overusing them may slow down your Excel spreadsheet.

What are two uses of ranges? ›

It provides an indication of statistical dispersion. It is always measured in the same units as the data. As it only depends on two of the observations, the range is useful for showing the spread within a dataset and. And also for comparing the spread between similar datasets.

What is the advantage of assigning a name to a cell or ranges? ›

Version 2 uses named ranges, making its formulas much easier to understand. Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from, whenever you start to type a name in a formula.

What is the difference between name range and table in Excel? ›

Range in Excel means any group of selected cells. It is defined by the reference of the top-left cell and the bottom-right cell. For example, in the left-hand part of the image below, the data is in the range A1:C6. Excel table is a structured and pre-formatted dynamic range of cells that has its own name.

Do named ranges increase file size? ›

Typically, Named Ranges will not significantly increase file size. Sometimes, however, they get corrupted in some fashion and add significantly to the file size. We recently received a workbook which contained over 2,600 Named Ranges, many of which were hidden and included external links.

Where a professional would use named cells? ›

Naming cells is used in businesses to store data and modify and refer them whenever necessary .

Can you use a named range instead of a cell address in a formula? ›

You can use a named range instead of a cell address in a formula. Answer: The statement is true. MS Excel allows us to assign names to cells in a worksheet and use it for quickly locating specific cells by entering the names. This could be useful when working with large spreadsheets.

What is a dynamic range in Excel? ›

Dynamic named range in Excel is the ranges that change as the data in the range changes, and the dashboard or charts or reports associated with them. So that is why it is called dynamic. So we can name the range from the name box, so the name is a dynamic name range.

Can you create multiple range names in Excel? ›

Save Time Referencing Ranges with the Ctrl key

When writing formulas we sometimes need to create references to multiple cells or ranges. One quick way to do this is by holding the Ctrl key and then selecting the cells or ranges. Excel will automatically add the commas between the range references in the formula.

How do I count a list of names in Excel without duplicates? ›

Count the number of unique values by using a filter
  1. Select the range of cells, or make sure the active cell is in a table. ...
  2. On the Data tab, in the Sort & Filter group, click Advanced. ...
  3. Click Copy to another location.
  4. In the Copy to box, enter a cell reference. ...
  5. Select the Unique records only check box, and click OK.

How do you create a list from a named range? ›

When you select the cells of a named range, you'll see the name in the name box. Now, you are ready to create a drop-down list that uses the named range. Select the cell where you want a drop-down list. Click Data Validation, select List, click in Source, press F3, select the name, click OK, and click OK again.

What is the benefit of using a range name in Excel quizlet? ›

What is a benefit of using a range name in Excel? Range names are easier for people to remember than cell references.

What is one benefit of using range names in formulas quizlet? ›

A range name is a word or a string of characters that represent one or more cells. A Range name can contain up to 1000 characters but it must begin with a number. One benefit of using range names is that if you copy the formula, you do not have to make the cell reference absolute in the formula.

What is the purpose of range in spreadsheet? ›

Range in statistics is simply the difference between the highest and lowest values in a set of data. For example, the lowest to the highest values of a salary dataset might be $10,000 to $50,000. In this case, the range of the data is $40,000, as $50,000 - $10,000 = $40,000.

What is the advantage of finding range? ›

This represents the difference between the smallest and largest values in the dataset. In statistics, the range is important for the following reasons: Reason 1: It tell us the spread of the entire dataset. Reason 2: It tells us what extreme values are possible in a given dataset.

Why would we use descriptive range names in formulas instead of using cell addresses? ›

Naming your ranges allows you use easily recognizable names in your formulas instead of cell addresses. Immediately, you can see how much easier it is to understand what is going on in the formula. The formula is easier not only to read but also to explain to others who aren't familiar with the workbook.


Top Articles
Latest Posts
Article information

Author: Rubie Ullrich

Last Updated: 23/11/2023

Views: 6300

Rating: 4.1 / 5 (72 voted)

Reviews: 95% of readers found this page helpful

Author information

Name: Rubie Ullrich

Birthday: 1998-02-02

Address: 743 Stoltenberg Center, Genovevaville, NJ 59925-3119

Phone: +2202978377583

Job: Administration Engineer

Hobby: Surfing, Sailing, Listening to music, Web surfing, Kitesurfing, Geocaching, Backpacking

Introduction: My name is Rubie Ullrich, I am a enthusiastic, perfect, tender, vivacious, talented, famous, delightful person who loves writing and wants to share my knowledge and understanding with you.