ExperTrain 2019 Naming Ranges in Excel User Guide

ExperTrain 2019 Naming Ranges in Excel User Guide

Introduction

Named ranges are useful for easily identifying cells or groups of cells on a worksheet.
Instead of referring to a cell or a range as B1 or C16:E25, you can give it a name such as Data Range or Expenses List.
In some cases, a named range is the easiest way of identifying data to import (eg. in Microsoft Access).

Creating a Named Range

Method 1 – By example

• a) Highlight the range that you wish to name.
• b) Click in the Name Box to the left of the Formula Bar.
• c) Type a name for the selected cell or range.
• d) Press Enter.

Method 2 – By dialog box

• a) Highlight the range that you wish to name.

• b) Click either:
  i. Name Manager in the Defined Names group of the Home tab, and then the New… button; or
  ii. Define Name in the Defined Names group of the Home tab. In both cases, the New Name dialog box opens.

• c) Enter a name for the range in the Name: box.

• d) Select a Scope: for the named range (see section below on Scope of Named Ranges for clarification).

• e) Enter a Comment, if required.

• f) The Refers to: box should already contain the required cell or range.
  NB. If you edit this range manually, beware of deleting the dollar signs as the named range may not then work as expected (see section below on ‘Absolute’ and ‘Relative’ Named Ranges for clarification).

• g) Click OK and if necessary, close the Name Manager.

TIP: Named ranges must start with a letter and should not contain spaces or (most) punctuation marks.
Tip: Avoid using all uppercase letters for a named range as this can make it difficult to distinguish between function names and named ranges when inspecting or troubleshooting a formula.
Jumping to a Named Range You can use the Name Box to quickly go to a cell or range.
a) Click the down-arrow at the right of the Name Box.
b) Select the named range that you wish to modify. c) Do one of the following:

Tip: Select multiple named ranges by selecting one from the Name Box, and then clicking the Name Box down arrow and while pressing the CTRL key, selecting another.

Editing a Named Range

• a) Click Name Manager in the Defined Names group of the Formulas tab.

• b) Select the named range that you wish to modify.

• c) Do one of the following:
  i. Click in the Refers to: box at the bottom of the dialog box.
  ii. Edit the reference manually or click and drag over the required cells on the worksheet.
  NB. If you edit this range manually, beware of deleting the dollar signs as the named range may not then work as expected (see section below on ‘Absolute’ and ‘Relative’ Named Ranges for clarification).
  iii. Click ✔ or press Enter
  Alternatively…
  i. Click the Edit… button .
  ii. Make changes as necessary.
  iii. Click OK.
  NB. This method also allows you to rename the range.

• d) Click Close when finished.

‘Absolute’ and ‘Relative’ Named Ranges

Absolute named ranges

Put simply, an absolute named range is one that has dollar signs in its reference, eg. $A$1:$E$10. This means that wherever you use the named range (eg. in a formula or for linking to), it will always refer to that specific cell or cells. Absolute named ranges are created by default when you select the cell(s) for the named range using the mouse.

Relative named ranges

A relative named range is one that points to a cell or range that is ‘away from’ the selected cell in which you are using it. A relative named range will not have dollar signs in-front of all the column letter or row numbers in its reference. In fact, they may no have any dollar signs at all. In the picture below, the relative named range – Cell To Left has been used in the formula in cell C2. Because Cell To Left has been created to always refer to the cell to the left, the formula will work when copied down the column. If Cell To Left had been an absolute named range, the formula would have given the same result each time.

Creating a relative named range

The instructions below are for creating a relative named range for a cell that is to the left of a selected cell. The principle, however, is the same for other relative named ranges that you want to create. For example, if you wanted to create a relative named range consisting of four cells immediately above a selected cell, you would have to select at least cell A5. Anything less (eg. cells A1 to A4) would not have four Excel Advanced Course cells above to give as an example.
a) Select any cell on the worksheet that has another cell to its left (ie. any cell that is not in column A).
b) Click either:

c) Enter a name for the range in the Name: box.
d) Select a Scope: for the named range (see section be-low on Scope of Named Ranges for clarification).
e) Enter a Comment, if required.
f) In the Refers to: box, enter the reference for the cell that is to the left of the selected cell. For example, if you have selected cell B1, then the cell to enter in the Refers to: box is A1. MAKE SURE there are no dollar signs on the column letters or row numbers. g) Click OK and close the Name Manager if necessary.

Deleting a Named Range

a) Click Name Manager in the Defined Names group of the Formulas tab.
b) Select the named range that you wish to delete.
c) Click the Delete button .
d) Click OK.
e) Click Close.

TIP: Any formulas using the named range will now return a #Name? error.

Scope of Named Ranges

The method described above for creating a named range makes the name available (scope) for use on any sheet of the workbook. Its scope, therefore, is for the entire workbook.
In some cases, it may be desirable to scope a named range for one sheet only.

NB. It is not possible to change the scope of an existing named range. You have to recreate it from new as described above and delete the old one.

Using a Named Range in a Formula

Named ranges can be used in formulas (eg. =SUM(DataRange)) where DataRange refers to a range of cells (eg. B2:E25).
You can enter a named range into a formula by:
i. Typing it manually;
ii. Selecting it from the ‘Formula Autocomplete List’;
iii. Selecting it from the ‘Paste Name’ dialog box.
Enter a named range into a formula as follows:

Method 1
a) Select the cell where you want to enter the formula.
b) Type an equal symbol ( = ) and start typing the formula.
c) At the point at which you want to enter the named range, start typing the first few characters of its name.
The ‘Formula Autocomplete List’ will appear with a list of functions AND named ranges that match your typing.

d) Continue typing the name or,
i. double-click it on the list, or
ii. highlight the named range on the list and press
Tab on the keyboard.
e) Complete the formula and enter it.

Method 2 (useful if you cannot remember the spelling of your named ranges)
a) Select the cell where you want to enter the formula.
b) Type an equal symbol ( = ) and start typing the formula.
c) At the point at which you want to enter the named range, press F3 on the keyboard.
The Paste Name dialog box appears.
d) Select the required named range in the Paste name list.
e) Click OK.
f) Complete the formula and enter it.
Method 3 (also useful if you cannot remember the spelling of your named ranges)
a) Select the cell where you want to enter the formula.
b) Type an equal symbol ( = ) and start typing the formula.
c) At the point at which you want to enter the named range, click in the Defined Names group of the Formulas tab. A list of named ranges appears.
d) Select the required named range in the list.
e) Complete the formula and enter it.

NB. When using a named range in a formula, it acts as an absolute reference. As a result, however, it may not always have the desired effect when copying a formula containing one. See Reference Guide: Excel 2019 Introduction to Calculations.

Using a Named Range in a Hyperlink

Because a named range refers to a cell or range, it can be used as a reference in a hyperlink.

Hyperlink to a named range in the same workbook

Hyperlink to a named range in a different workbook

Create a List of all Named Ranges

a) Select a cell below which and to the right of there are as many blank cells as there are named ranges in the workbook.

Apply Named Ranges to Existing Formulas

If you have formulas in a workbook that contain cells or ranges that are now named ranges, you can automatically replace the cell references with the names.
For example, the SUM function below contains the range A2:A10 as its argument. Range A2:A10 has now been named, Data Range. You can automatically replace A2:A10 in the formula with Data Range.

Creating Named Ranges from Headings in a Table

If you have a data table on a worksheet, you can automatically create headings based on the column and/or row headings of the table.

c) Click OK to confirm that you want to use the values in the top row and the left column as the names for the relevant data.
d) Click the Name Box down arrow to confirm that the named ranges have been created.

Cases where Excel automatically creates a named range

There are some data tools in Excel that when used, automatically create named ranges.
So, in case you find named ranges seemingly appearing from nowhere, below is a list of Excel tools and the associated named ranges that they create.
If these named ranges interfere with other things you are trying to do in a workbook.

Dynamic Named Ranges

An issue affecting named ranges that refer to data tables is that if you add extra columns at the right, or new rows at the bottom of the data table, the named range will not automatically extend itself to include them.
One way to avoid this is to create a ‘dynamic’ named range one that will automatically resize itself.
This is achieved by using the OFFSET function to calculate the identify where the table starts, and then calculate its height and width. Hence, because the named range is based on a formula, it will recalculate its size every time a change is made.

=OFFSET(reference, rows, cols, height, width)
i. reference is the cell at the top left corner of the data table.
ii. rows and cols are how many cells away from reference does the data table start. This will almost always be 0 and 0 meaning the starting point for the table is the same as the top left corner.
iii. height is how many cells high you want the named range to be. This is done by using a COUNTA function to count the number of cells vertically.
iv. width is how many cells wide you want the named range to be. This is done by using a COUNTA function to count the number of cells horizontally.

The example below shows how the OFFSET function could be used to create a dynamic named range for any sized data table that has its top left corner in cell A1 of sheet1.

NB. Named ranges based on formulas will not appear in the Name Box. They can however be used by typing their name directly into a formula, selecting from the functions list as you type, selecting from the Use in Formula button (Formulas tab > Defined Names group) or by pressing F3.

Read User Manual Online (PDF format)

Read User Manual Online (PDF format)  >>

Download This Manual (PDF format)

Download this manual  >>