Level 2 - Lesson 25 - Named Ranges
Category: [General,Excel] Demo Available
Named Ranges
In Excel it is always good practice to name ranges, formulas and constants with meaningful names. Before we do so though, it may pay to have a quick refresher on some rules that must be adhered to when naming any of the above.
See below from the Excel Help
Guidelines for naming cells, formulas, and constants in Microsoft Excel
What characters are allowed? The first character of a name must be a letter or an underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters.
Can names be cell references? Names cannot be the same as a cell reference, such as Z$100 or R1C1.
Can more than one word be used? Yes, but spaces are not allowed. Underscore characters and periods may be used as word separators — for example, Sales_Tax or First.Quarter.
How many characters? A name can contain up to 255 characters.
Note If a name defined for a range contains more than 253 characters, you cannot select it from the Name box.
Are names case sensitive? Names can contain uppercase and lowercase letters. Microsoft Excel does not distinguish between uppercase and lowercase characters in names. For example, if you have created the name Sales and then create another name called SALES in the same workbook, the second name will replace the first one.
end of Help Text
Let's look at naming ranges first.
Naming Ranges
By far the quickest and easiest way in most cases is to simply highlight the range of cells and type the name in the NameBox to the left of the formula bar. This, however, does limit us to naming ranges only, ie; you cannot use this method to name constants or formulas.
There are two other methods can be found by going to Defined Names options on the Formula tab.
Let's look at each one of these in turn.
Name Manager
When you select the Name Manager option on the Formulas tab (or push Ctrl+F3) you will see the Name Manager dialog box. It is in this box that you will see a list of all Names in the Workbook. By selecting any one of these Names you will see the Worksheet and cell address they refer to in the Refer to box at the bottom of the Define name dialog box. If you place your mouse insertion point anywhere within the Refers To box while you have a name highlighted, you will be taken to that location. This is also where you would go to delete any redundant Names. You would do this by simply selecting the Name and clicking Delete.
The Define Name dialog box is arguably the most useful for naming as it is here you can write formulas for your ranges and the only limit is ones own imagination. See Using Names in Excel. for some examples. It is also here that we can name Formulas so we can use them over and over again. These might be as simple as multiplying two constants or a deeply nested Formula. For example suppose you have a business with "x" number of employees and 5 different levels of pay rates. You could do the following:
In Cell A1 type the heading: Names. In B1 type the heading: Pay Rat
In Cells A2:A10 type any names eg; Bill L, John W etc. In B2:B10 type any pay rates eg; $14.25, $12.55 etc
Name the range A1:B10 PayRates, ie; select A1:B10 and type PayRates in the Name box (left of Formula bar)
Click in cell D2 and then select Define Name from the Formulas tab
Type: HrlyRate in the Name: box.
Type: =VLOOKUP(D1,PayRate,2,FALSE) in the Refers to: box
Click OK.
Now type any name from the Names column into any cell.
In the cell directly below the name put the formula: =40*HrlyRate.
You will get the hourly rate for the person named in the cell directly above multiplied by 40. Whenever and wherever you use this formula it will always use the correct hourly rate for the name in the cell directly above.
This is just a simple example of the sort of things you can do with named ranges. As you get better at writing formulas and using Excel Functions you can start to come up with very imaginative ways of doing this type of thing.
Use in Formula
This option allows you to select the name you want from a list of all names within the Workbook.
Lets say you were using the =40*HrlyRate formula in a cell and were a bit unsure of the name you had applied. You would type: =40 and then select Use in Formula under Defined Names options on the Formulas tab, then HrlyRate from the list and click OK. Excel will insert the name for you.
There is also a Paste Names option here. If you select this Paste Names and then Paste List, you will create a list of all the names in the Workbook in one column and all their associated references in the column to the right. Just make sure you don't have any data that will be replaced. If you do just use the Undo command (Ctrl+Z).
Create from Selection
This dialog (Ctrl+Shift+F3) will allow you to quickly use column or row headings as range names. Let's say you had a table set up on your Worksheet and you wanted to use the column headings as names for all the data below the heading. You would select the entire table (headings included) push Ctrl+Shift+F3, check the Top row CheckBox only and click OK. Excel will apply the names of each of your column headings and refer them to the data below the heading.
Lets say you have the names of 1000 employees in cells A2:A1002 and in cells B1:B10 you have the names of different locations that your employees need to travel to on a daily bases. Each time an employee travels to a particular location you put a tally in the intersecting cell of the Location and Employee name.
In other words, if Bill has been to New York five times this year, there would be a number 5 in the cell that intersects the column heading New York and the row heading Bill. Now Let's say that the pay office needs to know how many times Bill (or any employee) has been to New York (or any location). You could select the range A1:K1002 (your entire table) push Crtl+Shift+F3 and select the options Top row and Left column and click OK. Then in any cell you could type the formula: =Bill NewYork and Excel will return the value of the cell that intersects Bill and New York. You MUST push the Space bar after Bill. In other words you must have a space between Bill and NewYork.
Apply Names
This option is found by selecting the drop down to the right of Define Names under the Formulas tab and is used to replace cell references within formulas to their range names. Let's say you have been using a simple SUM function like: =SUM(A1:A100) throughout a Workbook. You then decide to name the range A1:A100 Totals. When you do so all your =SUM(A1:A100) formulas will still refer to the range address rather than its new name (Totals).
This is where you would use the Apply dialog box. You would simply activate it while you have a single cell selected and select the name: Totals and click OK. Excel will go through the Workbook and replace all formulas that are using the range A1:A100 with the name Totals. If you did the same with more than one cell selected, Excel will only replace references in the selected range. To apply more than one name hold down the shift key and select the names.
When we applied the name Totals, Excel by default will use absolute references ie; $A$1:$A$100. If we had a mix of absolute and relative references in our formulas ie; A1:A100 and $A$1:$A$100 we may only wish to replace the ones using $A$1:$A$100 (absolute references). In this case we would uncheck the Ignore Relative/Absolute Checkbox then click OK and Excel will only replace the reference $A$1:$A$100 with the name Totals. So basically clearing this check box will make Excel replace absolute references with absolute names, relative references with relative names, and mixed references with mixed names.
Leaving the Use row and column names box checked will force Excel to use the names of row and column ranges that refer to the cells if the exact names for the cells can't be found.
If you click the Options>>> button you will see
Omit column name if same column:
Replaces the reference with the row-oriented name without including the column name. Clear the check box if you want to include the column name.
Omit row name if same row:
Replaces the reference with the column-oriented name without including the row name. Clear the check box if you want to include the row name.
Name order:
Row column Column row
When a cell reference is replaced by both a row-oriented and a column-oriented range name, click Row column. Click Column row if you want the column name to appear first.
Summary
So as we can see we can use Excels Filters (Advanced and regular) to display virtually any criteria we want, so long as we have set up our table or list is a sensible manner. One aspect we have not mentioned is that Filtered data in Excel has a special Function that is designed to work with a Filtered range. It is called SUBTOTAL and can be used to return the result of rows that are visible as the result of Filtering. There is a good description of this easy to use Function in the Excel help under: SUBTOTAL.
We can also use Names in our Workbooks that can not only make our formulas easy to read but also very easy to edit en masse. We can also use Names to create a formula that we may need to use often or is one that refers a table of data. The only real limit to this is our own imagination and determination.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets and Index to Excel VBA Level 1 Free Lessons and
Index to Excel Level 2 Lessons and Index to COVID-19 Charting examples