Tag Archives: range names

Using Range Names in Formulas

Excel, Office, , , , , , , , ,

Have Excel Automatically Create Range Names

A quick way to create range names is to base them on heading cell text (worksheet labels). In the example shown below, the cells representing quarterly sales for all regions will be named based on the labels in columns B through E.

NOTE:  If the labels contains spaces, those are replaced with an underscore. Other invalid characters, such as & and # will be removed, or replaced by an underscore character.

Excel worksheet with column and row labels

Name Cells or Ranges Based on Worksheet Labels:

  • Select the cells that you want to name, including the labels. These can be above, below, left or right of the cells to be named. Here, we are having Excel name the four quarter labels all at once by selecting B4:E8

Selected cells with column labels for naming ranges

  • Click the Formulas tab on the Ribbon, then Create from Selection in the Defined
    Names group.

Create names from selection

  • Excel will automatically place a check mark for the location of the labels; assumes you want to use the first cell as the name (in this case Top Row of the selection).
  • Click OK to add the range names to the Name box and the Name Manager.

Name box with range names

  • Click the dropdown arrow on the Name box to see your new range names. Just click one to highlight the included cells (Quarter1 would highlight B5:B8 as B4 is the label and not included in the range).

    NOTE    : If there are spaces in the labels, they are replaced with an underscore. Quarter 1 would become Quarter_1.

Auto apply range names for the row headers by selecting the text in Column A through the numbers in Row 8 – A5:E8 (don’t include totals):

Cells selected with row labels

Repeat Steps 2, 3 and 4. Note that the check box is now Left row. Click OK.

Display range names from the Name Box:

Name box with alpha range names list

Notice that Excel alphabetizes the list no matter what the order of creation.

Before we put those names into action, we probably will want to know totals for all the eastern regions and all the western ranges at some point, so we’ll create range names for them. Also, we’ll abbreviate the individual regional names so they are much easier to use in our formulas:

Regional Annual Quarterly Sales sheet

Since we are naming the range, we only select the numerical cells:

  • Select B5:E6 (all quarterly sales for the two eastern regions)
  • Click in the Name box and type East
  • Press ENTER

Do the same for both the western regions:

  • Select B7:E8
  • Click in Name box and type West
  • Press ENTER

East and West names are added to the Name box list and the Name Manager.

Now, add a section below your existing data to capture total and average sales for the two regions:

Spreadsheet with Total and Average Sales area

We could call it done and begin using the created names in formulas but the regional names are too long, so for efficiency, let’s change them to just two letters.

Change a Named Range

After you create a named range, you might need to adjust the referred to cell references or, in our case, abbreviate the name to make it easier to use in a formula. Here’s the steps:

  • Click the Formulas tab, and in the Defined Names group, Click Name Manager icon
  • Click on the name that you want to change In the list (in this case Northeast)

Name Manager dialog box

  • Click the Edit… button or double click the name to display Edit dialog box
  • The name field shows the name highlighted
  • Type NE in the box to replace it
  • Click OK

Edit Name dialog box

  1. You can now choose the other three long regional names and replace with NW, SE and SW using Steps 3 through 6
  2. When completed, click the Close button in the Name Manager

Now we’re ready to rock and roll using them in our formulas!

Use Excel Names in Formulas

The real fun begins! We saw in earlier posts how to use range names for navigation or selection – very handy but here’s the real power. We want to find the totals for eastern and western regions for the four quarters. Because we made separate range names for the east and west regions, we can create formulas for the northern or southern regions and/or east and west like this:

  • In B13, type: =sum(ne,se) ENTER   (Range names are not case sensitive so can be typed lower case and Excel will convert)

Formula for Total Sales East Coast

Note: Another advantage of range names is that they are added to the auto display list along with function formula names and you don’t have to remember any cell references!

Next, we’ll simplify even more using the east/west names:

  • In B14, type: =sum(east) ENTER

Presto! There’s the total for the Northeast and Southeast regions.

For the average section, formulas are the same, changing the function:

  • In B17, type: =average(east) ENTER (or you could type: =average(ne,se) ENTER
  • In B18, rinse and repeat with (west) for final results!

Total and Average Sales results cells

TIP: If you need to change the cell references for a range name, open the Name Manager; select the name, and edit the contents of the Refers To box, or highlight the new range on the worksheet with the mouse, and Excel will edit for you. Click the check mark to save the change and close the Name Manager. No need to retype anything.

Your names can be created from references on one worksheet and the formulas used on another because their scope is available across the workbook. I’m pretty smitten with named ranges myself. What do you think? Do you see lots of uses for using this feature? Let me know in the Comments. Thanks!

For more information on Range Names, see:  https://gaylelarson.com/quick-navigation-excel-worksheet/ and https://gaylelarson.com/excel-name-box-for-navigation/

 

Use Excel Name Box for Navigation and Selection

Excel, Office, , , , , ,

In a previous post we covered navigating in Excel using the Name Box by typing a cell reference or a name for a cell or range of cells in the box or using the Go To dialog box (see link below).

compass for navigating your worksheet

When you name cells, they are called Range Names and become much more powerful, or at least more convenient, compared to using cell references.

Name Box for Navigation

The box reflects the current, active cell but does so much more. Although it looks separate, it is really part of the Formula Bar, so if you hide the Formula Bar, it disappears also.

The Excel Name Box

A quick recap on accessing the Name Box. There is no keyboard shortcut for landing there but you can press F5 function key to display the Go To… Dialog box and type in a cell reference in the Reference box; press ENTER or click OK, to go directly to that cell.

The Go To Dialog Box

Name Box for Selection of  Cell Ranges

Selecting huge ranges of data can be frustrating but if you know the cell references for the desired selection, the Name Box is the way to go. Just type in the range (or as close to it as you remember), i.e., a150:r8765, press ENTER, and that entire range is highlighted.

Tip: If you forgot column(s) or row(s) or included too many, just use SHFT and arrow keys to add or remove them. (Beats re-selecting two or three times).

Create Range Names in Name Box

The real power of the Name Box is to use it to create a name or ID for an often-used cell or range of cells, such as Commission or Regions, etc. You can create names that refer to cells, formulas, or a specific value.

The names can then be used for quick navigation or in a formula instead of cell references. You can create as many named ranges as desired and they are accessible from any worksheet in that workbook.

Steps to create range names:

  • Select the cell(s) that you want to name
  • Click in the Name box
  • Type a name for the cells (descriptive but short). There are some rules for range names:
    • Must start with a letter or an underscore
    • No spaces (can use an underscore to represent a space) *
    • Name may contain letters and numbers and periods but nothing that could be mistaken as a cell reference (examples: C, 2, R7C4)
    • Not case sensitive
    • Is ABSOLUTE cell referencing by default
    • Press ENTER after naming you range

 * Examples of naming a range could be: TotalSales or Total_Sales

Range Name example

Note: If there is a formula in that cell, it displays in the Formula Bar and the applied name in the Name Box.

I can now be anywhere in my workbook and return to that range with a click on the dropdown arrow in the Name box:

Name box with cell name

Now I don’t need to know the cell reference or even where it is located and can use that name in a formula, either for part of the formula or with other range names. For instance, if I had a commission rate in F8 and had named that cell, my formula would be: =TotalSales*Commission from any cell in my workbook. How sweet is that!

Name Box Limitations

There are several ways to create named ranges but the Name Box is the quickest. Be aware though that you can only create range names here, you cannot edit or delete them. That must be done from the Formulas tab on the Ribbon. You can also have Excel create range names for you using existing row and column labels to name them (covered later).

See original post for ways to navigate your worksheet:   https://gaylelarson.com/quick-navigation-excel-worksheet/

See the blog on Using Range Names in Formulas for more ways to use this great feature!  https://gaylelarson.com/use-range-names-in-formulas/

Are you liking this handy Name Box? Let me know in the Comments below!

Quick Navigation in Excel Worksheets

Excel, Office, , , , , ,

Go There in a Flash in Excel

You inherited the biggest spreadsheet on the planet and have to scroll and troll to ranges miles away from your current screen! Don’t despair, Excel has several tools to get you there quickly…

The Go To Dialog Box

You can jump to any area of your worksheet with this handy command. Using the Ribbon, Home tab, Editing group, Find & Select drop down arrow, and select Go To… Just type in a cell reference, click OK or press ENTER, and you are there!

OK, that might beat scrolling but not by much, so here’s the corresponding shortcut:

Press CTRL G or F5 function key to auto display the Go To Dialog Box and it is ready for your entry. It also remembers your previous cell location(s).

The Name Box

Want the quick mouse navigation trick? The Name Box is your ticket! Just click in the box and type the cell reference (such as T44456); press ENTER and you are there. (Unfortunately, there is no keyboard shortcut for the actual Name Box but you could use F5 to display the Go To box, type the cell reference and press ENTER).

keyboard angled 2 x 3
Where did I put that?

Range Names

The amazing Name Box is multi-talented. We saw we can highlight large ranges of cells and also jump to a faraway cell location just by typing the cell reference(s) in the Name Box. But what if you are short on sleep and can’t remember the cell reference(s)? No problem. There is a reason it is called the “Name Box”. All you have to do is find that cell once, select it and give it a name that makes sense to you, like Sales or Profit, etc.

Steps:

  • Navigate to desired location
  • Click on desired cell (or range of cells)
  • Click in the Name Box and type a name, i.e., Sales
  • Press ENTER

To return to that location any time, just click the drop-down arrow on the Name Box and select the name you gave it, and you are magically transported there!

Important things to know when naming a cell:

  • Range Names must start with a letter but can contain numbers.
  • You can use more than one word to name a range but it cannot contain spaces.
  • Capitalize the separate words, like TotalSales, or the underscore can be used to represent the space, i.e., Total_Sales.

Range Names are ABSOLUTE by default, meaning the name will always refer to the cell even if the content is moved. And, even more impressive, Range Names can also be used in formulas instead of cell references. That Excel is one smart cookie!