If there is one skill in Excel we wished we had learned much earlier in life, this is it. If you learn just one thing from our Excel tips, let it be this. Use named ranges to make formulas more intuitive, protect your links and speed up creating formulas and spreadsheets.
If names are new to you, follow along and do the Names Exercise. The results of the exercises are in a separate file called Names Result.
Named ranges can be a single row, single column, entire sheet or block of data. The simplest way to name a range is to highlight the range you want to name, then type in the name in the name range box. We can use a simple example.
In the example above, we highlighted the sales row, then typed in sales in the named range box (which appears above the A column and then clicked enter.
Another way to name a range is through the menu. Select Insert, then Name, then Define.
Then the Define Name dialog box will pop up. Since we already had the row highlighted, that is referenced in the location section. We then typed in cost_sales for the range name. We then click OK to finish.
Note that names cannot have spaces, so we used an underscore between cost and sales. Names also cannot conflict with any Excel function. Sum could not be a name for a range for example. Nor could Q1 for the first quarter, since Excel might think you are referencing cell Q1.
Now to put our named ranges to work. Let's calculate gross margin in our example.
We type in =sales-cost_sales for our formula. As you can see, one benefit of using names is that you have very intuitive formulas. While this is a simple example, for very large worksheets or for working across spreadsheets, named ranges can make formulas easier to create and follow.
Notice when we copy the formula across, the formula looks the same in each cell.
Here is how it would look totaling across columns. Let's total the industrial and consumer sales. First we would create names for each segment (direct, wholesale, retail, Internet). Then we would build the formulas to add these. Besides typing in the names, another way we can do it is by pasting the names in via the menu. Select Insert, then Names, then Paste.
You then get the Name Paste dialog box and can select the name to add in to the formula.
Below is the rest of our result, with one formula highlighted.
Here is one nice thing about names. They are not case sensitive. Excel will automatically correct the name in a formula to the case of the name you set up. If we had typed in Industrial in the formula above, Excel would automatically have changed it to industrial in the formula since we set up the name without a capital I.
This is a very simple worksheet. If all by itself, you probably would not use names here. However, it is good to practice with names first in simple examples. Next we will show where names have their real power, including:
blocks of data
with multiple worksheets
with multiple files