Abstract
Transcript
In this video we'll look at how to apply data validation to an Excel Table. We'll also look at how to use the values in a table for data validation.
This table contains a list of projects showing a sales pipeline.
The last column is meant to show the current state of a given opportunity.
To the right, in column G, is a list of possible stages.
To illustrate how data validation works with tables, I'll set up the last column of the table to enforce the values in column G, by providing a dropdown list.
To start off, I'll select the stage column, then click the data validation button on the Data tab of the ribbon.
Next I'll set the type to "List", and point to the values in column G.
After I click OK, the dropdown appears in the table.
And if I try to enter any value not on the list, Excel will display a warning.
If I add a new row to the table, it also picks up the data validation.
So this works pretty well.
However, notice that if I add a new value below existing stages, it doesn't show up in the dropdown list.
This is expected, because there is nothing dynamic about the current setup. Basically, I've hardcoded a reference to the values in column G, and this reference won't change.
You might wonder if we can create a second table for the stages, and then feed those values back to the data validation used in the first table?
Let's try it out.
First, I'll convert the list of stages to a table.
At this point, nothing changes. The data validation continues to work normally.
But notice if I add a new stage, it automatically appears in the dropdown.
If I check the data validation input window, you can see the range has been expanded automatically.
Generally, when a cell reference corresponds to a complete column in a table, Excel will update the reference when needed.
In this case, this means Excel will update the dropdown values to stay in sync with the second table.
Unfortunately, you can't use structured references directly in the data validation input window.
If you try to do this, Excel will complain.
To build a more transparent solution, you however can set up a named range that points to a table column, and then use the named range for data validation.
We'll look at how to do this in a separate video.
