"Creating Excel 2007 Data Validation Rules"

The Excel 2007 data validation tools allows you to set the validation rules for correcting data such as finding duplicate data values.

Validation rules let you determine how data should look and what type of data is allowed for any given column of data. For example, you can fix the data of a spreadsheet in two ways:

• If the data is already in the spreadsheet, you can have Excel find and mark the incorrect values.
• If the data is being added to the spreadsheet, you can restrict the values a user can enter in a cell.

To create a validation list, type the values you want to include into adjacent cells in a column or row. In some cases when entering data, you should know the range of values or which specific values can be used for each column. You can use that knowledge to create data validation rules that help clean your data.

The validation rule can ensure that data entered has the correct format, and you can restrict the data entered to whole numbers, decimals, dates, times, or a specific text length. You can also specify whether the values need to be between, not between, equal to, not equal to, greater than, less than, greater than or equal to, or less than or equal to the values you specify.

To create a basic Excel 2007 data validation rule

• Click in the cell in which you want to create a validation list.
• From the Data tab, in the Data Tools group, click Data Validation icon.
• From the Data Validation dialog box displayed, click the Settings tab.

• Click the Allow: drop down menu, and select the validation criterion.
• Click the Data: drop down menu, and select the validation criterion.
• If necessary, in the Minimum and Maximum columns, click and drag to select the valid entries, or type = followed by the range name.
• Click the Input Message tab.

• Type a title for your message in the Title: column as well as the Input message: column.
• Click the Error Alert tab.

• In the Styles drop down menu, select a style from one of the three option that most suitable to your situation:

- Stop if you want to stop the entry of invalid data.
- Warning if you want to display a warning to the user, but not prevent entry.
- Information to provide information to the user.


• In the Title: column, type a title.
• In the Error message: column, type an error message that you wish to display.
• Click OK. Excel creates the data entry rule.

Note: When you click in the cell with validation rule created, Excel 2007 displays your input message. If you enter invalid data, it displays an error alert. So, do wisely use the Excel 2007 data validation feature!

Related Topics:

Customizing and Adding Commands to the Quick Access Toolbar in Excel 2007

Guide to Highlight and Remove Duplicates Values in Excel 2007

How to View Multiple Worksheets at the Same Time in Excel 2007?

How To Protect Excel Worksheet or Workbook with the Password?

Using the Watch Window Feature in Microsoft Excel 2007!

How to Create Excel 2007 Drop Down List? - The Simple Way

Can't find what you're looking for? Try Google Search!

Custom Search

Back to Top

You're viewing the Excel 2007 data validation info page, click here to go back to the Home Page

Add To Your Social Bookmarks: Blink Del.icio.us Digg
Furl Google Simpy Spurl Technorati Y! MyWeb