Tips for Excel, Word, PowerPoint and Other Applications

Using Named Ranges in Excel

Why It Matters To You

Naming ranges is way of making Excel formula more plain text. If I just rattled off, "Income - Expenses" you'd know exactly what I was talking about. Conversely, if I just shouted at you, C15 - B10, you'd think I was playing Battleship with myself. Named Ranges help keep people from thinking you're playing Battleship.

How To ...

You can name discrete cells or ranges of cells. For example, if I wanted to name cell C6, I'd just click on cell C6, select Insert:Name:Define (or ALT I, N, D) and then choose a name. Here are two input cells I'm going to name.

Excel may try to suggest a name, based on potential titles nearby. However, I wanted something a little more compact, so I assigned my own name, then clicked OK.

There are a couple of rules. You cannot start with a number and the name has to be contiguous. If you have several words, you'll have to connect them with an underscore.

Now, when you write formulas, you can use the names instead of cell values, which makes formulas much more intuitive. For example, when I write a formula to calcualte profitability, I can either use cell references or cell names

Cell References: =D7*(1-D8)
Cell Names =GPW_2005*(1-Combined_Ratio_2005)

Which one is easier to understand?

Notes

Last updated9/4/07
Application VersionExcel 2003
AuthorMichael Kan
Pre-requisitesNone
Related Tips Using Named Formulas in Excel