Data Validation in Excel

This Tech Tip was submitted by Jinchun Yu, Assistant Director of Data Analysis and Management, The University of Alabama.

Institutional researchers receive or collect data from a variety of sources. While some of us clean the data afterwards, there are ways to limit the input by restricting the end user on data types and range values. The Excel Data Validation function guarantees that each data value you enter, have entered, or will enter will be correct and accurate. If you have hundreds of rows, scrolling to quickly review the data may not be the most efficient way to ensure its accuracy. Also, if you receive a data file, you can make sure the data is within a specific range or qualifies to the set criteria. This feature is particularly useful when you are verifying data or requesting other users to input data. This tip was tested using Excel 2010 on the Windows platform.

  1. Highlight a range of cells, rows, or columns. In this example, the Age column is being validated.

  2. Go to the Data tag and select Data Validation.

  3. Under Settings, select a validation criterion. You can allow any value or restrict it to only whole numbers, decimals, lists, dates, or time values. Depending upon the selection criterion, the Data values will change.  

  4. Under Source, type your comma delimited list. You can also select a range of values from the Excel sheet by clicking the button at the very end. For example, if you select a list, you can restrict the input to the values selected for the list. The following reflects the selection for gender. 

  5. If you select the checkbox for in-cell dropdown, a dropdown box will appear next to the cell with the values previously entered. You can select either a value from the list or type one manually. If the number you entered does not match the list value, Excel will show an error such as, “The value you entered is not valid.” 

Excel Keyboard Shortcuts 

Microsoft Excel has more than 300 shortcuts. Although using shortcuts will help you work with the data in your spreadsheets without having to scroll through menus, options, or left- and right-clicks, it is difficult to remember all of them unless you use them frequently. As an institutional researcher, I’ve used the following shortcuts regularly and found them to be very helpful. The list below is not all-inclusive. I often refer to the Microsoft website for more tools.  


Excel Tip: Select a Range of Data

It gets challenging when you have data spanning multiple rows, columns, and pages. In that case, you must drag the mouse or hold the Shift key and then select a range. Here is one trick that will help you select a range of cells. 

  1. Move the cell pointer to the starting cell and select the cell.
  2. Press F8 to activate extend. (In Excel 2010, you will see the message “Extend Selection” in the bottom left corner.)
  3. Use your arrow keys to select adjacent cells or a range of cells, or simply use the mouse pointer to select the ending row/column.
  4. Press Esc to end extend.
 

 Comments

 
To add a comment, Sign In
Total Comments: 12
 
Danielle posted on 4/10/2013 3:30 PM
Thanks for sharing! This will definitely save us time when reviewing and cleaning survey data.
Heather posted on 4/10/2013 4:39 PM
What a timely post! As we push for undergraduate research, Excel Spreadsheet is the most economical and user-friendly tool available for all our students. Currently, as I type this the rubric is minimized on my screen!, I am developing a rubric for how to utilize Excel for our undergraduate students. This tip, along with any others you care to share, will be included for the undergraduate research and my colleagues use.
Thanks so much,
Heather

Dr. Heather L Garten

Embry-Riddle Aeronautical University Worldwide
Jim posted on 4/10/2013 5:26 PM
The keyboard shortcuts chart is going on my wall as a reference! They're great, but I can never remember them. As for data validation, you can also use some of the conditional formatting options to highlight cells that don't meet required standards, although this just provides a visual prompt and not a firm restriction on what you can do.
Marlene posted on 4/10/2013 5:49 PM
Very helpful tips!
Nicole posted on 4/11/2013 9:45 AM
A great tech tip and very useful shortcut keys.
My two favorite shortcuts:
Ctrl + A: highlights all cells in a worksheet
F4: repeats last key action.
Gary posted on 4/11/2013 12:34 PM
I appreciate the attempt by Microsoft to add explicit data validation tools. However, I get nervous when I can't see what is going on behind the scenes. For example, numbers formatted as text with a leading space " 99" (which commonly happens due to unintentionally copying and pasting cell attributes along with values) are not identified as invalid if they are already present in the data, and will not be treated correctly if used in a calculation. It's a nice tool if you are aware of the pitfalls. However, I think pivot tables are still the most useful feature in Excel for data validation. Thanks for the interesting tip!
Fawzia posted on 4/11/2013 12:57 PM
Great tip!

Thank you.

Fawzia Abbas
Bamby posted on 4/11/2013 1:22 PM
Good tips. I especially like the shortcut reminder. Using shortcuts are much more efficient than fumbling around with the menu bar, tabs & drop downs, which change locations with every new version and seem to grow exponentially.
Angela posted on 4/11/2013 2:17 PM
This is a very helpful tip. Here's another list of keyboard shortcuts that I refer to frequently: http://www.shortcutworld.com/en/win/Excel_2010.html
Mary Jo posted on 4/11/2013 6:15 PM
This is a useful and helpful tip!
Eric posted on 4/12/2013 11:47 AM
Thank you for sharing this advice on data validation, this is very helpful! Thank you also for including the keyboard tips, these are great time savers.
Arlene posted on 4/18/2013 7:38 AM
I really appreciate you reminding us of this feature. I will begin using it immediately. We often have surveys that must be entered manually by student assistants. This feature will eliminate errors in data entry.