1.3 Defining and Using Multiple Names at Once

Create from Selection

Creating names is straightforward. However, sometimes we would like to create names for each row and column of data in a dataset. While we could individually highlight the data and row for each name we wanted to create, that would be a bit tedious, especially with a large dataset. Thankfully, Excel includes an option that makes creating names from a table of data more manageable. This is referred to as creating names from a selection.

To create names from a selection, first, highlight the data that you want to have named. Make sure that you include the header row and header column that names will be based on. Then, click on Formulas → Create from Selection.

Excel tries to determine where the values for the names should come from. In our example, it automatically selected the Top row and the Left column. Now, click the OK button. When you do this, the dialog box should disappear, and it seems like nothing happened. However, if you pull up the Name Manager, you will see that it created several new names: one for each column of data and one for each row of data.

Apply from Selection

Sometimes, you create formulas that use cell references, and later you create names for those cell ranges. Wouldn’t it be nice if you could scan existing formulas for named ranges and replace names in the formulas rather than the cell ranges? Excel allows you to do that by applying names.

To apply names to existing formulas that use cell ranges, first highlight the cells that have formulas that you would like to replace. Then, click on Formulas → Define Name (drop-down) → Apply Names.

This will open a dialog box that allows you to select which names you would like to apply. Once you click OK, Excel will search through formulas for cell ranges with defined names and update the formulas in the selected cells to use the names instead of the cell ranges.

Intercepts

Using intercepts with named ranges is a valuable feature. In the following example, names have been created for each of the North, East, South, and West regions. In column J, we want to get the value for the East region for each row for the corresponding month. For example, for January, the East’s sales are 2235. So, instead of using a formula that gets column E’s value for a given row, we can refer to the East named range. If we ask Excel to get the intercept (e.g., =East or =@East), then Excel will find the value for that range for the current row. This is much easier than finding the value individually, especially if the dataset is large.

Intercepts can also be used to find the intersection between two overlapping named ranges. In this example, perhaps we would like to find the value for the West region for the month of February. We can simply refer to both named ranges in a formula with a space between them, and Excel will return the value where those named ranges intersect.

=February West

You could also use

=West February

Both formulas will return the value 1003 as that is where those named ranges intersect.