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.
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.
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):
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.
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.
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.
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.
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.
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.
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.