MULTI IN ONE: Constants in Excel

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>= PI() * 5</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
We'll use the spreadsheet below to set up our own custom name:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="An Excel spreadsheet to work out custom names" height="236" src="http://www.homeandlearn.co.uk/excel2007/images/2010/custom_name_start.gif" width="533" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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 <b>Formulas</b> ribbon at the top of Excel. On the <b>Defined Names</b> panel, select <b>Define Name > Define Name</b>: (In Excel 2010 and 2013, locate the <b>Defined Names</b> panel instead.)</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Defined Names Panel in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2010/define_name_ribbon.gif" width="195" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You should see the <b>New Name</b> dialogue box appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The New name dialogue box" height="230" src="http://www.homeandlearn.co.uk/excel2007/images/2010/new_name_db.gif" width="307" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the Name area at the top, type <b>slingback_discount</b>. In the <b>Refers to</b> textbox at the bottom, you can type a cell reference or a formula. Type <b>=5%</b> in the textbox, though. Then click OK when your dialogue box looks like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The New name dialogue box showing a custom name set up" height="231" src="http://www.homeandlearn.co.uk/excel2007/images/2010/new_name_db_2.gif" width="307" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To use your new custom name click into cell D2 to select it. Then click into the formula bar at the top. Type <b>= C2 *</b>. Then start typing your custom name. As soon as you type the "sl" you'll see a popup box appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The formula auto-complete in Excel" height="165" src="http://www.homeandlearn.co.uk/excel2007/images/2010/custom_name_popup.gif" width="371" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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 <b>=5%</b> in the <b>Refers to</b> textbox of the New Name dialogue box the constant <b>slingback_discount</b> will always be 5%.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Press Enter when the formula is complete and you should see the discount appear in cell D2:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="Spreadsheet showing a custom name formula" height="254" src="http://www.homeandlearn.co.uk/excel2007/images/2010/custom_name_formula.gif" width="517" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If you want to delete a custom name, click the <b>Name Manager</b> on the <b>Defined Names</b>panel:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Defined Names panel in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2010/name_manager.gif" width="195" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
You'll see the following dialogue box appear:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img alt="The Name Manager Dialogue box" height="344" src="http://www.homeandlearn.co.uk/excel2007/images/2010/name_manager_delete.gif" width="448" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
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.</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next section, we'll explore Pivot tables in Excel.</div>
</div>

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