Simplify Formulas in Microsoft Excel
One of the nice things about spreadsheets is the ability to create formulas referencing other cells. Excel offers several ways to improve the readability of formulas.
Named ranges. If your formulas are a mash of letters and numbers you will be happy to
know you can name
individual cells. For example, open a new spreadsheet and with cell A1 selected choose the Insert/Name/Define...
menu item. Type test in the top box, and notice the bottom field refers to cell A1. Click
OK to save.
Excel will now show the word "test" instead of "A1" in the upper left when cell A1 is selected. Select cell B1, type "=test+2" and hit Enter (you can also click on cell A1 instead of typing its name). Notice the formula for B1 shows the word "test" instead of the cell number.
Another benefit for those of you with large spreadsheets is that you can use the pull down menu in the upper left of Excel's screen (where the cell number is shown) to quickly jump to the "test" cell. Typing a name and pressing Enter works as well.
Note when copying cell B1 to another cell, Excel keeps referencing the cell test instead of adjusting the reference as it would if you had referenced the cell using "A1." You no longer need to use the "$A$1" syntax.
Named constants. Similarly, you can create a constant in Excel by using the same Insert/Name/Define... procedure and typing a number or text in the Refers To... area.
Easy named range creation. If you want to create multiple named ranges at once or would like to leave a label for the cells inside your spreadsheet, type the label into the adjacent cell. Select both and use Insert/Name/Create... to define the appropriate names for your cells.
January 2003
Send this article to a friend!
Subscribe to The ITS Connection
Related articles