MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Excel Data Validation List

with 4 comments

A second Excel 2011 question in as many days, February is off and running. The question posed: “How do you create a data validation list without referring to a range of cells?”

You can create a data validation list without referring to a cell range. There’s a small trick to it. You enter the list of values as comma delimited strings, but you don’t enclose them in double quotes. Excel reads a set of literal text strings as if they’re a list of cell values, which is why you don’t provide the double quotes that you use in every function.

The desired list was either TRUE or FALSE, and the required behavior was that they function like Boolean variables. Fortunately, that’s straightforward when you remember that the case insensitive text strings TRUE and FALSE are Boolean values.

You create a data validation list by navigating to the Data Tab (Windows Excel 2010 it’s a Ribbon), and choose Validate. You’ll see the following list. Click on Data Validation… in the list, as shown below.

The selection launches the Data Validation dialog box. Choose List from the Allow poplist. Then, enter TRUE,FALSE in the Source entry field, like this:

Now click OK and you have a poplist. Hope this answer the question for others too.

Written by maclochlainn

February 2nd, 2011 at 9:30 pm