We have an Excel sheet with a filter on a specific column. The values in the filter for this column are "Purchase" and "collection".

I want my users to be able to choose 1 of these 2 options in this column when they create a new row. Sort of like a little checklist of options.

This will combat typo's and other issues.

Does anyone know how to do this?



First Method

You can make cells as dropdown list with your answers.

  1. Select the column.
  2. Go to Data -> Data Validation -> Data Validation...
  3. Under Settings go to Allow and select List from the dropdown.
  4. Now go to Source and insert your options with ; between them.
  5. You can go further to Error alert tab and set an error message in case people insert other words.
  6. Click OK and you're done.

Second Method (from Atzmon)

  1. In a secondary sheet put your dropdown options.
  2. Go back to your primary sheet and repeat points 1-3 from first method.
  3. At Source click on the select cells icon from the right and select the cells containing your options from the secondary sheet.
  4. Set as well the Error alert options and click OK. Done!

!This way you can edit your answers in the future(from the secondary sheet), without having to do this process again.

!You can hide the secondary sheet with the options, so that users can't see it.

enter image description here

  • You can also create a list of values (for example in a hidden sheet) and refer to that list in the Source field. This way, if you want to add, edit or remove valid values, you only need to change the list, not the validated cells. – Atzmon Dec 16 '16 at 11:08
  • @Atzmon True! I will edit my post with this method as well. :) – User552853 Dec 16 '16 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.