Naming Cells and Ranges

Naming Cells and Ranges

You’re no doubt used to referencing cells by their cell addresses – the name Excel gives them based on their column letter and their row number – but you can also give them more meaningful names.

This process involves the use of the Name Box, found to the far left of the Formula Bar. This is where you see the address of the cell you’re in at the time, as shown here. I’m in cell D6, and you see that address in the Name box.

Note that when you’ve selected a range of cells, the Name Box momentarily displays the number of columns and rows, such as 6R x 2C, for 6 rows and 2 columns, but then it displays the first cell in that range. So, if I drag through cells B4 through F15, it briefly displays 12R x 5C, but then as soon as I release the mouse, it displays B4, the first cell in the range.

First, let’s look at how we can name a cell. I’m going to select cell C6, which contains the US Sales Division total sales for 2021.

To name it, I just select the C6 showing in the Name Box and type the new name: US_Sales_2021

I’ve used underscores to prevent spacing – you can’t have spaces in cell names.

I can repeat this in cells C7 and C8, for the European Sales and Asian Sales, naming each one as follows:

European_Sales_2021 in Cell C7

Asian_Sales_2021 in Cell C8

Now, to use these names as I calculate the percentage each division’s sales represent of the total sales, as soon as I type, for example, “US” – Excel offers up the named ranges starting with those letters. I can double-click the one I want from the list, and it’s inserted into the formula.

Because I already named cell C9 Global_Sales_2021, I can type a G and see that in the list, too.

Now my formula is:

=US_Sales_2021/Global_Sales_2021

This is a much clearer formula for someone unfamiliar with my data, or if the formula was on a worksheet separate from the cells that are used within the formula.

To name a range of cells, select the range and then either right-click and choose Define Name or go to the Formula tab and choose Define Name there.

In the resulting dialog box, type the name you want to use – often replacing what Excel has guessed based on adjacent cells’ content – and then click OK. To demonstrate, I’ve selected cells C6 through C8 and I’m calling the range US_Europe_Asia_Sales_2021.

Named ranges can be used in formulas and functions, the same way named cells can – as you begin typing your formula, instead of dragging through the range, just begin typing the name of it, and choose the one you want from the selection offered in a pop-up. To show you this in action, I’ll re-do the sum in cell C9 and use the range name rather than dragging through the cells.

Of course, this means it’s important to name your cells and ranges in a meaningful way – so that you can recognize and choose them easily as you put them to use in your formulas and functions.

If you want to edit or delete any named cell or range, just select Name Manager from the Formulas tab and choose the name you want to edit or remove. You can then use the appropriately named buttons to complete your task.