Excel Pro Tip: How Names Save Time
Excel® is a robust computer program for multiple tasks, from creating personal budgets and supplemental accounting documents to troubleshooting accounting software and even programming some software. With all that it offers, many of us still only use a fraction of its features. This series — Excel Pro Tips — covers some of the best formulas, tables, conditional formatting, and other processes that benefit users. Today’s article looks at the use of Names to simplify cell referencing.
You have been creating a financial workbook in Excel for hours. It includes everything from cash flow statements to production forecasting models. With the nearly 30 spreadsheet tabs it is becoming daunting to try and find the proper cell references when making formulas. You may find yourself clicking from spreadsheet to spreadsheet, scrolling up and down on each one trying to find if the number you need is in cell AA136 or cell N54. There is a faster and better way to find and reference cells and it involves renaming.
Many will be aware of the white box in the upper left next to the formula bar. That white box tells you the cell name. If you were to click on cell D15 in the spreadsheet the name box in the upper left would show D15. Now, let’s say that in D15 you have the current market price of diesel. Elsewhere on the spreadsheet you have been working on a trucking cost calculator for hauling grain to different elevators. As you build the formula you are constantly having to remember D15 is the cell with the price of diesel. Try this instead. Click on D15 and then click in the name box in the upper left. D15 should now be highlighted in blue. Type in “fuel” and hit enter. Now every time you click on D15 the name box shows “fuel.”
In cell D17 we will make a formula that figures the total cost of fuel for a given price and tank size. Let’s type "= fuel*D16" and then Enter. The formula now is doing an absolute reference to the cell that has been named “fuel.” This feature may seem trivial for one spreadsheet’s short calculations, but when you start to use the Name feature in a workbook of numerous spreadsheets, it speeds up the process immensely.
When the cell name you want to use includes several words, for example “rate of gain,” your options are to either delete the spaces or use underscores (for example, “rateofgain” or “rate_of_gain”). Once a cell is named, it can be referenced on any spreadsheet within a workbook.
If you have named the same cell twice, or no longer want it named, there is a process to remove the name. In the Excel ribbon at the top of the page, click on the Formulas tab, and then on Name Manager in the Defined Names Section. From the pop-up screen you will have the option to make a new name, edit an old name, or delete a name.
If you are working along and forget where a name was, Excel can also take you straight to the cell. In the cell name box in the upper left there is a drop down arrow. When you click on it a drop down list will appear with all the named cells within the workbook. By clicking on one of the names, Excel will automatically switch to the spreadsheet and cell in which that name is located.