29

I'd like to setup a column in excel with several fixed possible values (like 'in-progress', 'done', 'canceled').

I'd like to set it up so the only possible values in these column are from the fixed list, and it would be easy to pick one of these items whenever I try to edit a cell in this column (a drop-down box would be ideal). How can I do that?

4 Answers 4

38

Piece of cake.

1) Enter your list of the fixed values. (These have to be in the same sheet as the cell you want to restrict)

2) Click on the cell you want to restrict. Select "validation" from the Excel "Data" pull down menu

3) In the pull down on the "Settings" tab select "List"

4) click In the box labeled "Source" then select the cells that contain the values set up in step 1)

5) Click OK and you are done.

You can do this for any number of cells

You will see a pull down icon next to the restricted cell(s). Click on it to choose only values in the list. Excel will not allow any other value in this cell.

Look around in the Validation dialog box and you will see you can tweak how this works.

1
  • is there a way to convert existing values in a cell to the restricted list that you want to impose upon them? I am trying to tidy up an existing Excel sheet with lots of data.
    – rnoodle
    Mar 28, 2020 at 14:50
10

Rather than selecting a buch of cells with the values in them you can write the values in as a semicolon seperated list in the source box instead.

3
  • 1
    Great! But should be a comment of the accepted answer, as it doesn't answer the question. Oct 27, 2014 at 9:34
  • 1
    Know this is an old thread - but if anyone else finds it and is using Office for Mac - it's a comma not a semicolon if using the source box
    – steve
    Jan 6, 2015 at 11:32
  • Also, this is not a best practice. From the Excel docs: "Why should you put your data in a table? When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. " If you enter it directly into the source, then it makes modifying the list more tedious.
    – papiro
    Mar 1, 2020 at 20:15
6

Also the restriction in step one of the first answer "(These have to be in the same sheet as the cell you want to restrict)" is not true.

If fact it is good practive, especially if you have quite a few of this lookup list in use to put them all on a LookUps tab and then hide the tab so that general users cannot see it & fiddle with it.

To get round the restriction mentioned you must set up a Named Range to identify the list of allowed entry items. Because the Named Range is recognised throughout the entire workbook you can then use this name to specificy the list in any other tab you like. In the Source box you type an = sign immediately followed by the name you chose for the Named Range

2
  • 1
    This is not an answer to the original question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post.
    – DavidPostill
    Jan 12, 2015 at 16:31
  • To use a different tab, prefix the range with: YourTabName!
    – fig
    Oct 4, 2023 at 14:53
3

Official documentation

Microsoft has a nice and short tutorial on this topic directly on their support site (as video and as text with some screenshots):

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .