Do you collect data in Excel spreadsheets? Well, if you do, you are not alone. And for some situations, it is an Excel-lent (pun intended) tool for collecting data.
Excel is even better when you know a few quick tips that make it easier to collect the data you want and harder to collect the data you don’t want (yes, we have all been there).
Here are brief instructions about how to use the data validation feature in Excel.
What is the data validation feature?
This feature in Excel lets you limit the values or types of data that can be entered into a particular row, column, or cell. This often makes it easier to enter data into Excel spreadsheets and makes the data more useful because it’s more consistent and accurate.
This feature is available as part of the regular Excel software, no add-ons required (I’m not good with add-ons!).
Validating calendar dates
Click on the cell or highlight the column where you want people to only enter a date field. Go to the Data tab and then select “Data Validation.” You should now see this Data Validation box:
Click the arrow on the right side of the field that says “Allow:” and “Any value” by default.
Select Date from the dropdown list. Several other fields will pop up after you select Date. If the field is for the date of service, you might want to limit it to dates after the start of your program. If your program started July 1, 2018, you would set the following values:
Click OK. Try to enter a date prior to 7/1/2018 in that cell and you should get an error message.
If you did not highlight the whole column, it’s easy to copy the validation to other cells. Use keystrokes or right click to ‘Copy’ the cell with the validation. Then highlight the whole column, select ‘Paste Special’ and select ‘Validation’ under Paste. Try to enter text or a date outside the range you set in any cell in this column, and you will receive an error message.
Make a Dropdown List
This might be my favorite validation type because it looks so official! Click on a different cell or column and pull up the data validation screen again (you are building connections between your brain cells by remembering how to do this new thing).
Instead of selecting ‘Date’, select ‘List’ in the ‘Allow:’ field.
There are two ways to create your list: 1) type your list in the ‘Source’ field, or 2) type your list in a different worksheet and refer to it in the ‘Source’ field.
The first way is fine to use if it’s not a long list. Put a comma and space between each thing in your list like this:
After clicking ‘OK’, you will see a dropdown arrow in the box you added the validation too. Click on that arrow and you will see your list (doesn’t it look fancy?). Copy and paste the validation to the rest of the column the same way you did above.
Typing your list in a different worksheet is slightly more difficult but worth it if you have a longer list. I usually label the new worksheet ‘Lists’ (you could be more creative if you want to). Type your list in a column in the new worksheet like this:
After typing the list, pull up the ‘Data Validation’ box and select ‘List.’ Click the icon on the far right of the ‘Source:’ field.
Don’t be alarmed – the box shrinks quickly and it’s not clear what to do next. Simply avoid panicking, click on your ‘Lists’ worksheet, highlight your list (don’t include the list heading if you included one), and click Return.
This brings you back to the full ‘Data Validation’ box. Click OK and you will see your dropdown list once again! Again, you can Copy the cell and Paste Special > Validation to copy the validation to the rest of the column.
Action Idea: Add Validation to a Spreadsheet
Pick a spreadsheet, any spreadsheet. If you are entering data in Excel, you should use the data validation feature. Pick one of your spreadsheets and add validation. You should either create a new column for future data to be
Take-home point
Excel can be a useful tool for collecting data on a shoestring. Using features like data validation make it even better because it’s easier to enter the right data!
