Tutorial: Forcing valid data entry in Excel

Avoid mistakes in your spreadsheets by forcing users (and yourself!) to enter valid values. Discover how to use Data Validation in this Microsoft Excel tutorial by Sean McManus, from the book 100 Top Tips: Microsoft Excel

To improve data quality, you can validate data as it is entered into the spreadsheet.

1. Select the cells you want to apply validation to.

2. Click Data Validation on the Data tab of the ribbon. The Data Validation options open.

3. In the Allow menu, you can choose to allow any value (the default), whole numbers, decimals, dates, times, or text of a certain length. You can also define custom validation (see Tip 9) or use a list of valid data items (see Tip 8).

4. In the Data menu, you can choose a sequence of valid values (between), minimum and/or maximum values, or a sequence of invalid values (not between). Use the boxes below to enter the start and end dates, or minimum and maximum numbers. Use the Input Message tab to give users instructions on entering valid data. Instructions are shown when users select the cell.

   

Where next?

• See the next tip
• See the table of contents and find out more about the book
• Buy the book

About 100 Top Tips: Microsoft Excel

This tutorial is from 100 Top Tips: Microsoft Excel. The powerful pocketbook helps you to learn more from your data, be more productive with Excel, and improve the accuracy of your spreadsheets. It's packed with shortcuts, tips, and hacks for Excel that solve everyday business problems. Find out more and download a free PDF sampler here, and order the book online here.

Buy the book here