 |
|
|
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.
There are many ways that we can use to name ranges in Excel. By far the quickest and easiest 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.
The other methods can be found by going to Insert>Name and selecting one of the options from the sub menu leading off from Name.
Let's look at each one of these in turn.
Define
When you select the Define option (or push Ctrl+F3) you will see the Define name 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 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 Dyna Ranges 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 to 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 Cells A1 type the heading: Names. In B1 type the heading: Pay Rate
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)
Select cell D2 and go to Insert>Name>Define (Ctrl+F3)
Type: HrlyRate in the Names in workbook: box.
Type: =VLOOKUP(D1,PayRate,2,FALSE) in the Refers to: box
Click Add then 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 the Define name dialog. 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.
Paste
This dialog box (F3) can be used to either select the name you want from a list of all names within the Workbook or Paste a list of names and their associated references.
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 HrlyRate from the list and click OK. Excel will insert the name for you.
If you use this dialog and select Paste list Excel 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
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
This dialog box 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.
If you do not understand this it may become clear once you are familiar with Labels. See below. There is also a explanation with screen shots here http://193.144.38.110/manuais/excel97/ch06/ch06.htm under Applying Names to Formulas.
Label
If you have used numbers in your row and column headings such as years eg; 1995 Excel will use the numbers themselves instead of understanding you want to use a range named 1995.
To overcome this, you can create Labels. Let’s say that you have data on a worksheet as follows:
In F7 type: Vic
In G7 type: NSW
In H7 type: Tas
In J7 type: Total
In E9 type: 1995
In E10 type: 1996
In E11 type: 1997
In E12 type: 1998
In E13 type: 1999
Now fill your table with fictional numbers in the range F9:H13. Then:
Select cells E7:H13.
Go to Insert>Name>Label.
Click on Column labels in Add label range.
Click on Add to add the Labels.
Click on the Collapse Dialog button to shrink the dialog box. Select cells E9:E13
Click on Row labels in the expanded Label Ranges dialog box.
Click OK.
Now in cell J9 type: =sum(1995) and press Enter. Click in cell J9 again and you will see how the formula has automatically updated to include a Label for ‘1995’. Click in cell J10 and type: =sum(1996) and press Enter. Again you will see that the calculation is correct. You could also use your fill handle on cell J9 and drag down to cell J13 and Excel will automatically change the Label to be the appropriate year.
*If you go to Tools>Options/Calculations you will see an option to: Accept labels in formulas. If this option is not checked the above example would not work at all!*
So as we can see we can use Excels Filters (Advanced and Auto) 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 about Filtered data is that Excel has a 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.