I Always Name Ranges in Excel, and You Should Too

Improve Excel efficiency: Make good use of named regions

By default, Microsoft Excel cells are named after column-row coordinates, such as A1 or B2. However, you can assign more specific names to a cell or cell range, improving navigation, making formulas clearer, and ultimately saving time.

Why always name regions in Excel?

You may be familiar with bookmarks in Microsoft Word, which are invisible signposts for the specified locations in your document, and you can jump to where you want at any time. Microsoft Excel has a bit of a unimaginative alternative to this time-saving tool called "names" and is accessible via the name box in the upper left corner of the workbook.

Related content##### Excel forms do not support bookmarks (so please use this trick)

Excel has its own bookmarking feature.

The article assumes that you have a huge worksheet with a lot of data rows and columns, and you want to jump to a certain cell without scrolling forever. The fastest way is to type a cell reference in the name box and press Enter.

Similarly, if there are other active tabs in your workbook, you can jump to any cell in any worksheet by typing the tab name, followed by an exclamation mark, and then a cell reference. For example, type:

 <code>Sheet2!H11</code>

Then pressing Enter will take you to cell H11 in Sheet 2.

While this is all good, it's not very useful if you can't remember which cells contain the information you want to jump to. This is why you should change the name of a cell or region you access frequently or use in formulas.

In this example, I named a cell range "Monthly_Totals" and I've been looking at it. Since I have named the area, no matter which sheet I am currently using, I can jump to it by clicking the down arrow in the name box.

More importantly, I can use this named area in the formula. For example, type:

 <code>=SUM(Monthly_Totals)</code>

All values ​​in this area are added and the total is provided.

12 ways to fix corrupt Excel formulas Excel formulas cause problems? Stop looking for it.

Another useful example of article naming regions in formulas is when they form part of logical tests in IF functions. In this example, by clicking on the cell and viewing in the formula bar, you can immediately see if the criteria for reaching the threshold involve total revenue. On the other hand, using cell references here will make this less clear:

 <code>=IF(Total_Income>10000,"Threshold met","Threshold not met")</code>

Finally, named areas helps create dynamic drop-down lists through Excel's Data Validation Tool. In this example, I have renamed the entire column A to "Employee_Names". Now when I click Data Validation in the Ribbon Data tab to add a name dropdown to the E1 cell, I can specify the source of the list as the column named for this.

This means that any name I add to column A will be included in the drop-down list.

How to name a region in Excel?

It's easy to name a cell or a cell range in Excel. Simply select the cell or range you want to name and replace the cell reference with the name you want in the Name box. Then, press Enter.

When you use this method to manually name the region, there are some rules that you have to follow to get Excel to accept your changes:

• The first character must be a letter, an underscore (_) or a backslash (\).
• The rest of the name cannot contain any special characters. In other words, you can only use letters, numbers, periods, and underscores.
• If the name you assigned contains multiple words, use an underscore or period (.) as the word separator. You cannot use spaces.
• Named areas cannot be the same as cell references, such as A1 or B$2.
• You cannot name the region "R", "r", "C", or "c" because these references are reserved for other background uses in Excel.
• Each region name must be different from other names in the workbook, and Excel is case-insensitive.
• A name can contain up to 255 characters, but this should not be a problem because the name you assign should be clear and concise.

You can also force Excel to name the region based on the data already in the worksheet. Select the area you want to name, and in the Formula tab of the ribbon, click Create from Selection. Then, check the relevant options in the dialog box that appears. In this example, selecting "Top Row" will cause the area to be named "Employees" because the column title is included in the selection.

How to change named regions in Excel?

Whether you want to change the name of the region or name the cells contained in the region, the process is quick and easy.

In the Formula tab of the ribbon, click Name Manager.

Then, double-click the named area you want to modify, or click it and select Edit.

Next, in the Edit Name dialog box, you can change the region name in the Name field, or adjust the reference region in the Reference Location field.

When you are finished, click OK and close the Name Manager dialog box.

If you include previous names or areas in formulas, data verification conditions, or other references in your workbook, don't worry, these will be automatically updated based on the changes you just made.

Things you should know about naming areas in Excel

Before continuing to name certain areas in your workbook, note the following:

• Use a descriptive but short name. For example, "Total_expenditure_for_the_year_2020" will only make the formula too long and cause confusion. Similarly, "TotExp20" may not provide enough information to anyone who has never seen the worksheet before. On the contrary, names like "Expenditure_2020" are more appropriate.
• No need to name each region. Sometimes, unnamed areas (direct cell references) may be more useful if sharing a worksheet with others because they can see the exact reference location. In other words, the region should be named only if doing so adds value to the spreadsheet.
• You might as well consider adding the "Rng_" prefix to any named area to distinguish it from other named items such as tables or charts. Taking this step also makes the areas in the formula easier to identify – when you type Rng, all named areas will be displayed in the tooltip list, and you can simply select the relevant areas.
• Another way to name regions in Excel is to format the data into an Excel table and then rename the table.

Related content##### If you haven't renamed a table in Excel, today is the beginning

What's in the name? Well, a lot.

article ---

Named regions are just one of many ways to improve Excel's productivity. For example, you can also freeze the pane to avoid overscrolling, generate predictions using the Data Prediction tool, and adjust the Quick Access Toolbar to personalize your Excel experience.

The above is the detailed content of I Always Name Ranges in Excel, and You Should Too. For more information, please follow other related articles on the PHP Chinese website!