Possibilities : Image Source - Pixabay.comIt can be so frustrating when you look over a database of information to find that how data was input can limit your ability to report on the data. This is because when the data is not consistent, a query will not pick up all the data. This is because Excel has no way of knowing that different words mean the same thing.

For example, test result on patients. One person could describe the result as high whereas another could describe it as elevated or excessive. These words all mean the same but to Excel they are very different data. The solution is to restrict what people can input into a cell to create consistency.

In this tips column I have previously covered Data Validation. In that tip I explained how to make sure people input the right date or number into your data sheet. Here I will explain how to give them a list to choose from, where they cannot input their own interpretation.

  • Begin with typing on a separate sheet, which if you like to you can later hide, a list of choices. Such as: – Yes, No, Maybe, Other. List of choices
  • Select those cells and name the range.
  • Go back to the main sheet where data will be inserted.
  • Select an area, usually a column, to set the validation rule.
  • Select the Data Validation tool from the Data Tab.

Data Validation Tool

  • Select “List” from the Allow drop down box.

Data Validation Dialog Box

  • Click in the Source box and use the F3 key to display all the names in your workbook.

Data Validation Setings for list

  • Select the validation list you created and click on OK twice to exit the Data Validation tool.

name of list

  • In your spreadsheet, click in the first cell you require the data to be input. You will notice a dropdown arrow to the right of this cell.
  • Select it and your list appears. You can select only from this list.

ResultNow you have tested that your validation rule works clear any unnecessary data and send the sheet off to the person who will be inputting the data for you. You can be sure that the data collected is correct. Happy data processing.



Please enter your comment!
Please enter your name here