When you develop an Excel spreadsheet with formulas and complex calculations, you are likely to have many references to key cells in that worksheet where the calculations are completed. Those completed calculations are used in other calculations or are needed for summary displays.
Rather than trying to remember the cells specific reference eg C27, you can create a Name for that cell that can be used in the workbook to reference that cell. For example if the worksheet contains Sales Data and the cell in question is the total of sales for all sales staff it may be called TotalSales. Then wherever we need to use that value we can refer to the name rather then the specific cell reference.
Sales Data Example
In the following example we have a Sales Data worksheet that lists the Sales for each staff member and the Total Sales for all staff members at the bottom of the worksheet.
Now we want to show a value for the percentage of total sales made by each staff member. We could do this by simply including a calculation in column E to reference cell D13.
Now to complete the other cells we could drag cell E6 down for the other sales staff. But we would have to change the reference to Total Sales as it would not refer to D13 and the calculations would be incorrect.
We can solve this in a number of ways the first would be to change the formula in cell E6 to include an “absolute reference” to D13. The formula would then become =D6/$D$13. Then when dragging that formula down to the other cells the reference to $D$13 would remain in place and the calculations would be correct.
For this example we will create a Name for cell D13 and then replace the formula reference to D13 with that name. To create a name simply select the cell D13 and then beside the formula bar type in the name for that cell. Names cannot include spaces and must be numbers or alphabetic characters.
Now we have a name for the total sales we can use it in the calculation of the % of total sales in column E. So now we replace the formula in cell E6 to include the name TotalSales and then drag that formula down for each row in the table.
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.