Custom Names - Constants in Excel

Custom Names - Constants in Excel

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:

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.)

You should see the New Name dialogue box appear:

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:

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 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:

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

You'll see the following dialogue box appear:

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 section, we'll explore Pivot tables in Excel.

Posted in: Microsoft Excel,Microsoft Excel 7 (Processing Data in Excel),Tips,Trick,Tutorial,Tutorial Excel 7