How To Edit Named Ranges in Excel in 6 Steps (With Tips)

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.
6. Click Close, to close the Name Manager TOP.

Microsoft Excel is a powerful and widely used software program that allows users to create, analyze, and store data in various formats. One of the most important tools to master in Excel is the ability to edit named ranges. Named ranges provide a great way to quickly refer to data or formulas within a spreadsheet, allowing users to make quick changes or calculations while working with large amounts of data. This blog post will provide a detailed overview of how to edit named ranges in Excel. We will discuss how to create, delete, and make changes to named ranges as well as some of the common pitfalls to avoid when working with named ranges. By the end of this post, readers should have a solid understanding of how to edit named ranges in Excel and be able to do so with confidence.

How to Edit or Delete: Named Range in MS Excel | Step by Step process | 2020

Benefits of named ranges

Some benefits of using named ranges may include:

Finding data easily

Excel worksheets frequently contain a lot of data, which can make it challenging to navigate or remember where specific information is located. A named range for a set of cells functions like a bookmark. The “Name Box” in Excel can be used to search for a named range instead of scrolling through the worksheet or using general search functions. Excel finds the named range, highlights it, and directs you to it when you type the name of the range and press “Enter.”

Improving efficiency

The same data can be used to perform a variety of calculations. This might entail repeatedly scrolling through the worksheet to choose a different cell range. However, if you use named ranges, you can select the cells just once before selecting the proper option for each of the formulas from a drop-down menu.

Simplifying formulas

The majority of Excel formulas demand that you choose or list the specific cells that you want to use in your calculations. However, a named range can be used in formulas in place of cell references. This might make formulas easier to understand and more transparently communicate what they’re calculating.

Cells B2 through B12 of a spreadsheet, for instance, display each team member’s monthly sales revenue figures. The formula =SUM(Sales) can be used by highlighting these cells to create a named range called “Sales.” In contrast, the formula to determine the total sales revenue team would be =SUM(B2:B12) in the absence of a named range.

Using dynamic formulas

Organizational policies may change, requiring you to update your records but allowing the use of dynamic formulas For instance, a sales organization might modify its commission percentage every three months. By using a named range, the company can automatically update the commission earnings each quarter by updating the named range rather than the individual values.

What is a named range?

A named range is a set of cells that have been gathered together and given a specific name. Additionally, a single cell may serve as a named range. The name serves as a point of reference, making it simple to search the worksheet for the range.

Rules for creating named ranges

There are restrictions on what you can call a named range in Excel. Some of these rules include:

How to create a named range in Excel

The steps for making an Excel named range are as follows:

How to edit named ranges in Excel

While working in a worksheet, you might come to the conclusion that you prefer a different organizational structure or don’t like the names of your named ranges. Its possible to modify the titles of the ranges. To change a named range in Excel’s title, take the following actions:

You can also add more entries to cells close to or connected to a named range that has already been established. You can easily expand the named range even though the named ranges don’t update automatically to include the new entries. The steps to edit a named range’s contents are as follows:

Tips for working with named ranges in Excel

Consider these tips to help you use named ranges successfully:

Delete unused named ranges

Consider deleting unused name ranges from your workbook. This may help reduce confusion when working. Follow these steps to delete a named range:

Try using shortcuts

Keyboard shortcuts for Excel are available and may be helpful for maximizing productivity. It’s crucial to keep in mind that shortcuts can differ depending on the operating system and program version. However, some named range keyboard shortcuts include:

Use “Name Manager” carefully

When attempting to use the “Name Manager” feature, be aware of what is happening right now. For instance, updating a cell’s contents is not possible when using this box. Before attempting to edit a named range, click out of any currently selected cells.

Please note that Indeed is not affiliated with any of the businesses mentioned in this article.

FAQ

How do you edit named ranges?

1. Go to the Formulas tab and click on Name Manager.
2. All named ranges in that workbook will be listed in the Name Manager dialog box. The Named Range that you want to edit should be clicked twice.
3. In the Edit Name dialog box, make the changes.
4. Click OK.
5. Close the Name Manager dialog box.

How do I edit named ranges in Excel 2016?

1. Click Name Manager under the Defined Names group on the Formulas tab.
2. Double-click the name you want to edit in the Name Manager dialog box, or click the name you want to change, and then click Edit.
3. Type the new name for the reference in the Name box of the Edit Name dialog box.

Why can’t I change a named range in Excel?

Instead of adding your new column to an empty column to the left, you could try this trick: insert it inside the current data table. It ought to now be in your named range, and the range should automatically expand.

How do you edit a named range in sheets?

1. Open a spreadsheet in Google Sheets.
2. Click Data. Named ranges.
3. Click Edit on the named range you want to modify or remove.
4. Enter a new name or range to modify the range, then click Done.
5. Click Delete range next to the name to remove the named range.
6. On the menu that opens, click Remove.