Jun 282012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

One of the key benefits of Defined Names scoped to a worksheet is that the worksheet can be cloned and the Defined Names are also cloned. However, sometimes, you already have several worksheets for which you would like to define a Named Range, but doing it manually for each worksheet will be slow and tedious.

Fortunately, there is a clever trick you can use to get Excel to create these names for you automatically. However, each Named Range that Excel creates will refer to the same range on each worksheet so it will not work if this cannot be guaranteed. E.g. if you have a Totals row at the bottom of a list and the length of the list is not the same on each worksheet, then the Totals row will not be on the same row on each worksheet and this will not work. However, if the range that the Named Range refers to is the same on each Worksheet, then follow these steps to auto-create the names.

Step 1

Create the Named Range on a single worksheet. Ideally the first in the series of worksheets. Refer to my post on Defined Names 101: Creating and Managing for more information. Using the Name Box, you can simply select the range (if it is an entire row or column, then click on the row or column title) then click in the Name Box and enter the worksheet name followed by “!” followed by the name you with to create. E.g. if we wanted to name the first row on each worksheet as a Title Row we could enter this:

Sheet1!BBB_TitleRow1  (Refers To: Sheet1!$1:$1)

See my post on Defined Names: Naming Conventions (Part 2) to see why I added the BBB_. I also usually add a number to he end of the name for rows or columns like Titles as often one needs more than one title Row or Column.

Step 2

Enter a simple formula on Sheet1 that refers to the newly created name.  The easiest one is to enter the following formula on any row other than the first row (for our example):

= BBB_TitleRow1

If you enter it on the row it Refers To, then Excel will warn you that you have a circular reference (unless of course you have Iteration turned on). This is not an issue as the formula will be deleted in a later step, but it is annoying to get the warnings so it is best to use another row. The key is that you enter a formula on Sheet1 that references the new Named Range.

Step 3

Copy the formula. Just select the cell and choose copy. The formula cell will be animated to show it is in the process of being copied. The easiest way to copy is just to enter <Ctrl>C from the keyboard, or to right click the cell and choose Copy from the context menu.

Step 4

Select all the worksheets that you wish to create the names on. The easiest way is to select them from the worksheet tabs at the bottom of the window while holding down the <Ctrl> key. However, make sure that you click on the first worksheet you wish to create the names on before holding down the <Ctrl> key to avoid including the original worksheet in the collection.

If all the worksheets are consecutive, you can click the first one, then press the <Shift> key before clicking the last one and all the worksheets in between the two will be selected.

Step 5

Now, you simply find a cell that is not used on each of the selected worksheets and paste the copied cell  over it. It is critical that all these destination cells are empty as their contents will be replaced by the formula copied from Sheet1. Also, you don’t want to use a cell in the range that the copied formula Refers To, in order to avoid getting a warning for circular references.

If you have done this correctly, Excel will have pasted the formula to each of the selected worksheets and in doing so, it will have created Named Ranges for each worksheet referring to the same range as on the original worksheet, i.e. each Worksheet in the collection will now have an identical named Range scoped to itself.

Step 6

With the cell selected on the worksheet where you just pasted the formula, then hit the Delete key to delete the formula. You don’t want to keep it so it is best to  delete it immediately especially when all  the other worksheets are still selected, so as to ensure that you delete the formula from all the selected worksheets.

Step 7

Deselect the selected worksheets. The easiest way to do this is to click on a worksheet that is not part of the selection. I suggest the original worksheet, Sheet1 as it can not be part of the selection. If you click on another worksheet in the selection, then Excel will simply move the focus to that worksheet but it will not remove the collection of selected worksheets.

It is critical that you remove this collection of selected worksheets otherwise, most of what you do next will be applied to all the selected worksheets simultaneously.

Step 8

Click on a few of the worksheets that you created the Named Ranges in and click on the Name Box. You should see the name in the dropdown list. Select it and Excel will select the row you initially created a Named Range for. In our case, Row 1. In Excel 2007 or above, open up the Name Manager and you will see all the newly created names scoped to each of the worksheets you selected.

Jun 222012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010

Most of my Defined Names are scoped to the Workbook. This is the default scope when defining names in Excel. So why and when do we use Defined Names scoped to the worksheet?

One of the key benefits of using the worksheet scope is the ability to clone a worksheet together with all the worksheet Defined Names. This is ideal if you wish to create a number of similar or identically structured worksheets. For example, if you have a sales worksheet for each month of the year and each worksheet is very similar in structure and nature.

Issues with Cloning Worksheets

Cloning a worksheet is very quick and simple in Excel. However, this benefit can also create problems when you clone a worksheet which has workbook Defined Names (i.e. names referring to that worksheet but scoped to the workbook) . What happens is that Excel also clones the workbook Defined Names but converts them to worksheet Defined Names. The problem here is that most users do not realise that the workbook Defined Names have been cloned. As the newly cloned worksheet now has a worksheet Defined Name scoped to itself and the workbook also has a workbook Defined Name, the model will, most likely, not work properly. This is because Excel will prioritise the Defined Name scoped to the worksheet over a Defined Name scoped to the workbook, thus all references made to that Defined Name on the newly cloned worksheet will no longer reference the original Defined Name scoped to the workbook.

Excel 2007 Cloning Workbook Names – Before

Excel 2007 Cloning Workbook Names – After

The Defined names marked by the yellow background are scoped to the workbook (hence the AA_ prefix). The diagram on the right has the names marked by the orange background as these names are now scoped to the worksheet. This is done automatically by Excel when you clone the worksheet. Download the sample workbook here.

Excel 2007 Cloning Workbook Names – Name Manager

The Name Manager shown above displays the names in the Workbook. Note how there are two of each of the AA_ prefixed names, one scoped to the workbook and one scoped to the worksheet SAL_WorkbookNames_After. These duplicate worksheet names should be deleted.

Thus, the choice of scope is important when creating Defined Names. If you are creating worksheets that have a common trait and which you will want to clone, then use worksheet names. Otherwise use workbook names. This way you will avoid this problem.

Cloning Worksheets with Workbook Names

If however, you do need to clone a worksheet that has workbook names that refer to it, you should immediately check the list of Defined Names and delete all occurrences of worksheet names that were cloned from Defined Names scoped to the workbook.

If you have followed my suggested naming convention for Defined Names, then this is extremely easy because all workbook names will be prefixed with AA_ and all worksheet names will be prefixed with BB_. When you review the list of names in the Name Manager (or Defined Name dialogue box – Excel 2003), you will immediately see, grouped together, all the names that start with AA_ but which are scoped to the worksheet. These can easily be selected and deleted. Then there will be no risk of Excel using the wrong Defined Name.

If you have not used a naming convention, then you will have to go through the list manually and look for names that are scoped both to the workbook as well as the worksheet one by one.

Note: the use of a naming convention also makes it easy to use VBA code to remove invalid names when automatically cloning worksheets!

When to Use Worksheet Names

There are two cases when I would recommend using worksheet names.

Case 1: Common Worksheet Structure

Lets say we have a model that has common characteristics on many of the worksheets which will be referenced by formulas on those worksheets. i.e.

Formulas on Sheet1 referencing Defined Names on Sheet1
Formulas on Sheet2 referencing Defined Names on Sheet2
Formulas on Sheet3 referencing Defined Names on Sheet3

Then it makes sense to use worksheet names. Examples of such names would be:

  • Titles (often showing months, quarters or years)
  • Column Labels (usually on the left and may include several levels)
  • Standard Helper Columns or Rows (perhaps a row to calculate the year or month number or a column used for validation purposes
  • Columns or Rows used by Conditional Formatting or Data validation formulas

In all of these cases, these structural names should be referenced only by formulas on the worksheet to which they are scoped. The key benefit to using Defined Names scoped to the worksheet is that the worksheet can be cloned and all the formulas will continue to work without modification as the Defined Names are cloned with the worksheet.

When I create names for a structural purpose as described above, I usually prefix them with a BBB_ rather than the BB_. This is a way of differentiating them from the worksheet names described under Case 2 below. I can then also use these Defined Names easily in VBA code.

Case 2: Common Data Descriptors

Lets say we have a model that contains twelve worksheets representing sales figures by month. Each worksheet contains a column for each of the following:

  • Country
  • Currency
  • Category
  • Qty

If we had just an Annual Sales Summary sheet, I would create a Named Range scoped to the workbook for each for these columns. However, with twelve monthly worksheets, I would have to create Named Ranges for each column for each of the twelve worksheets (by prefixing the Defined Name with part of the worksheet name, each name would then be unique). This would require the creation of 48 names which is not only time consuming in the first instance, but will also makes it much harder to maintain the model. What if we had 10 such columns to name?

The solution is to use Named Ranges scoped to the worksheet. This way we can create just one worksheet and set it up the way we want, then simply clone it to create the other eleven month and name them appropriately. See my post Worksheets: Naming Conventions

These Named Ranges are not part of the structure of all worksheets in the model but are specific to the data contained on these worksheets. Thus I would prefix them with the simple BB_ to show that they are worksheet names but not part of the common worksheet structure. Download the sample workbook here.

Excel 2007 Defined Names – Worksheet Named Ranges Example

Using Worksheet Names

When using a workbook Defined Name in a formula, you simply use the name exactly as it has been defined. If you use a worksheet Defined Name on the same worksheet that it is scoped to, then it also can also be used exactly as defined. However, if you use a Defined Name in a formula that is scoped to a worksheet other than the one that it is scoped to, then you need to prefix the name with the worksheet name. i.e.

Name Scope Formula on Sheet1
AA_TotalRevenue Workbook AA_TotalRevenue
BB_TotalRevenue Sheet1 BB_TotalRevenue
BB_TotalRevenue Sheet2 Sheet1!BB_TotalRevenue

However, again, when cloning worksheets, it can be problematic if you use names on one worksheet that are scoped to another. Because of this, I always avoid using any worksheet name on a worksheet other than the one to which it is scoped.

The main issue with using worksheet names on a worksheet that the name is not scoped to is that it can get confusing and messy and hard to manage and control. That is what workbook names are for.

Downloads Used:
Defined Names Cloning Sample Workbook
Defined Names Scope Sample Workbook

Jun 192012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 Excel workbooks contain individual worksheets and the number of worksheets in a workbook is limited only by the system memory of your computer. This article is all about the names you can give to a worksheet. Naming Syntax By default, a new workbook contains 3 worksheets and they are named; Sheet1, Sheet2, Sheet3. You can add new worksheets and […]

Continue »

Jun 102012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 This is part 2 of a 2 part post. Continued From Defined Names: Naming Conventions (Part 1) Grouping When implementing a naming convention, one has to consider a number of issues pertaining to the logic of what is being named and the environment in which the naming convention applies. As Defined Names are usually presented in an alphabetical list, it […]

Continue »

Jun 052012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 I have covered the basics of Defined Names in a series of three Posts named Defined Names 101. However, in order to use them effectively, you really need to use a strict naming convention for the following reasons: It makes it easier to manage large numbers of names. It separates the names you define from the names that Excel […]

Continue »

Jun 032012
Defined Names 101: Using Named Ranges

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 This is part 3 of a 3 part post. Continued From Defined Names 101: Creating and Managing So now you know what Named ranges are for, how to create, modify and manage them. Now we need to look at how to use them. This post will cover using them for navigation and how to enter them in formulas. This will […]

Continue »

May 312012
Defined Names 101: Creating and Managing

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 This is part 2 of a 3 part post. Continued From Defined Names 101: What Are Defined Names? Creating Named Ranges There are two main ways to create Named Ranges on an individual basis. The first way: the Quick Method Select the Cell or Range you want to name. Click on the Name box (to the left of the Formula […]

Continue »

May 292012

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 This is the first part of a 3 part post A Defined Name is a way of adding a meaningful descriptor to any of the following: A Cell A Range of Cells (including an entire row or entire column) A Constant (i.e. a number like 8.3) A Formula First Name Last Name Email Address Cell =C1 =TaxRate Range of […]

Continue »

May 252012
Excel Intersection 101: Explicit Intersection

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 This is part 2 of a 2 part post. Continued From Excel Intersection 101: Implicit Intersection As opposed to using Implicit Intersection, you can also use the intersection operator to force an Intersection between rows and columns. This intersection is called Explicit Intersection. The Intersection operator is a “Space”. First we will look as some simple ways to use it. […]

Continue »

May 242012
Excel Intersection 101: Implicit Intersection

This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010 Excel has a little known but very powerful feature that can help users to build much simpler and easier to read formulas and, if used correctly, can also help to make models much easier to modify without breaking  references or referencing the wrong cells. The key to this is Implicit Intersection. First we will look as some simple ways […]

Continue »