Excel Named Ranges: Absolute and Relative References | Lecture notes MS Microsoft Excel skills

 
Excel 2019 Advanced Course  Page 1  Last updated 09/2019 
 
Naming Ranges in Excel 2019 
Topics Covered in this Guide 
Introduction ............................................................................. 1 
Creating a Named Range ......................................................... 1 
Jumping to a Named Range ..................................................... 1 
Editing a Named Range............................................................ 1 
'Absolute' and 'Relative' Named Ranges ................................. 2 
Absolute named ranges ....................................................... 2 
Relative named ranges ........................................................ 2 
Creating a relative named range ......................................... 2 
Deleting a Named Range ......................................................... 2 
Scope of Named Ranges .......................................................... 2 
Using a Named Range in a Formula ......................................... 2 
Using a Named Range in a Hyperlink ....................................... 3 
Hyperlink to a named range in the same workbook ........... 3 
Hyperlink to a named range in a different workbook ......... 3 
Create a List of all Named Ranges ........................................... 4 
Apply Named Ranges to Existing Formulas ............................. 4 
Creating Named Ranges from Headings in a Table ................. 4 
Cases where Excel automatically creates a named range ....... 4 
Dynamic Named Ranges .......................................................... 4 
 
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 DataRange or ExpensesList.  
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 be-
low 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 'Rel-
ative' 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 from the list. 
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 dia-
log 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 de-
leting the dollar signs as the named range may not 
then work as expected (see section below on 'Ab-
solute' and 'Relative' Named Ranges for clarifica-
tion). 
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. 

Partial preview of the text

Download Excel Named Ranges: Absolute and Relative References and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity!

Topics Covered in this Guide Introduction ............................................................................. 1 Creating a Named Range ......................................................... 1 Jumping to a Named Range ..................................................... 1 Editing a Named Range............................................................ 1 'Absolute' and 'Relative' Named Ranges ................................. 2 Absolute named ranges ....................................................... 2 Relative named ranges ........................................................ 2 Creating a relative named range ......................................... 2 Deleting a Named Range ......................................................... 2 Scope of Named Ranges .......................................................... 2 Using a Named Range in a Formula ......................................... 2 Using a Named Range in a Hyperlink ....................................... 3 Hyperlink to a named range in the same workbook ........... 3 Hyperlink to a named range in a different workbook ......... 3 Create a List of all Named Ranges ........................................... 4 Apply Named Ranges to Existing Formulas ............................. 4 Creating Named Ranges from Headings in a Table ................. 4 Cases where Excel automatically creates a named range ....... 4 Dynamic Named Ranges .......................................................... 4 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 DataRange or ExpensesList. 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 be- low 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 'Rel- ative' 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 from the list. 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 dia- log 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 de- leting the dollar signs as the named range may not then work as expected (see section below on 'Ab- solute' and 'Relative' Named Ranges for clarifica- tion). 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 - CellToLeft - has been used in the formula in cell C2. Because CellToLeft has been created to always refer to the cell to the left, the formula will work when copied down the column. If CellToLeft 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 immedi- ately above a selected cell, you would have to select at least cell A5. Anything less (eg. cells A1 to A4) would not have four 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: i. Name Manager in the Defined Names group of the Home tab, and then the New... but- ton; 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 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. f) Select the cell or range you want to name. g) Click Name Manager in the Defined Names group of the Formulas tab. h) Click New.... i) Type a name for the named range in the Name: box. j) Click the down arrow at the right of the Scope: box. k) Select the sheet that you want to make the named range visible for. l) Click in the Refers to: box and type or (preferably) click and drag the required cells on the workbook. m) Click OK. n) Close the Name Manager dialog box. 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 ';

e) Select the named range that you want to hyperlink to under Defined Names. f) Click OK. g) Click OK. NB. The 'Address' for the hyperlink will consist of the file- name followed by the named range separated by a hash ( # ). 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. b) Press F3 and click the Paste List button The names of the ranges are listed vertically in the col- umn with the selected cell, and the cells that they re- fer to are listed in the column to the right. 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, DataRange. You can automatically replace A2:A10 in the for- mula with DataRange. change to 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. In the example below... ...you could automatically create four named ranges as fol- lows: Name Refers to: Jan B2:B Feb C2:C Sales B2:C Expenses B3:C a) Select the range containing the heading and the data. In the above example, you would select A1 to C3. b) Click Create from Selection in the Defined Names group of the Formulas tab. 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, automati- cally 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 try- ing to do in a workbook. Tool Named Range(s) Created 'Tables' feature (Home tab > Styles group > For- mat as Table) Table followed by a number. Numbers are sequential so the first 'Table' created in the work- book will be named Table1 , the second will be named Table and so forth. Advanced Filter (Data tab > Sort & Filter group

Advanced Filter Criteria Extract Set Print Area (Page Lay- out tab > Page Setup group > Print Area Print_Area 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 bot- tom 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 for- mula, 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 refer- ence does the data table start. This will almost al- ways 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 F. End of Document

Excel Named Ranges: Absolute and Relative References, Lecture notes of MS Microsoft Excel skills

Related documents

Partial preview of the text

Download Excel Named Ranges: Absolute and Relative References and more Lecture notes MS Microsoft Excel skills in PDF only on Docsity!