Call us on 0844 800 7880
This weeks Bite Size IT tip : MS Excel 2010 - Naming Ranges

Introduce Me

new mouse glasses  thumb
Creating Named Ranges 

Named Ranges provide a feature enabling you to assign names to one or more cells.

This provides benefits such as making it easier to find a cell(s) within a workbook or making formulas easier to understand. For example, imagine a situation of converting a currency value to Euros. You might hold the Euro exchange rate on Sheet 25, cell Q2598 (a sheet reserved for exchange rates). Instead of a formula such as A10*Sheet25!Q2598 you could write A10*EuroRate where EuroRate is a name referring to Sheet25!Q2598.

Also, if you need to find the Euro rate quickly you can simply go to it using Excel's Find and Select feature. Let's see how named ranges are created.

Tell Me

 

To Create a named range:

  1. Click on the cell(s) containing the data you wish to name.
  2. Click in the Name Box just above Column A.
  3. Type a name for the range.
  4. Press Enter to register the name.

MS Excel 2010 - Creatingnamedranges01

 Here are some of the syntax rules for named ranges:

  1. Valid characters:  The first character of a name must be a letter, an underscore character (_), or a backslash (\). Remaining characters in the name can be letters, numbers, periods, and underscore characters.
  2. Cell references disallowed:  Names cannot be the same as a cell reference, such as Z$100 or R1C1.
  3. Spaces are not valid:  Spaces are not allowed as part of a name. Use the underscore character (_) and period (.) as word separators; for example, Discount_Rate or First.Quarter.
  4. Name length:  A name can contain up to 255 characters.
  5. Case sensitivity:  Names can contain uppercase and lowercase letters. Excel does not distinguish between uppercase and lowercase characters in names.

Tell Me More

Create a Named Range using the Ribbon:

  1. On the Formulas tab, in the Defined Names group, click Define Name.
  2. In the New Name dialog box, in the Name box, type the name that you want to use for your reference. 

    MS Excel 2010 - Creatingnamedranges02 

  3. Setting Scope enables access to the Name throughout the Workbook or to a worksheet only.
  4. Enter optional Comments.
  5. In the Refers to box type or select the cell reference.

Editing a named range reference:

To edit the cell references of a named range or change its name use the Name Manager on the Formulas tab. This dialogue box provides the ability to create, edit and delete named ranges.


Tip

Note that you cannot edit or delete named ranges via the worksheet's name box: you must use the Name Manager. To find a named range quickly - regardless of which worksheet it's on - go to the Home tab, choose Find and select (binoculars) and click on Go To. A list of names appears: choose the one you want!

MS Excel 2010 - Creatingnamedranges03 

Let Me Try

Here is an exercise for you to try out Naming ranges for yourself.

MS Excel 2010 : Creating Named Ranges

If the "OPEN" option does not show you all the icons you'd expect in the application, use the "SAVE" option.

1aletmetry 


Help Me

NEW Mouse Head Wink 2
Remember to put your new skills into practice as soon as possible. Learn a little each day to improve your knowledge and skills.


 

 

My Bite Size Login

On-line Courses
Facebook Twitter LinkedIn Google+ YouTube