How many times, when working with spreadsheets, have you come across formulas such as =B7-B20 or =SUM(B10:B19) and spent ages trying to fathom out what they mean? Wouldn’t it be good if we could give logical and unique names to cells and ranges?
Well we can. By naming cells and ranges, our formulas could now become =Total_Income-Total_Expenditure and =SUM(Expenditure) or you could use some other naming strategy that would mean something to you. This process makes cell referencing and cell selection a lot easier.
You can download a sample household accounts spreadsheet >>here<<, in Excel 2010 format, to accompany these instructions.
Open the downloaded file ‘haccounts.xlsx’.
Select cell B6.
If you look to the left of the formula bar, near the top of the screen, you will see the names box (shown below). Highlight the B6 name and type in Total_Income (use the underscore because spaces are not allowed). Press Enter to confirm.
You have now renamed cell B6.
Select cell B19 and rename it Total_Expenditure (remember the underscore). Press enter to confirm.
Select cell B21 and rename it Balance. Press enter to confirm.
Click on the down arrow on the right-hand side of the name box and you should see the three names you created appear.
Naming ranges is a very similar process to naming cells. This time we highlight the cells we want in the range and then we name them as before.
Select cells B4 and B5. In the names box, highlight the current reference (B4) and change it to Income. Press Enter to confirm.
Select cells B9 and B18. Name the range as Expenditure.
Again, these new names will be added to the drop-down box. Check it out.
Select cell B6 (the one we named Total_Income). Click on the Autosum (Ʃ) function (you will find this on the Home ribbon bar in the Editing section). You have now added the formula =SUM(Income), notice it has used the range name we entered. Once more press Enter to confirm.
Select cell B19 (the one we named Total_Expenditure). Click on the Autosum function again. This time the formula should be =SUM(Expenditure). Press Enter.
Select cell B21. Type in the equals sign to let Excel know we are adding a formula by hand. Select cell B6 to add the Total_Income to the formula. Type in the minus sign. Select cell B19 to add the Total_ Expenditure to the formula. The formula in B21 should now read = Total_Income- Total_ Expenditure. Press Enter.
You have now finished the exercise.
He is also the Group Manager of the East Ayrshire Business Forum on LinkedIn.