Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You've heard the old saying, garbage in garbage out. And that's very true when you're working with large amounts of data, especially in Excel. And what we're going to look at now is the Data Validation option, of the Excel 2007 application. What Data Validation allows you to do is it will make sure that the information that's included in the cells, is within a particular range. That it needs a certain set of criteria and if it doesn't, it will let you know. Let's see how that works. I'm going to select the rows in column E. And I'm going to go back up to my Data Validation command that I find on my Data Tools group, on my Data tab.
I select it and I get a dialog box that comes up and asks to input specific criteria that it will then apply to that section. Under the Settings tab, it's asking me, "What validation do you want to apply?" Notice that you have a whole list of options to choose from. I'm going to select Date. Under Date I'm going to identify that I wanted the dates included in that column to be between 01-01-05, which is the start date for people at EatCake, and for the purposes of our demonstration here 01- 11- 06.
The Input Message tab allows you to put in a message when a particular cell is selected. That could be a reminder that the input dates need to be within a certain range. You can also select your Error Alert, that shows up when errors have been inputted into the system. So you have three different styles; you could do a Stop Style, which gives you this icon, you can have the Warning Style, which gives you this icon, or you could have an Information Style, which gives you this icon.
I like to put in the Stop Style just to make sure that people are aware of what's happening. The title would be EatCake Start Dates, and my error message will be, The date must be within the EatCake Start times. I'm going to click OK. Notice when I come back to my area that it's done a validation automatically. And it points for me, to the different areas, and identifies that there are errors within the data. You select the little warning sign on the left, choose Display Type Information to see what the information is about, and it gives you the typical field error. It's telling me here that the date now must be between that and that date range.
Now, that's a typical error message. How do I get the error message that I input into the system to show up? If I delete out the information that's in that cell, and start again, and this time I input 01- 11- 03, which is clearly outside of my date range, click Enter. Here's the error message that comes up that I input into the system. Notice it shows my icon and it states my static text, The date must be within the EatCake Start times.
I can retry it, put in a new date, or cancel it, and start over. You can customize the Data Validation for any one of your Excel spreadsheets be it a cell, a column, a row, or any range of data to ensure that your information is as clean as possible, so you can make the analysis you need to when using Excel.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98120 Viewers
80 Video lessons · 141333 Viewers
59 Video lessons · 59685 Viewers
52 Video lessons · 72914 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.