Ever wanted the users of your spreadsheets to fill in cells using only a certain number of options.  Say you had a list of States… maybe numbers from your General Ledger… or Product Categories.  Wouldn’t it be fantastic, instead of making people remember the correct names (and spelling) of each… that we could just give them a nice little list of options to pick from to populate the cell?

Well in Excel 2007, that is really easy!  Here is how to go about it.

Using Data Validation to create look up lists in Excel 2007

There are two ways to go about this… the simple way, if you only have a few options… or the less simple way

The Simple Way 

1) Click on the cell you want the look up / drop down list to appear in

2) Go to the “Data” tab in the Excel 2007 ribbon

3) Look for the “Data Tools” group

4) Click on “Data Validation”

5) In the box that appears, on the “Settings” tab, set the Validation criteria to allow “List”

6) In the “Source” text box, type in the options you want to make available (and remember to separate them with a comma!)

7) Click ok

Check out how unreal your drop down box in Excel 2007 looks!  And go tell your friends how easy it was!!!!

The Less Simple Way

I call this less simple simply because it is still pretty simple.  This method is best if you will have a long list of options that might already exist in your spreadsheet, or a list that you would want to regularly update.

Basically all you need to do is follow “The Simple Way”, and when you get to step 6, instead of typing in the options you want to make available, simply select the cells in your spreadsheet that contain the values.

So there you have it – drop down boxes… look up lists… whatever you call them… made easy in Excel 2007.

’till next time!
TNP