Data Validation Lists

A Data Validation drop down list allows Excel users to restrict data entry to the values they specify.  This gives the Excel user full control over the content entered in cells and allows for greater functionality when filtering and sorting data.

Creating a Data Validation Drop Down List

When creating a Data Validation drop down list, the source data must be created and remain in the same spreadsheet in which you wish to use it.  In this example, our source data is in Column A.

To create a Data Validation list:

  1. Open the spreadsheet that contains the source data for the list or type the source data for the list into your spreadsheet.  For this tutorial we have used a list of Canadian cities.
  2. Highlight the cells that you wish to apply the Data Validation to.  For this example, we have highlighted Column H.
  3. Select the Data Tab
  4. Select the Data Tools Group
  5. Click the Data Validation Button


  6. The Data Validation dialog box will launch
  7. Select the Settings Tab
  8. Under the “Allow”section of the dialog box, select the List option
  9. Click the “Source” section of the dialog box
  10. Select the range of data that you wish to use from the spreadsheet


  11. Select the Input Message tab
  12. Type an Input Message for the Data Validation you are creating.  It is best practice to use the Input Message to provide information on how the form should be filled out.


  13. Click the OK button when done.
  14. The Data Validation has been applied to Column H.  You should see the input message beside each cell in Column H.  You can now hide Column A in your spreadsheet.  This will hide the source data and make the spreadsheet less confusing to fill out.


  15. Highlight Column A.
  16. Right Click with your mouse.
  17. Select “Hide” from the contextual menu to hide Column A.




Entering Data Validation from a List

  1. Click the Data Validation arrow next to the cell in which the validation has been applied.
  2. The Data Validation list you have created will “drop down”.


  3. Click the desired option with your mouse.

Clearing Data Validation from a Spreadsheet

  1. Select the range of data that the Data Validation has been applied to.
  2. Select the Data Tab
  3. Select the Data Tools Group
  4. Click the Data Validation button

  5. Click the “Clear All” button in the bottom left hand corner of the Data Validation dialog box.
  6. Click the OK button when done.
"Hawthorn Training delivers expert and up to date software training solutions for your business."