Excel spreadsheets often include drop-down lists of cells to simplify and / or standardize data entry. These drop-down lists are created using data validation to specify a list of valid entries.
To set up a simple dropdown list, select the cell where you want to enter data, then click Data Validation (on the Data tab), select Data Validation, select List (under Allow :), and then enter the list items (separated by commas.) in the Source: field (see Figure 1).
In this type of basic drop-down list, the list of valid entries is specified in the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation. However, this can be difficult for inexperienced users or in cases where the list of options is too long.
Another option is to put the list in a named range in a spreadsheet, and then specify that range name (with an equal sign) in the Source: field of the data validation (as shown in Figure 2).
– /
This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic. Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($ H $ 3: $ H $ 10, as shown), if more choices are added in cells H11 or below, they will not appear in the dropdown. (since these cells are not in the FruitChoices range).
Likewise, if, for example, the Pears and Strawberry items are removed, they will no longer appear in the dropdown, but instead the dropdown will include two “blank†choices, since the dropdown still references the entire range of FruitChoices including empty cells H9 and H10.
For these reasons, when using a regular named range as a list source for a dropdown list, the named range itself must be edited to include more or fewer cells if records are added or removed from the list.
The solution to this problem is to use the dynamic range name as the source for the dropdown. A dynamic range name is a name that automatically expands (or shrinks) to exactly fit the size of the data block when records are added or removed. To do this, a formula is used to define a named range, rather than a fixed range of cell addresses.
How to set up a dynamic range in Excel
A regular (static) range name refers to the specified range of cells ($ H $ 3: $ H $ 10 in our example, see below):
But the dynamic range is defined using a formula (see below, taken from a separate spreadsheet that uses dynamic range names):
Before we start, make sure you have downloaded our sample Excel file (sorting macros disabled).
Let’s take a closer look at this formula. The Fruit selections are in the block of cells directly below the heading (FRUIT). This heading is also named: FruitsHeading:
The complete formula used to determine the dynamic range for the Fruits selection is as follows:
= OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1,0,20,1 )), 0,0), 0) -1,20), 1)
FruitsHeading refers to the heading one line above the first entry in the list. The number 20 (used twice in the formula) is the maximum size (number of lines) for the list (it can be changed as desired).
Note that in this example there are only 8 entries in the list, but there are also empty cells below them where you can add additional entries. The number 20 refers to the entire block in which records can be made, not the actual number of records.
Now let’s take a breakdown of the formula (denoting each part with a color) to understand how it works:
= OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1), 0,20,1)), 0,0), 0) -1,20), 1)
The “innermost” element is OFFSET (FruitsHeading, 1,0,20,1). This refers to a block of 20 cells (below the FruitsHeading cell) where you can enter variations. This OFFSET function basically says: start at the FruitsHeading cell, go down 1 row and more than 0 columns, then select an area 20 rows long and 1 column wide. Thus, we get a block of 20 lines in which the fruit selection options are entered.
The next part of the formula is the ISBLANK function:
= OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (the above), 0,0), 0) -1,20), 1)
Here the OFFSET function (explained above) has been replaced with the “above” function (for easier reading). But the ISBLANK function works with a 20-line range of cells that the OFFSET function defines.
ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20 row range referenced by the OFFSET function is empty (empty) or not. In this example, the first 8 values ??in the set will be FALSE because the first 8 cells are not empty and the last 12 values ??will be TRUE.
The next part of the formula is the INDEX function:
= OFFSET (FruitsHeading, 1.0, IFERROR (SEARCH (TRUE, INDEX (above, 0.0), 0) -1.20), 1)
Again, the “above†refers to the ISBLANK and OFFSET features described above. The INDEX function returns an array containing 20 TRUE / FALSE values ??generated by the ISBLANK function.
INDEX is typically used to select a specific value (or range of values) from a block of data by specifying a specific row and column (in that block). But setting the row and column inputs to zero (as done here) causes INDEX to return an array containing the entire data block.
The next part of the formula is the MATCH function:
= OFFSET (FruitsHeading, 1.0, IFERROR (MATCH (TRUE, above, 0) -1.20), 1)
The MATCH function returns the position of the first TRUE value in the array returned by the INDEX function. Since the first 8 entries in the list are not empty, the first 8 values ??in the array will be FALSE and the ninth value will be TRUE (since the 9 th line in the range is empty).
So the MATCH function will return 9. In this case, however, we really want to know how many records are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last record). So MATCH (TRUE, above, 0) -1 returns 8.
The next part of the formula is the IFERROR function:
= OFFSET (FruitsHeading, 1,0, IFERROR (above, 20), 1)
IFERROR returns an alternate value if the first specified value results in an error. This feature is enabled because if the entire block of cells (all 20 rows) is filled with records, the MATCH function will return an error.
This is because we tell the MATCH function to look for the first TRUE value (in the array of values ??from the ISBLANK function), but if none of the cells are empty, then the entire array will be filled with FALSE values. If MATCH cannot find the target value (TRUE) in the array being searched, an error is returned.
Thus, if the entire list is full (and therefore SEARCH returns an error), IFERROR will instead return 20 (knowing that there should be 20 entries in the list).
Finally, OFFSET (FruitsHeading, 1.0, above, 1) returns the range we’re really looking for: start at the FruitsHeading cell, go down 1 row and more than 0 columns, then select an area that contains as many rows as there are records in the list (and 1 column wide). Thus, the whole formula together will return a range containing only the actual records (up to the first blank cell).
Using this formula to define the range that is the source for the dropdown means that you can freely edit the list (adding or removing records if the rest of the records start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).
The sample file (dynamic lists) used here is included and available for download from this website. However, macros don’t work because WordPress doesn’t like Excel workbooks with macros in them.
As an alternative to specifying the number of lines in a list box, the list box can be given its own range name, which can then be used in a modified formula. The example file uses this method in the second list (Names). Here the whole list box (under the heading “NAMES”, 40 lines in the example file) is assigned the name of the range NameBlock. An alternative formula for defining NamesList is as follows:
= OFFSET (NamesHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (NamesBlock), 0,0), 0) -1, ROWS (NamesBlock)), 1 )
where NamesBlock replaces OFFSET (FruitsHeading, 1,0,20,1) and ROWS (NamesBlock) replaces 20 (number of rows) in the previous formula.
So, for dropdowns that are easy to edit (including by other users who might be inexperienced), try using dynamic range names! And note that while this article has focused on dropdowns, dynamic range names can be used wherever you need to refer to a range or a list that can vary in size. Enjoy!
–