Named Ranges (★)

Named Ranges (★)

In Excel, you can allocate a name to a cell or group of cells (range). This article describes how and why using named ranges in your spreadsheets.

How to name a range ?

There are several ways to name a range in Excel, I will focus on 3 of them.

  •  You can pre-select the cell(s) and then name them straight in the Name box

Name box

  • You can use the “Defined names” section in the “Formulas” tab

Upper tab real

  • You can press Ctrl F3 to open the name manager from which you can create your name

Name manager

Monitoring your named ranges

The name manager allows you to monitor, create, delete & edit the named ranges in your workbook.  Each named range has a value, an address (“refers to”), a scope (whole workbook or single worksheet) and a possible related comment (made by the creator of the name for clarity purposes).

The default scope of a named range is “workbook”. It means that you can use your named range in all the worksheets of your workbook. However, if (for example) you only want this name to be used in the worksheet where it has been created, you can restrict its scope to this worksheet when creating it.

Note that if you create a named range with the method 1 above (Name box), the workbook scope will be automatically applied. And once created, you can not modify the scope of a named range…

Why using named ranges ?

There are many reasons why you should use named ranges in your spreadsheets.

   1. Formulas are clearer and easier to read

These formulas  = Op_Inputs!$B$2 * Op_Inputs!$B$3 * (1-Op_Inputs!$B$5)

= Price * Quantity * (1-Flex)    

give the same answer. Which one do you prefer ?

You got the answer ! Named ranges, as long as they are not too long and easy to understand, can make your formulas easier to read or edit. It is especially convenient when someone else is reviewing your model and trying to understand its logic.

   2. It can make your spreadsheet safer

When you refer to a named range in a formula, it is automatically anchored. You don’t have to pay attention to the $ (press f4) to fix the row  and/or the column of your formula. Your named range is fixed and you can peacefully drag your formulas.

In addition, named ranges are particularly useful when you have to convert your data into millions or billions. Instead of hardcoding your 0 in your formula (=E9/1000000), triple-checking that you put the correct number of 0, you can use a named range “Million” or “Billion”.

   3. It is convenient

A named range with a workbook scope – nearly 98% of your named ranges – is available in the whole workbook. You just have to type in its first letters and it will be suggested in your formula (Excel 2007+). No need to go to your inputs sheet and call up the correct cell.

In addition, you can use your named ranges as sources of your graphs, pivotTables or drop-down lists.

Good to know

There are several restrictions when naming a range in Excel :

  • The first character of the name must be a letter, an underscore character (_), or a       backslash (\).
  • Cell references cannot be used (A5, C18…) in the name
  • No spaces are allowed in the name. Use “_” instead
  • The length of your name should be 1 – 255 characters

For clarity purposes, try using short and easy-to-understand names.

 Small tip : if you press F3 within a formula or when attributing a source to your graphs- drop-down lists, all the named ranges of your workbook will be displayed.

–  You can also name your shapes (graphs, pictures…). This is particularly handy when you want to interact with them in VBA or connect slicers for your dashboards.

–  You can make your named ranges dynamic. We will see how in the next article.

And you, do you like using named ranges ?

 

To subscribe to the blog and keep up with the updates, press the “+” button at the bottom right of your screen!

Feel free to share, like or comment below.

★ Beginner    ★★ Intermediary     ★★★ Advanced

 

 

Advertisements

4 thoughts on “Named Ranges (★)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s