Utilizing a named range

Available Files

The following files are available for download:

Download Exercise Files

Learn more about using exercise files.

Microsoft Excel gives us an easy way to assign descriptive names to cell ranges. These named ranges can be used in formulas and functions in place of a cell reference.

Using a named range offers several benefits. First of all, a descriptive name is more meaningful than a cell reference. For example, a formula that reads =PointsEarned/TotalPoints is easier to understand than =B5/H12.

Named ranges can also reduce typing errors. Microsoft Excel will offer suggestions as you enter a formula. If you have named ranges in your workbook, Excel will show those names as suggestions.

Finally, named ranges can be used as absolute references. If a named range is used in a formula or a function, that reference will not adjust relative to the row.

Creating a named range

We'll create two named ranges and then use them to calculate the net revenue of the fundraiser. As is often the case in Microsoft Excel, there is more than one way to complete this task. We will create the first named range, TotalSales, using the Name Box and the second named range, TotalDonation, using the New Name dialog box (Microsoft) or Define Name dialog box (macOS).

NOTE: It is important to note that named ranges can not contain spaces and must start with a letter.

To make cell D7 the active cell,
Click in cell D7
To begin naming the cell, to the left of the Formula bar,
Click
To replace the cell reference with the name, type:
TotalSales
To accept this name, on the keyboard, press:
Enter key

Cell D7 is now named TotalSales.

Let's add a second named range. This time we'll use the New Name/Define Name dialog box to name cell E7 TotalDonation. The name tools are on the Formulas tab of the ribbon.

To make cell E7 the active cell,
Click cell E7
To move to the Formulas tab,
Click the Formulas tab
To open the New Name/Define Name dialog box, in the middle of the ribbon,
Click

The New Name - Windows or Define Name - macOS dialog box appears:

New Name dialog box from Microsoft Excel for Windows

At the top of the dialog box is an area to add the name. At the bottom of the dialog box is the cell reference that the name will refer to, cell E7 on the Fundraiser Sales worksheet.

To begin naming the range, in the New Name (Define Name) dialog box,
Click in the Name field
To name the range, type:
TotalDonation

To close the New Name/Define Name dialog box,
Click

Using named ranges in a formula

Now that we have created two named ranges, we can use them in place of cell references in a formula. Let's create a formula to determine the net revenue of the fundraiser. First, let's add a new column header to column K.

To make cell K1 the active cell,
Click cell K1
To add the column header, type:
Net Revenue, Enter key
To make cell K1 the active cell, on the keyboard, press:
Up Arrow key
To move to the Home tab, on the ribbon,
Click the Home tab
To format the column header, on the left side of the Home tab,
Click
To widen column K,
Double-Click between the K and L column headers

To make cell K2 the active cell, if necessary,
Click cell K2
To begin writing the formula, in cell K2, type:
=tot

As you type, Microsoft Excel offers suggestions for possible functions or named ranges.

Close-up of cell K2 with the beginning of the formula displayed and the suggestions pop-up visible underneath the cell.

To calculate the net revenue, we will want to subtract the amount of donations from the total sales. We can see both the TotalSales and TotalDonation named ranges in the suggestion list.

To add TotalSales to the formula, in the suggestion list,
Click , press: Tab key

To add the subtraction operator, type:
-
To see the list of possible functions and named ranges, type:
tot
To add TotalDonation to the formula,
Click , press: Tab key

The formula should be as follows: =TotalSales-TotalDonation.

To finish entering this formula, on the keyboard, press:
Enter key

The formula result, 935.55, appears in cell K2. It may be necessary to format this value as currency.

Make cell K2 the active cell.
To format the contents of K2 as currency, in the middle of the Home tab,
Click , Click Currency

To save the workbook, on the keyboard, press:
Control key + S

UITS IT Training Content Library

Utilizing a named range

Available Files

Need help with the available files?

Creating a named range

Using named ranges in a formula

Utilizing a named range

Available Files

Need help with the available files?

Creating a named range

Step 1. Create the named range TotalSales.

Step 2. Open the New Name/Define Name dialog box.

Step 3. Create the named range TotalDonation.

Step 4. Close the dialog box.

Using named ranges in a formula

Step 1. Add the column header "Net Revenue" to cell K1.

Step 2. Begin writing a formula to calculate the net revenue.

Step 3. Add TotalSales to the formula.

Step 4. Add the subtraction operator and TotalDonation to the formula.

Step 5. Finishing adding the formula.

Step 6. Format the Net Revenue amount as currency, if necessary.

Step 7. Save the workbook.