I was asked a question last week about Excel and how we manage all the calculations we have going on. Doesn’t it get confusing when you can’t recall what cell BalSht!AA192 represents?
Well, we hardly ever have that problem. We use two distinct methods to help control our Excel programmatic flows:
- Cell Naming
- Data and Calculation sheets
In today’s example, I set up a very simple payment calculation worksheet. Here you can see where I begin naming cells; in this case I have named B7 PerPayments, which I am using as short-hand for periodic payments. But in the function wizard, you can see where I named the cell for the Rate = APR, the NPer as Years and Pv as PresentVal. These correspond to B2, B3 and B4.
We set it up this way for two reasons – first and foremost, we try not to create new workbooks. We have standard templates which we use and turn over to clients to use. To make everyone’s life more hassle-free, we think that making it easier to follow the logic makes sense. The second is that, in some of our work, the process is incredibly involved and trying to go back to a particular sheet and cell can be time-consuming and often leads to grabbing the wrong input. Naming eliminates both the fog of not understanding and typing in the wrong cell reference.
Above is a partial list of the names we have running in our projections workbook. There are almost 200 named cells; almost all of which come from the data page we named assumptions. Each of these names actually helps us with some basic calculation on one of the other calc sheets. Organizing our work this way makes it easy for us to navigate through the workbook and even copy sheets with the formulae intact.
Below is what our assumptions page looks like. Currently we have about 85 lines in 9 sections covering about 400 variables which provide the static data we want to evaluate.
ClickDYr4 represents cell E29. We reference ClickDYr4 in about 12 calculations on both the sales and marketing calculations sheets. When we need to update the variable, we simply return to the assumptions page, go to the correct cell and enter it. If we are not sure, we can go to the name manager and look it up.
It also makes formula creation much simpler. 
Naming allows you to simply start typing, instead of using your mouse to jump to the page where you have stored your variable, click on the cell and then hit enter. It can speed up your work and help you make sure you have important calculations documented.
So, how do you name a cell? This is super easy. In the box above Column A it shows the current cell reference. In this case it is referencing B94, which we have defined in column A as Utilities and triple net costs.
Click in the box and then type the name you want. We are naming it NNNCosts for our workbook.
Hit enter. Now it is named. You can then reference it anywhere in the workbook. In our case, we have a facilities calculation worksheet which manages the outflows for facilities in our projection.
There it is, ready to be used in your calculations. It is much easier to follow than what was originally there; =D4*Assumptions!$B$94.
By following these simply and easy to follow rules, you will make your workbooks more reusable and user-friendly. This will make your work in expanding your workbooks easier and also make your work that much more effective. Your team and clients – if you hand your workbooks off to others – will thank you.
Accounting Clarity 