Home Archives for Named Constant
April 9, 2009 Leave a Comment

Save Time and Maintain Consistency When Entering Data

This is every company’s nightmare: You have a list of Customer Names that are entered in multiple worksheets by multiple employees. The (nightmare) result – you have multiple misspellings of the Customer Names! This is GIGO (“Garbage In, Garbage Out”). This is a mess!

This is preventable!

In this Excel Training Video I demonstrate three tips to save you time and help you to maintain consistency when entering long text labels into your worksheets. The Tips:

  1. Use Data Validation – Choose from a List
  2. Use a Named Constant  – you type =”Shortcut” and get the Customer Name spelled out correctly
  3. Use MS Office’s AutoCorrect Option – Create your “Replace and Replace With” in Excel and you can use it in Word and PowerPoint

Here are the steps to follow in this lesson:

  1. Enter the list of Customer Names in a worksheet. Highlight the list and type a NAME for this range into the Name Box. Remember to begin your Name with a letter and you can not use a Space in the Name.
  2. Tip # 1 – Use Data Validation – Allow – List. You can watch my lesson on Data Validation.
  3. Tip # 2 – Create a Named Constant. You can watch my lesson on Named Cells, Ranges & Constants.
  4. Tip # 3 – Use MS Office’s AutoCorrect Option – Go to Tools – AutoCorrect Options in any MS Office Program. For the “Replace” entry, type in your “Shortcut.” I like to make my 1st character an”_” so that it is easy to find, edit or replace. For the “Replace with” type in the full Customer Name. Remember to ht the SPACEBAR when you use this in Word & PowerPoint!

It is easy to find the Excel Training Video you want to view – Search my Index of Excel Topics

New! Danny’s DVD Training Series, “The 50 Best Tips for …” is now available at the online store for The Company Rocks

Link to this post!
Filed Under: Excel Basics, Excel Training Tagged: , , , , , ,
August 13, 2008 7 Comments

3 reasons to use named cells and ranges in Excel forumlas

I use named cells, ranges and constants in my Excel formulas and workbooks for 3 reasons:

  1. They are easier to explain (to others and to myself 6 months after I create a formula.)
  2. They are easier to adapt (to other locations in the workbook – absolute cell references not required!)
  3. They are easier to update (especially named constants in formulas when e.g. rates change.)

These are the steps to follow in this Excel training video:

  1. Select the cells that you want to name and also the labels in the left column and top row.
  2. Choose, Insert, Name, Create. Make sure that Left column and top row are selected.
  3. To verify that your names have been created, click the Name box drop down list of names. Select a name and Excel takes you directly to that cell or range.
  4. To substitute Name cells and ranges in existing formulas: Choose Insert, Name, Apply, OK
  5. When you are writing a new formulas and you want to use a name, use the F3 “Paste Name” shortcut combination to select the name you want in the formula and click OK.
  6. A “Named Constant” does not refer to a cell. Select Insert, Name, Define and then Name the Constant and in the “Refers to” part of the dialog box type in e.g. =0.0825 if you want your Named Constant to refer to a Sales Tax rate of 8.25%

Find the Excel Video Training Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Video Training Topics

Link to this post!
Filed Under: Excel Formulas, Excel Training, MS Excel Training, Tips and Time Savers Tagged: , ,
July 20, 2008 1 Comment

Name a cell and use it in a formula

Here are the steps to follow in this lesson:

  1. A “named cell” creates an Absolute cell reference.
  2. One way to name a cell is to select the cell and type the name you want in the “Name Box” (Do not use any spaces) and hit Enter
  3. To apply the Named Cell in an existing formula, highlight the part of the formula and select Insert, Name, Paste (the named cell) and click OK.
  4. Consider creating a “Named Constant” for numbers you use frequently in a formula e.g. Sales Tax or Inflation Rate.
  5. To create a Named Constant choose Insert, Name, Define and in the “Refers to” box type (for example) = 0.0715
  6. Follow the same procedure to use the Constant in your formula: Insert, Name, Paste (named Constant) and click OK.
  7. Edit the Named Constant if e.g. the Sales Tax Rate changes. All formulas that use the Named Constant (in this workbook) will update automatically!

Click Here to Find the Excel Training Video in My Index

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , ,