Named Ranges
Named
ranges are a great way of navigating through your spreadsheet, simplifying your
formulas and maintaining the accuracy of your calculations. One way to create a
named range is to use the Name Box.
First we’ll create a named range that has only one cell. Go to cell B2.
Notice that at the top left corner of the Excel window there is a box containing
the location of the cursor–in this case, B2. It’s called the Name Box.
Click inside the name box and change B2 to
Length. Be sure to hit the Enter
key so that the name change is registered. Change the selection to B3, then type
Width in the name box and hit Enter. Finally, change the selection to B4, type
Height in the name box and hit Enter. It will be easy to forget which is which
when you’re in another cell, so go to A2:A4 and type in Length, Width, and
Height as labels. And while you’re there, type in some values in column B.
Maybe 7, 4, and 2. Your spreadsheet should look like the picture below (except
for the result!)
Now to calculate the result. In cell A5 type Volume. In B5 type
=Length*Width*Height. Excel has recognized the named ranges and used them in the
calculation. Note that if your spreadsheet already has labels, you can use them
to automatically created named ranges. Watch this movie for details.
Ranges
larger than a single cell are used as the data source for Charts, PivotTables or
formulas that have an indeterminate number of cells like Sum or Average. Create
a range of bogus data from C1:C30. You can use =RAND() to make random numbers.
While the range is highlighted, type TestResults in the name box. Now let’s
base a chart on this range. Click the Chart Wizard icon: Choose any line chart
in step 1. In Step 2 type TestResults for the data range, then click finish.
What happens when you want to include more data in your chart? The easiest thing
to do is to update your named range—the chart will update itself. Fill a few
more rows with data beneath C30. Highlight the entire region, both new and old
data. Go to the Insert menu at the top of the Excel window and choose Name then
Define... You’ll be tempted to click on TestResults in the list box but don’t
do it!
You must retype the name TestResults in the text box at the top to
re-define the name as opposed to choosing the old name (with the old definition)
from the list. You’ll see that your chart is updated with the new data from
your named range.
The process is even simpler in VBA. Simply type
Range("TestResults").CurrentRegion.Name = "TestResults"
to
replace the old TestResults range with the new.