OzGrid

Lesson 24 - Excel's Constants and the Paste Name Dialog

< Back to Search results

 Category: [Excel]  Demo Available 

EXCEL'S CONSTANTS AND THE PASTE NAME DIALOG

 

Lesson 24 - Using Constants to Name Values and the Paste Name Dialog

CONSTANTS

There is one other area of Naming we would like to cover and this is naming CONSTANTS. A constant is simply a value that is not the result of a formula. So if we type 10% or Davidor 12/12/2000 into a cell these would all be constants, as their values would not change unless we changed them.

Quite often when setting up a spreadsheet some users will type a value or text that they will be using a lot into a cell and then refer to that cell in their formulas. For example lets say we are setting up a spreadsheet that will constantly be using the percentage 10%. We could place this into a cell somewhere and then name this cell Ten Percent. We could then use this named cell in all our calculations that require 10%. Let's try this to see how it works.

  • Type The Value 10% In Any Cell.
  • With This Cell Selected Go To The Name Box And Type In Ten Percent And Push Enter.
  • Now In Any Other Cell Type =100*Ten Percent And Push Enter.
You should have the result 10, which is what we would expect by multiplying 100 by 10%. The biggest advantage to this method is that if we need to change 10% to say 20% we simply go to our named cell Ten Percent and type in 20%. All our formulas that are using Ten Percent in their calculations will change accordingly. The down side to this method is that it is all too easy to accidentally delete the value in our named cell. A better method is the one below.
  • Go To Formulas>Define Name (Or Push Ctrl + F3)

  • Select Ten Percent And Then In The Refers To Box Type 10% Straight Over The Top Of The Cell Address.
  • Click Add Then OK.

What we have now done is named a constant. This is a better method as it is very unlikely this could be accidentally deleted.

PASTE NAME DIALOG

You may end up with a Workbook that has many named constants and it is a bit hard to remember them all! Not to worry as Excel has a feature called the Paste name dialog. This can both insert a named constant or range and create a list of all our names within the Workbook. We can see this best if we create some named ranges and some more named constants. Do this using the methods above. When you have created 3 named constants and 3 named ranges try this.

  • Type 0 In Any Cell Then Before Pushing Enter Push F3 Or Go To Formulas>Use in Formula.
  • From The Paste Name Dialog Box Select Ten Percent And Click OK
  • Excel Will Insert The Name For You. This Can Be Very Handy When Using Named Ranges And/Or Constants In Formulas.
  • On A New Worksheet Select Any Cell And Push F3 And Click The Paste List Button. Excel Will Create A List Of All Named Ranges And Constants In The Workbook. The Names Will Be In One Column And What Or Where They Refer To In The Other. The Only Reason We Suggested A New Worksheet Is So That The List Is Not Pasted Over The Top Of Existing Data.

Go back to:

Lesson 1 - Excel Fundamentals
Lesson 2 - Starting Excel and Excel Workbooks
Lesson 3 - Excel Toolbars and Task Panes
Lesson 4 - Excel Worksheets
Lesson 5 - Excel Cells and Navigating a Worksheet
Lesson 6 - Excel Cut/Copying and Pasting Data
Lesson 7 - Excel Copying with the Fill Handle
Lesson 8 - Excel Paste Special
Lesson 9 - Excel Insert Command
Lesson 10 - Excel's default options
Lesson 11 - Excel's Undo and Redo
Lesson 12 - Excel's Format Painter
Lesson 13 - Excel's Dates and Times
Lesson 14 - Excel's Custom Formats
Lesson 15 - Excel Formulas
Lesson 16 - Excel Cell References
Lesson 17 - Excel: Avoid Typing
Lesson 18 - Excel Formulae Arguments & Syntax
Lesson 19 - Excel Autosum Formula
Lesson 20 - Excel Auto Calculate
Lesson 21 - Excel's Insert Function
Lesson 22 - Excel's Useful Functions
Lesson 23 - Excel's Named Ranges

See also:

Lesson 25 - Excel's Calculations
Lesson 26 - Excel Comments Cell
Lesson 27 - Excel Find and Replace
Lesson - 28 - Clear Excel Cell Contents
Lesson 29 - Effective Excel Printing 1
Lesson 30 - Effective Excel Printing 2
Lesson 31 - Sorting in Excel
Lesson 32 - Hide/Show Row/Columns in Excel
Lesson 33 - Auto-Formats in Excel
Lesson 34 - Creating a Basic Excel Spreadsheet
Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet
Lesson 36 - Excel Worksheet Protection
Lesson 37 - Excel IF Formula Nesting
Lesson 38 - Excel Function Now/Today Formulas

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 

 


Gallery



stars (0 Reviews)