Using named cells and ranges —A handy Excel trick

Most the work in excel is done through the use of functions and almost all of the functions require either a cell address or a cell range as one or more of its inputs. The cell address and ranges can be confusing and cryptic. In excel, you can assign descriptive names to both cells and ranges. For example, you can name a cell range as July_sales or applicant_name instead of misleading cell addresses.

There are a couple of ways to create a name. The easiest one is by using the name box. The name box is a drop-down list that contains all names of the workbook.

0. Select the cell or the cell range

1. Click on the name box

2. Type the name and press enter

Another way is to create a lot of names at once is to use the selection box. The method creates names that define adjacent columns or rows. Here are the steps to create name from selection.

1. Select the table containing the data and the names you want for those data

2. Click on the Formula tab in the Ribbon

3. Click on Create from selection option. A dialog box appears

4. Select the appropriate options from the dialog and click on OK. In the example below, since the options Top row and Left Column are selected, the values of the topmost row (January, February and March) and the leftmost column (Region_1, Region_2 and Region_3) are created as names.

The names of the January, February and March represent the data of the columns J, K and L respectively of the selected data barring the first cell. The names Region_1, Region_2 and Region_3 represent the rows 2, 3 and 4 respectively of the selected data barring the first cell.

Note: Since the name does not allow for spaces, the spaces are replaced with the underscore ( _ ) as seen in Region_1, Region_2 and Region_3.