Excel Tutorial Series Introduction

This is part one of the Excel Tutorial Series. Read all tutorial posts by browsing the Tutorial category.

A long time ago I promised to post my Excel training class course material, and I've finally gotten around to doing so.

The following series of posts will be based on that material. I'll post as much as I can, but unfortunately some of the material is proprietary, so I'll only post the parts that I'm able to.

It's a lot of material so I'll break it up into several posts, each one about a different topic. Some of it is beginner and intermediate Excel and VBA, so if you know your way around Excel and VBA very well, this will just be a refresher for you. I'll also include a list of links for further reading. If you know of a good link that I missed, post it in the comments.

Here's what we'll be covering:

Naming a Range

Naming a range has several benefits:

You can give your calculation cells a 'friendly' name, so instead of "$B$5" you can use the name "Total_Cost". This makes the intent of the cell much clearer and avoids "magic numbers."
The name "sticks" to the range, so you can refer to the name in formulas, instead of the cell location. If you move the named range, the name refers to the new location. This avoids inefficient "formula fixing" sessions where, when you have to move a formula to a new cell, you have to find all the cells that referred to the old location and change the cell reference.

If you name your cells or ranges, and you already have formulas referencing those cells, you can replace cell references in existing formulas with their names. See Replacing Cell References for assistance.

The Name Box

Select a cell, or highlight a range of cells, and use the Name box to give the range a name:

namebox

The "Define Name" dialog box

You can also define a name by using the Define Name dialog box. Press Ctrl-F3 or go to Insert » Name » Define to access this dialog box.

definenamebox

An additional bonus is you can use the Define Name box to create named constants: unchanging values you can keep off your worksheet, and use in your formulas.

The steps are the same as for creating a named range in the Define Names dialog box: go to Insert » Name » Define. In the textbox at the top, type in the name you want, and in the "Refers to" box, type an equals sign, followed by the value you want the name to represent.

For example, if I wanted a constant representing the number of days per week, I could enter the number 7 into a cell, then use the name box to name the cell. But now my worksheet has another dependency to track, and my formula is linked to a cell, and we have to think about hiding the cell to avoid accidental deletion, when all we really wanted was the number 7. Instead I'll use a named constant as follows:

Named Constants Dialog Box