It doesn’t take most regular Excel users very long to get used to the alphanumeric referencing system for cells such A4, C8, BA27 etc, and this is fine when constructing fairly small and simple spreadsheets. However once workbooks and their worksheets become larger than just a few cells, trying to remember which number was in which cell can become quite a task. To help with this it’s possible to give each and every cell in your workbook an individual name, and also to give groups of cells a collective name, which is known as a range.
The big advantage with this feature is that you can use the cell names in formulae rather than its reference, e.g. if you have a value in cell C5 that is your annual sales figure, and you wish to calculate the average monthly sales figure, which of the two formulae below is the easiest one read?
By giving cell C5 the name ‘AnnualSales’, the formula becomes much more readable.
It can obviously be time consuming to enter the cell name for many different cells, so Microsoft have added a nifty little feature to the naming process, which allows you to automatically name your cell assuming it has a an appropriate label in an adjacent cell, though this method can’t be used when naming ranges.
In this short tutorial we will show you how to create names both manually, and automatically.
Creating names manually
Select the cell, or range of cells, that you wish to name, then click in the cell name box (red circle). Type your cell name in here and press enter and your cell will be named.
Creating names automatically
Select the range of cells to be named, along with the cells containing the label
- From the ‘Insert’ menu select ‘Name’ and click on ‘Create’
- You will presented with a dialog asking where the labels are, for our example above we would use ‘Left Column’ and click ‘OK’
- Cells B1, B2, B3, are now named according to the values in cells A1, A2, A3
Once cells have been named, it’s possible to navigate around your worksheet using the dropdown list on the cell name box, by clicking on the name of a cell in the list, you will be taken directly to it.