Excel-User.com

You can use named ranges on your sheets for lots of things. We will see some good examples here on how you can use them. You can name a cell or a range of cells. You can use the name box near the formula bar to insert names on your sheet, just select the cell(s) and type the name you want on the name box, like this:

This will give cell B5 the name “MonthPayment”. Names cannot have blank spaces, special characters, and operators, and can be up to 255 characters long. In this case, to avoid using spaces, I joined the two words but I could use Month_Payment for instance. Now I can just type like this on a cell:

=MonthPayment

and this will display the value I’ve on cell B5. You can use this named ranges to simplify your formulas. For instance, you can now build a formula like this, if you also give cell B3 the name “Term”:

=MonthPayment*Term

As it was explained on the Cascading Validation Lists article, you can also create named ranges using the Name Manager dialog box.

You can use the Named Ranges to navigate through your sheets. Just select the name on the names box on your formula bar and you will jump to corresponding cell(s). This is very useful if you have big and complex worksheets/workbooks.

As said before, you can name multiple cells, not just a cell. Let’s see an example on how to do this and how useful this can be. In this sheet, I selected cells B2:B4 and putted a name, on the name box, of JanSales:

Now, I can put a formula on my sheet, for instance on cell B5, just like this:

=SUM(JanSales)

and it will give me the sum of the January sales. It’s easier to read the formulas If you use named ranges!

You can create many named cells at once. For instance, with this sheet, just select cells A1:B9 and, on the Formulas tab, on the Defined Names group, click on the “Create from Selection” option.

This will open the “Create Names from Selection” dialog box where the “Left column” option is already checked:

In this case, the names that we want to use for our cells are on the left of the values. If they were on the top, with the values below, we would choose “Top row”. Clck the OK button and you will now have 5 names on your name box, one for each value on column B. Excel replaced the blank space by an underscore so that the names can be valid.

You can manage your names on the Formulas tab, by clicking the Name Manager button. This will open the Name Manager dialog box where you can check the names that you have created, it’s values, the range to where it refers, the scope (either is for the whole workbook or it’s particular to one sheet) and also the comment that you can put to help you identify it. You have also a Filter button that you can use to filter the created names, for instance, if you just want to see the names build for the active worksheet.

Names can also be used to assign a constant value. Go to Data tab, click on the Name Manager button and on the Name Manager window click on the New button to create a new name. Then create a new name for IRS_Tax and on the “Refers to” field, put a 23% tax value, like this:

Now you can use this constant value on this IRS_Tax name on your formula, like this:

=100*IRS_Tax

You can created dynamic named ranges. This is very useful if you have a column with data and this column can receive more inputs. This way, you don’t need to edit your named range to the new range, it will automatically adjust it self. Let’s see an example using the OFFSET() function of Excel. We have a list of clients on column A, like this:

We want to create a dynamic range with the list of clients so that if we had another name on cell A12, the list will expand and consider also this new client name. We will create a new name and on the “Refers to” field, we will put and OFFSET formula, like the one on this image:

After you click OK button, if you click, on the Name Manager window, on the “Refers to” field, you will see that the range A1:A11 is select (with the “marching ants”). Now write a new client name on cell A12 and go to the Name Manager window again to see what range is selected. Is A1:A12, right? As you can see, this is a useful way of creating dynamic Validation Lists.

Finally, you can use Named Ranges to help you speed data entry on your sheets! We have seen that you can create named ranges referring to multiple cells. They don’t need to be contiguous cells. Let’s see a vey simple example on how we can apply Named Ranges to helps us speed data entry. We have this simple form:

We just need to insert data on the yellow cells, starting on cell B1, then B5 and finally cell B9. If we want to select a non contiguous set of cells, we just click on the first, then hold down Ctrl key and click on the rest of the cells. If you do this on this three cells, want the name box. It is showing B9 as our active cell. If you put a name for this range on the name box, and then select him on the name box, you will see that your starting input cell will be B9, then it goes to B1 and finally to B5. This is not the order we want! The trick here is to start clicking  on our second cell (B5), then B9 and end on the cell that we want to be our first input cell, in this case B1. This is our active cell so this is the one where we should put the name on the name box. So, put for instance, NewOrderInput and press Enter.Now select that name on the name box. You will get all of the three cells selected, starting on B1. Now enter some some value and press Enter. You will see your active cell move from B1 to B5. Repeat the same and you will see it move to cell B9. This is a great way of using Named Ranges to help you improve your productivity when it comes to input data into an Excel sheet.

Here’s the scenario: on our worksheet, we want to choose a Country and the corresponding City. We want to limit the countries available to the user as well as the cities that they can choose. For start, we want to choose the country and on the second cell we can only put cities that correspond to that country. It makes sense, right?
So, let’s start we our simple example on how we can make cascading (or dependent) Validation Lists. In cell A2 we want to put the country and on cell B2 we want to put the city, like this example:

To start, we have to build our “database”. On a second sheet, I’ve made a list of countries that I want to have available for the user to choose on my validation list and I putted and index number that will correspond to the right cities list. So, for instance, for France, I used the index 2 that will be corresponding to the cities list 2. You will see after how this index number will be used.

Now, if you’re using Excel 2010, you can have the “source” lists on a separated sheet but if you’re using Excel 2003 for instance, you can only have the values on a second sheet if you use named ranges. We will use the name ranges so that this cascading validation lists work on both versions. So, the next step is to create the named ranges for our lists. On your Formulas tab, we have the Name Manager button. When you click it, it will open the Name Manager dialog box.

Click on the New button to add a new named range and it will open the New Name dialog box.
 

On the Name field you can put, for instance, “Country” for our first named range and on the “Refers to” field we will select, in this case, =Sheet3!$A$3:$A$5 to get the list of countries. Click OK to finish. Then we need to create the named ranges for the rest of the lists. Click again on the New button on the Name Manager dialog box and repeat the process to create three more named ranges: England, France and Portugal, selecting for the “Refers to” field the corresponding range of cities names. This will leaves with 4 named ranges, like this:

Now we can build our validations lists on our original sheet where we want to select the data from our “database”. With cell B1 selected (where we want to select our country), go to the Data tab and click on Data Validation and choose Data Validation.

This will open the Data Validation dialog box.

Now we want to use a list of options for the user to select so go ahead and on “Allow” change the value to “List”. This will open the Source field that is where we will put our Country named range. So put =Country and click OK. Now we want to make the second Validation List for the user to choose the city. Select, in this case, cell B2 that is where we will put this second validation list. Now comes the trick. We need to check the value of the country cell (cell B1) to get the index of the corresponding value on our “database”. If the user selected Portugal we need to get the index “3” that will tell us to get the list of cities that correspond to Portugal. For that we will make a VLOOKUP of the value in cell B1 in the Countries table. After we get the index value, we will “choose” the list to use for the Source of our second validation list. To make this we will use this formula:

=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)

So, what does this formula do? It will start for looking up the value on cell B1 against the values on our countries table and getting the corresponding index that it will use on the CHOOSE() function to get either the 1st, 2nd or 3rd named range.

This is how our second validation list will look:

The result will be two cascading or dependent Validation Lists. If we choose country France, we will have on the cities Validation List the cities from France only.

This article demonstrates how you can make basic cascading Validation Lists to use on your sheets. You can take this example and use it on your own real life situations.