Excel: Naming cells and ranges

When you work with worksheets, you will often type cell addresses that reference to a particular cell or a range of cells (for example, Sheet2!A3:D5). In Excel, you can name those cells to better describe their contents. For example, it is easier to understand the notation

=SUM(cost)/month

than

=SUM(B3:B14)/D5

Naming cells

Look at the table below. There are names of three persons and the number of hours they worked each day of the week.

 

Let's suppose that you want to choose all the hours worked by men at once, without selecting cells manually every time. 

Example 1:

Select cells from C3 to G3, then, while holding down the Ctrl key, select cells from C5 to G5. After you do this, enter text "Men" in the Name Box.

 

In our example, we have only one woman. To create a named range for her, select cells from C4 to G4 and this time type "Woman" in the Name Box

How to name multiple ranges at once

Suppose that you want to select the working hours for each day separately. You can do this in a similar way as you did it for "Men" and "Woman". In this case, you would need to make five selections for each day separately. However, to speed up the process you can create all of them at once.

Select cells from C2 to G5, then go to FORMULAS >> Defined Names >> Create from Selection.

The Create Names from Selection window will appear, asking you which cells you want to use as names. In our case, there will be cells from the top row. 

 

Click OK, then the arrow in the Name Box. Here, you will find two groups that you've created earlier: “Men” and “Woman”, as well as a group of hours for each day of the week.

 

Selecting ranges

There are several ways to select previously created groups. 

Example 2:

The first method is to type the group name directly into a cell. 

CAUTION

If you type the name =Men then Excel will return an error because you cannot enter range into one cell. In this case, you can use it in a function. 

In this example, we will use the SUM function to calculate the sum of hours worked by men. When you start typing the formula, and you get to =SUM(Me, Excel will display a hint.

 

To accept this group- click it or use the Tab key.

Example 3:

The second way is to select a group of FORMULAS >> Defined Names >> Use in formula.

 

Example 4:

The third method is Paste Name. Press the F3 key. When a window appears, select a range from the list.

 

CAUTION

If you move all cells in a range, Excel will remember their new position. However, if you move only a part of them, Excel won’t be able to select them when you select a position from the Name Box

Name manager

In the name manager, you can manage names of ranges. You can add, delete and edit them. To use Name Manager, go to FORMULAS >> Defined Names >> Name Manager or use the Ctrl + F3 keyboard shortcut.

Download attachment