Excel Custom Named Ranges

You can set up a custom name to be used as a constant. An example of a constant is PI when working with circles. If you need to use PI in formulas you don't need to type out 3.1415 all the time, you can just do this:

= PI() * 5

We'll use the spreadsheet below to set up our own custom name:

An Excel spreadsheet to work out custom names

We'd like to work out a discount, depending on the shoe type. So Slingback shoes might have a discount of 5 percent but Stacked Heels might have a discount of 12 percent. To set up a custom name, click on the Formulas ribbon at the top of Excel. On the Defined Names panel, select Define Name > Define Name: (In Excel 2010 and 2013, locate the Defined Names panel instead.)

Defined Names Panel in Excel 2007

You should see the New Name dialogue box appear:

The New name dialogue box

In the Name area at the top, type slingback_discount. In the Refers to textbox at the bottom, you can type a cell reference or a formula. Type =5% in the textbox, though. Then click OK when your dialogue box looks like this:

The New name dialogue box showing a custom name set up

To use your new custom name click into cell D2 to select it. Then click into the formula bar at the top. Type = C2 *. Then start typing your custom name. As soon as you type the "sl" you'll see a popup box appear:

The formula auto-complete in Excel

The popup box should have your custom name on the list. Double click it and Excel will add it to your formula. Because we typed =5% in the Refers to textbox of the New Name dialogue box the constant slingback_discount will always be 5%.

Press Enter when the formula is complete and you should see the discount appear in cell D2:

Spreadsheet showing a custom name formula

If you want to delete a custom name, click the Name Manager on the Defined Names panel:

The Defined Names panel in Excel 2007

You'll see the following dialogue box appear:

The Name Manager Dialogue box

If you want to edit your custom name, click the Edit button at the top. To delete a name, simply select the name from the list then click the Delete button.

In the next part, you'll see how to set up a Name to associate text with numbers.

<--Back to the Excel Contents Page