Range Names
An excellent feature in Excel is the ability to give a meaningful text name to various parts of our workbooks. Some of this we do almost without noticing we doing it – e.g. assigning a file name when we save a file, renaming a tab from Sheet 3 to ‘Sales Summary, etc. Likewise, we can name a single cell or a range of cells to convert a reference from (say) $B$5:SX$20 to ‘Sales for the month’. To a human brain the latter is much more memorable than the formula.
There’s a little more effort required to name a range as opposed to a sheet or workbook (but not much more). The steps are:
- Highlight the cells that you wish to name
- from the Formula ribbon in the names section (Highlighted in red below) select ‘define name’
This brings up the following dialogue box
Type the text that you wish to name your cells in the box highlighted in blue
NB The scope box allows you to restrict the name to a particular sheet within the workbook or use it across the whole workbook
Check the cell range in the box highlighted in Red. If its correct click the ‘OK’ button. If its not correct amend the range and then click OK
NB the Comment box allows the user to make some notes on the use of this name
Some useful things you can do with range names:
- use them in formulas e.g. if you have a list of products and you want to know how many times Beans occurs then instead of COUNTIF(SZS20:$Z$157,”Beans”) you could type COUNTIF(products, “Beans”) after having defined the name ‘products’ as relating to the cells $ZS20:$Z$157. A meaningful name can make the formula more understandable to a another person looking at your spreadsheet.
- Use them is dialogue boxes such as data validation e.g when applying a list validation in the range box you could type ‘=products’ instead
- Use them to save updating many other formulas. e.g. if I am a business trading in two currencies enter the exchange rate in one cell and name it ‘exchange’. Then where ever I need to convert one currency into the other I can multiply by the exchange rate using the name. If the exchange rate changes then there is only on cell to update
- in VBA code
Range names can be edited at a later date: Simply select the Name Manager from the Formula tool bar (see screenshot above), select the name you wish to edit and change the details.
Dynamic range names (advanced)
Each time the size of your data changes you could go and change your range names using the name manager. Whilst this is a perfectly acceptable way to change a name it will add time to your updates and if you forget to change the name then you might be producing erroneous results. Fortunately Excel comes to the rescue with is ability to make range names change as the size of your data changes. In Excel speak, flexible range names are called Dynamic ranges.
To make a range name dynamic in range entry box (highlighted in red in the example on normal ranges above) you replace the standard range (e.g. =Sheet2!$G$5:$G$16) replace this with an offset formula such as =OFFSET(Sheet2!$G$5,,,COUNTA(Sheet2!$G$5:$G$50))
Dynamic ranges are particularly useful where you are downloading data on a regular basis (e.g. sales orders) and the number of entries in the list will vary on each download occasion
Dynamic ranges can be used in the same way as names referring to a fixed range of cells.
Where you have a very long list for your range name you may wish to use my Double Dynamic Range Name solution
Excel Monkey 