[ad_1]
Using Named Ranges in Your Microsoft Excel Spreadsheets
If you try to get someone’s attention in a crowd by shouting “Hey you!” there is a very good chance that some confusion may be created. Several people may look to see if you are calling them. To avoid this you could call the person by name. Far fewer people are likely to think you are shouting at them.
Using a name can also be useful in Excel. Rather than simply referring to a cell or range of cells by their cell referencing (A1) you can give them a more specific name such as ‘tax.’
Why would you want to do that? Good question!
1. It can be more efficient if you are creating formulas
If you are working with the same data for a variety of functions, rather than selecting the range of cells each time you can use its name. If you begin to type a function name you will see an ‘AutoComplete’ list appear to help you. The function names have ‘fx’ in front of them. If you have created any named ranges, they will also appear in this list. Rather than ‘fx’ they will have an icon that looks like a luggage tag in front. This tag represents a named range. Just like the function names, if you double click on the name of the range, or highlight it in the list with your arrow keys and then press TAB it will be added to your calculation.
2. You can avoid mistakes in your data range
If you have created a name for your range, you can avoid accidentally selecting the wrong cells – assuming that you have selected the correct ones when you created the named range.
3. You can keep your data on one sheet and your calculations on another
All named ranges are available for every worksheet in that workbook. As you begin to type the name of the range, it will appear in the ‘AutoComplete’ list even if the range is on another sheet in the workbook.
4. It gives more meaning to your function
Let’s say you are creating calculations that deal with a tax rate that is in cell B4. If you use cell referencing you will need to look at the sheet to see what the cells contain to make sense of the calculation. Imagine creating named ranges. Instead of seeing ‘A1:A56*B4’ you could see ‘sold_inventory * tax’. That has so much more meaning for the reviewer.
5. A named cell or range has absolute cell referencing
If you are using ‘AutoFill’ to copy your formulas you won’t have to worry about making cells absolute. A named range refers to specific cells so they are automatically absolute.
How to Create a Named Range:
1. Highlight the cell(s) that you want to name
2. Click in the Name box under the ribbon on the left of your screen
3. Type the name you want to give the cell or range of cells
4. Press Enter
Naming Conventions:
* The first character must be a letter, an underscore, or a backslash
* You cannot use cell references like A1
* You cannot include spaces in the name
* The name can be up to 255 characters
The Name Manager:
If you need to edit or delete the named range, go to the Defined Names group in the Formulas ribbon and click on Name Manager. From here you can add, modify or delete a named range. You can also create named ranges from this dialog box.
If you haven’t been using named ranges it’s time to give them a try. These five reasons make using named ranges a must if you want to work smart rather than working hard.
