Save content
Have you found this content useful? Use the button above to save it to your profile.
Desk office
istock_kitzcorner

EZ guide to Range Names - advanced features

by
11th Oct 2016
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst recently presented an ExcelZone guide which concentrated on the basic uses of Excel Range Names, but this topic was too big to fit into just one article.

Although Hurst explained how to make Excel formulae easier to create and to understand by allocating names to cells or ranges of cell and how to avoid the pitfalls of using range names, there was still more advanced areas left to explore.

In this extended ExcelZone addition, Hurst takes a looks deeper into this topic, looking at scope and Range Names that do not refer to cells.

Scope

The Define Name option in the Ribbon allows you to choose whether the scope of a name is restricted to a single worksheet or the whole workbook (the default).

Names must be unique within their scope, so you can't use the same name twice if both are set to workbook scope, but you can use the same names on separate worksheets if the scope is restricted to each worksheet:

Source: Simon Hurst
Source: Simon Hurst

The name will not be included in the Name Box dropdown on sheets other than the Data sheet in this case.

Range Names that do not refer to cells

Range Names don't have to refer to a cell or a range of cells. They can refer to a formula. This technique can be useful in some fairly specific circumstances, for example where Excel does not allow you to enter a function directly. A formula that includes the function can be allocated directly to a Range Name which Excel will accept. Examples of this would be chart, Sparkline, and camera picture data sources:

Source: Simon Hurst
Source: Simon Hurst

This approach does have drawbacks. Names that refer to formulae rather than cell ranges do not appear in the Name Box dropdown, so for a user to examine what they do, they would need to access them via the Name Manager.

Name intersections

Where a Range Name is applied to multiple cells in a row or a column, Excel will work out whether there is an intersection between the row or column where your formula is and the range covered by the name. If there is, the formula will return the value at that intersection:

Source: Simon Hurst
Source: Simon Hurst

Print all the names in use in a workbook

As well as listing Range Names for use in a formula, the F3 button screen also includes a Paste List button that allows you to paste a list of the current names in use in your workbook and the cell ranges or formulas to which they refer:

Source: Simon Hurst
Source: Simon Hurst

Beware copying whole sheets between workbooks

If you move or copy a sheet or sheets from a workbook in which Range Names are used to another workbook, the names will be copied to the new workbook and those with workbook scope will continue to refer to the ranges on the original workbook unless you are copying or moving the sheet or sheets to which they refer. Note that the names copied will be those with a scope that includes the moved or copied sheet, so all names with workbook scope will be copied, but a name that has the scope of a different worksheet will not.

Tags:

Replies (9)

Please login or register to join the discussion.

avatar
By adamburns
21st Oct 2016 16:10

You should do a feature on dynamic named ranges (ie. those that will expand to incorporate newly added data). It's one of the more useful features I've found.

Thanks (0)
avatar
By adamburns
21st Oct 2016 16:10

You should do a feature on dynamic named ranges (ie. those that will expand to incorporate newly added data). It's one of the more useful features I've found.

Thanks (0)
Replying to adamburns:
Simon Hurst
By Simon Hurst
24th Oct 2016 09:46

Thanks for the comment.

We have touched on this before and part of the technique involved is covered in the follow up to this article on using Range Names with Sparklines:
https://www.accountingweb.co.uk/tech/excel/use-range-names-to-make-spark...

The OFFSET() function allows a rectangle to be defined by entering the number of rows and columns that the rectangle covers. To make this dynamic, the row and/or column number can be calculated using the COUNTA() function to count the number of non-blank cells in a row or column. As more data is added, COUNTA() returns a higher value which causes additional rows or columns to be included in the OFFSET() rectangle: =OFFSET(Sheet2!$B7,0,0,1,COUNTA(Sheet2!7:7)-1)

While there are circumstances where this approach is still useful, to a considerable extent the need to use it has been superceded by the ability of Excel Table to automatically expand to incorporate new rows and columns: https://www.accountingweb.co.uk/tech/excel/ez-guide-to-excel-tables

Thanks (1)
Replying to shurst:
avatar
By adamburns
24th Oct 2016 09:52

Thanks for clarifying! I look forward to the follow up article.

shurst wrote:

Thanks for the comment.

We have touched on this before and part of the technique involved is covered in the follow up to this article on using Range Names with Sparklines:
https://www.accountingweb.co.uk/tech/excel/use-range-names-to-make-sparkl...

The OFFSET() function allows a rectangle to be defined by entering the number of rows and columns that the rectangle covers. To make this dynamic, the row and/or column number can be calculated using the COUNTA() function to count the number of non-blank cells in a row or column. As more data is added, COUNTA() returns a higher value which causes additional rows or columns to be included in the OFFSET() rectangle: =OFFSET(Sheet2!$B7,0,0,1,COUNTA(Sheet2!7:7)-1)

While there are circumstances where this approach is still useful, to a considerable extent the need to use it has been superceded by the ability of Excel Table to automatically expand to incorporate new rows and columns: https://www.accountingweb.co.uk/tech/excel/ez-guide-to-excel-tables

Thanks (0)
Replying to adamburns:
Simon Hurst
By Simon Hurst
24th Oct 2016 10:04

Thanks - the follow up is already available, I think it was added to the site on Friday.

Thanks (0)
avatar
By Fastlane
25th Oct 2016 15:58

1. "Range Names don't have to refer to a cell or a range of cells. They can refer to a formula." are interesting statements, for it was once explained to me that Defined Names are nothing more than formulas with a name assigned as a substitute to cell references! Names that refer to a cell range are just formula referring to an array (which could even be a range in a workbook that is different to where the Name itself is hosted).

2. Names can also be used to hold constants, such as a VAT or income tax rate (e.g. "Tax" RefersTo =20%), text strings (e.g. "Coy" RefersTo = "My Company LLB", or a multiple used for rounding values (e.g. "Million" RefersTo = 10^6)

Thanks (0)
Replying to Fastlane:
Simon Hurst
By Simon Hurst
25th Oct 2016 16:49

Thanks for those additions. Unless the intention is to make it difficult to change the 'constants' it would usually be better to enter the constant value in a cell, and use the Range Name to refer to the cell. This makes is easier for the user to discover what the Range Name refers to and, of course, change the value if necessary.

Thanks (0)
avatar
By Ian Narbeth
02nd Nov 2016 11:26

I commonly use VATRate = 20% as a Defined Name. If the VAT rate were to change again I will only need to change this and not to go through the whole sheet to change hard-coded references. Also putting it as a Defined Name rather than in a cell means that inexperienced users cannot delete or amend it.

Thanks (1)
Replying to Ian Narbeth:
avatar
By adamburns
03rd Nov 2016 09:55

That's actually a really neat idea. I'm stealing that.

Thanks (0)