 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only $145.00. $59.95 Instant Buy/Download,30 Day Money Back Guarantee & Free Excel Help for LIFE!
Got any Excel Questions? Free Excel Help
By default, Excel uses the A1 style reference for cell addresses. However, we can override this default and apply meaningful names to our cell(s). For those of you who are not familiar with naming ranges within Excel, it is a very simple process that allows us to change the way we read formulas. Instead of having a formula read =$E$1*$E$2, where $E$1 may store a specific Tax Rate, and $E$2 an employees wage, we can use named ranges to make our formula look like =TaxRate*Wage. Perhaps the biggest advantage to doing this is that our formulas become very easy to read and understand.
Naming Ranges - The Basics
To name a range in Excel it is very easy, but there are a few rules that must be adhered to:
To name a basic range in Excel, you simply select the cell or cells you wish to apply a name to, and click at the top of your screen immediately to the left of your formula bar in what is called your Name Box. (This will highlight the active cell address that you will type over - If you haven't noticed before, this box always displays the address or name of the active cell). Type the name that you wish to apply to your range selection. Once you have done this, simply push Enter.
From now on, no matter where you are in your Workbook, you will be able to click the drop arrow to the right of the Name Box and you will see a list of all standard named ranges. Click on the one you require and you will be taken to that location. When you type a formula Starting with an = sign, and then use your mouse pointer to select the cell you wish to reference, instead of Excel inserting the A1 style reference address, it will automatically insert the name you have given the cell.
If you have already set your workbook up with possibly thousands of formulas referencing only cell addresses, and you decide that you should name your ranges but cannot face the task of manually going through changing all cell references to your newly named ranges, don't despair because Excel has made this extremely easy to do. The best way to see this is to follow this simply example.
Imagine you have hundreds of formulas like this all referencing these cells. Rather than go through and change each individually, you could perhaps use Excels Edit>Replace feature to replace all "A1" with "aRange1", but this is really tempting fate and could cause all sorts of problems if you are not fully aware of the consequences of what you are doing. Here is the correct method for doing this.
If you now click back in any cell that was previously referencing a cell address, that you have replaced with it's name, you should see that Excel will have automatically replaced all cell references with the named range. You will have noticed there are a few other options available when using this feature which are very easy to use, but as with most things, trial and error on an unimportant workbook is the best way.
Deleting/Modifying Named Ranges
To delete or modify a Named Range, you must go to Insert>Name>Define or (Ctrl+F3). Once here you simply select the name you wish to delete and click Delete. If you are wanting to modify where the Named Range refers to, simply select its name and then modify accordingly in the Refers to: box at the bottom, then click Add.
Creating Names Very Quickly
Let's look at how we can use existing column and row labels to name ranges in Excel. For this example do the following:
If you now select the drop arrow to the right of your name box, you will see that by selecting one of the newly created names you will be taken directly to that range. This can save a lot of time and mis-typing if you have a large table of data. Don't be concerned that Excel may run into problems if you use too many named ranges, as I have frequently worked on Workbooks containing hundreds without any problems.
What we can now do is cross reference our table and very easily extract out information at the intersection of two named ranges. For example, if we wanted to find out what resided in the intersecting cell of the named range Head3 and Row4 (eg; cell D5), all we simply need to do is type =Head3 Row4 and push Enter. It is very important to note the space between the two named ranges.
As you can see this method can be used in place of some very deeply nested Look-up formulas. The one that springs to mind immediately is the VLOOKUP and/or the HLOOKUP.
Naming Non-Contiguous Ranges
Our ranges that we name are usually continguous, in other words all cell boundaries are adjoining. However, if there is a need to name non-contiguous ranges, we can do this simply by selecting our cell(s) and holding down our Control key. Once we have our selection, we simply again go up to our Name Box and type in our name. Just check you have incorporated the correct range, by selecting the name from the Name Box and seeing where Excel takes you.
Lets assume we have named our non-contiguous range MyRange and it incorporates many different cell references. We can now replace a formula that may have looked like =SUM(A1:A5,D6:D10,Q50:Q57,BB30:CC43) with =SUM(MyRange). Just be very careful when doing this as not all Excel's functions that take ranges as their arguments will exclude the cells between the non-contiguous named range. As with all things, if uncertain, give it a thorough test first.
Naming Constants
It is often when developing a spreadsheet that you will be constantly referring to a specific figure for calculations, such as a Tax Rate, Superannuation Rate etc. This often means either referring to a cell containing the value or simply typing the value in the cell itself. What we can do is instead of naming a cell or range of cells, is name a constant value. For example, you may be using calculations such as =(EmployeeRate*TaxRate)-8%, where the 8% represents the employers Superannuation contribution. We can easily replace the constant value 8% (0.08) with a more meaningful name. To do this:
The two advantages of doing this are:
Naming Formulas
As mentioned above Excel uses Absolute cell references for named ranges, this is normally what is required. But there might be times when a Relative reference would be better. Assume you have a list of 300 employee names in Column A, Column B has their pay rate per hour, Column C the hours they have worked. We would normally use a formula like: =$B2*($C2*24). Note we must multiply the hours by 24 to get the correct result, otherwise a time value like 8:00 would have a real value of 0.333333333333333 times this by 24 and you get a true value of 8. Lets replace this formula with a named formula. Note that the cell selection in this case only reflects a table set up as stated above.
In cell D2 type =PayOwing and copy this formula down and you will have the pay owing for each employee in the list. The very important point to note here is that we had cell D2 selected before we went to Insert>Name>Define and we used a relative row reference in our formula. This means that whenever we copy this named formula PayOwing, it will always multiply the cell two columns to the left on the same row by one column to the left *24.
Sheet Level Names
Normally when you name a range the name is at the Workbook level, meaning the name refers to a specified range on a specified Worksheet. Once the name has been used it cannot be used again to represent a range on another Worksheet. However, sometimes it can be very handy to have one name that will refer to a specified range on the active sheet at the time. Here is how it is done!
Assume we have a Workbook with 3 Worksheets. These 3 Worksheets are simply named Sheet1, Sheet2 and Sheet3. We want to have a named range called MyRange (can be any legitimate name) that will refer to the range Sheet1 A1:A10 when on Sheet1, Sheet2 A1:A10 when on Sheet2 and Sheet3 A1:A10 when on Sheet3. Here is how
Now activate any sheet and click the drop arrow on the Name Box. You should see only one occurrence of the name MyRange. Select this and you will be taken direct to the range A1:A10. Now activate any other sheet and do the same. You will always be taken to the range A1:A10 of the active sheet.
The reason we can do this is because we preceded the name with the sheet name followed by the ! (exclamation mark). If you go into Insert>Name>Define you will note that you only see one name and that name is the one that refers to the current active sheet.
If your Worksheet name includes spaces you cannot simply use Sheet1!MyRange. What you must use is 'Sheet 1'!MyRange. In fact you can use the single apostrophes with a Worksheet name with no spaces. It is a good idea to always use the single apostrophes when referring to Worksheet name as it covers all bases.
Another little trick that can often come in handy is using a relative reference named range. By default, named ranges are absolute but we do not have to leave them this way. For example try this.
You should get 55 and 10 respectively. The reason for this is that we had cell A11 active when we went toInsert>Name>Define and referred our range name to A$1:A$10 which is a relative Column and absolute Row named range. It's the $ sign that forces any range to be absolute.
When we use the name MyNumbers in a formula, it will always refer to the 10 cells immediately above. If you used =SUM(MyNumbers) in cell A11 of another Worksheet it will still refer to cells A1:A10 on the sheet which was active when we originally created the range name.
Ok, lets take this one step further. Suppose we want to simplify the summing of the 10 cells above. Here is how
As you will see we get the same results now but without the need for the Sum function. Have a play about with these types of names, mix up the absolute and relative references and nest a few function together, they can be very handy and save a lot of work in some cases.
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates