• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 26. Error-Proof Data-Entry Techn... > Restricting and Validating Data Entr...

Restricting and Validating Data Entry for a Cell or Range

When designing a worksheet, you'll occasionally want to restrict the type of data users can enter in a specific cell or range. Excel lets you define data validation rules for cells and ranges to do exactly that. Examples of useful applications include the following:

  • In a list of recent sales results formatted to show only month and date, restrict entry in a specific column to only dates within the last month. This technique prevents users from inadvertently entering a date in the wrong month or year, or in the future.

  • On a budget worksheet, require that the user enter a department name and restrict allowed entries to a specific list. You can add a drop-down arrow to a cell with this type of restriction so users can pick from a list.

  • For purchase orders, check the amount a user enters against his or her authorized spending limit—say, $500. If the amount is over the limit, display a message that directs them to talk to a supervisor or re-enter the amount.

  • Ask a user to enter a description in a form; to keep data to a manageable length, restrict the total number of characters the user can enter and display a warning message if the description exceeds that length.

  • On an invoice form, allow a salesperson to enter an optional discount for good customers, but only if the amount before sales tax is over $100. Compare the entry in the Discount field with a formula that calculates the total purchases to validate the entry.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint