 |
| Using Names in Excel to save time and effort 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. Step 1 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.  Step 2 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. Step 3 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.  Step 4 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.  This is just one example of how "Names" can be used to save time and effort when building spreadsheets. If you would like any advice or assistance in implementing Names for one of your workbooks then please Contact Us and one of our consultants will step you through the process. Subscriber Special !!
25% off all Consulting until the end of November, 2010 Contact Us now and mention this newsletter offer to secure your discount If you are not yet a subscriber to the XLAutomation newsletter then Click Here to subscribe and receive guidance and tools that will increase your efficiency in using Microsoft Excel. |
Job Tracker Launch special - 50% saving on offer !! Get a head start on the rest of the market....Be one of the first 10 people to buy Job Tracker and receive a huge 50% discount off the purchase price ! Simply Click Here to register your interest and you will be sent an email the day before its release to secure your 50% discount. What is Excel Dashboard Reporting? Business managers know how important it is to continually track their companies performance in order to improve, make better business decisions, and create new opportunities. Excel Dashboard Reporting makes this process simple and extremely effective. Imagine with the click of the mouse seeing your most useful information in a single page of visual charts ready to compare at a glance. Click Here for more information and to see a video presentation |
| |