One problem with tables is that you can’t use structured references directly to create data validation or conditional formatting rules. However, as a workaround, you can define named a named range that points to a table, and then use the named range for data validation or conditional formatting. To illustrate, here I have a list of projects representing a sales pipeline. The last column in the table uses data validation to provide a dropdown list of allowed values. Although these values come directly from a table named “stages”, Excel displays only a regular cell reference in the data validation window. If I try to use a structured reference to define the data validation range, Excel complains. One solution is to define a named range using a structured reference, then use the named range for data validation. To keep things straight, I’ll first rename the table to start with a “t” for table. This will let me see at a glance which range is the table. Next, I’ll create a named range called “Stages” using the name manager. In the name manager, I’m free to use a structured reference. When I select the values in the table, Excel automatically fills in the structured reference. On the worksheet, I’ll now see the stages table, and the named range I created with a structured reference Back in the data validation input area, notice that if I re-select the same range, Excel still doesn’t use a name. However, if I enter the named range manually, Excel will happily accept it when I click OK. And if I check the data validation rule again, the name will still be there. The benefit of this two-step approach is that the data validation rule is easier to understand, since it’s clear that values come from a specially named range. And, if we check that name in the name manager, it’s clear the values are provided by the column called “stages” in the table.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
