Utilizing a named range

Google Sheets 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 then =B5/H12.

Named ranges can also reduce typing errors. Google Sheets will offer suggestions as you enter a formula. If you have named ranges in your workbook, Google Sheets 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.

Copying the practice workbook

If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.

To begin copying the practice workbook in a new browser window,

Right-Click Copy practice workbook, Click Open link in new window

The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.

Move to the new browser window.
To copy the workbook file, in the browser window,

Click

A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.

Creating a named range

We'll create two named ranges and then use them to calculate the net revenue of the fundraiser.

To open the Named ranges panel, on the menu bar,
Click Data, Click Named ranges

The Named ranges panel opens:

The Named Ranges panel.

To make cell D7 the active cell,
Click cell D7
To begin naming the range, in the Named ranges panel,
Click

The Named ranges panel adjusts. We now see the default name for the selected range, NamedRange1, and the location of the selected range, 'Fundraiser Sales'!D7. This location points to the worksheet name, Fundraiser Sales, and cell reference D7. Because we selected cell D7 before we began the naming process, we know this is correct. Let's give the named range a more meaningful name.

NOTE: Names for named ranges can not contain spaces.

To select the default name, if necessary, in the Named ranges panel
Press & Drag NamedRange1
To name the range, type:
TotalSales
To accept this name, in the Named ranges panel,
Click

The Named Ranges panel changes again and now lists TotalSales as a named range.

Let's add a second named range. This time, we'll name cell E7 TotalDonation.

To make cell E7 the active cell,
Click cell E7
To begin naming the range, in the Named ranges panel,
Click
To select the default name, if necessary, in the Named ranges panel
Press & Drag NamedRange1
To name the range, type:
TotalDonation
To accept this name, in the Named ranges panel,
Click

To close the Named ranges panel,
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
To select the column header text,
Press & Drag Net Revenue
To format the column header, on the toolbar,
Click

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

As you type, Google Sheets offers suggestions for possible functions or named ranges, as shown in the following image:

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

To add the subtraction operator, on the keyboard, type:
-
To add TotalDonation to the formula, on the keyboard, type:
tot, Click from the suggestion list

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

To accept and enter this formula, on the keyboard, press:
Enter key

The formula result, $935.55, appears in cell K2.

Utilizing a named range

Copying the practice workbook

Copy the practice workbook to your Google Drive.

Creating a named range

Step 1. Open the Named ranges panel.

Step 2. Begin creating a named range for the total item sales (cell D7).

Step 3. Change the range's name to TotalSales.

Step 4. Name cell E7.

Step 5. Close the Named ranges panel.

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. Accept this formula.