Cell NAMES (Names part 1)
We use NAMES to define cells all the time. It's so much easier to understand formula.
As part of any good design we set one tab to be a control page where parameters such as the period end date are set. By calling the period end date 'Period_End' we never need to remember absolute cell reference.
If the Period_End date was in cell B2, to fix it you would have to add the dollar signs to the cell, i.e. $B$2 however this is not needed with a cell NAME.
To create a cell NAME all you have to do is to type in to the cell reference as follows:
or you can create, amend and delete NAMES from the Name Manager from the Formulas tab.
Once your NAMES are defined it can help in the understanding of a formula. Take the example below with the formula:
=(C4-B4)/D4*(Contol!B2-C4)
The formula has been used to calculate an electricity accrual by taking the actual charge and then calculating the daily cost over the service and then multiplying this by the number of days since the last invoice up to the current period end.
To fully understand the formula you would have to press F2 to highlight where the cells referenced, obviously excluding any cells not on the worksheet.
But by defining NAMES the formula becomes a little more understandable as follows:
=(Invoice_Date-Start_Date)/Invoice_Value*(Period_End-Invoice_Date)
And by using standard NAMES it becomes very easy to understand a formula, particularly when you are dealing with more complex ones.
So start using cell NAMES to make your formulae more understandable and easier to write. You won't have to remember the absolute references for standing data such as tax rates, period and year ends, what-if percentage increases, etc... and if someone else has to look at the formula it should be easier for the to review it when useful NAMES are used.
