Create a list box in Excel to ensure valid data entry

I’ve been pulling together a spreadsheet for tracking and planning the expenses of my upcoming wedding and honeymoon. Having not used Excel for a while, I was a bit rusty. Anyway, what I wanted to do was have a simple list-box in a cell from which I could select ‘Yes’ or ‘No’ (I know, not earth-shattering at all).

So, I had a quick Google and found a plethora of tutorials on how to add a combo-box or a list-box; but, they all got way too messy and created a box that sat somewhere, and referenced a list of values before returning the index of that value, and then you could use that index to read the original list of values and select the correct one in order to use it. Phew, that was tiring just typing it!! Not a happy camper, but I knew there must be a simple way to have it “just work”.

I got there in the end, after some muddling around, and here’s the solution using Data Validation (this just does ‘Yes’ or ‘No’, but it’s easy to add more values):

  1. Select all the cells you want to have this validation applied to
  2. Go to Data | Data Validation
  3. On the Settings tab, select List from the Allow drop-down list
  4. In the Source text box, enter the valid values for the data, separated by commas (eg: Yes, No) [note: don’t use quote marks]
  5. Select the In-cell Dropdown check box
  6. Click OK

Done!
When users move to one of the selected cells, Excel displays a drop-down arrow. Clicking the arrow reveals a list of valid options which users can select for that cell. If users attempt to type an entry that’s not on the list, Excel will display an error message.

Advertisements

About Cameron
I'm a final year Computer Science/Information Systems major. Already finished my BA in Politics/Philosophy. I do web and software freelance on the side, while I finish studying. Hoping to be self-employed by the end of my degree, otherwise off into the real-world I go....

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: