• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Data Validation in Excel

May 24, 2021 by Andrew Childress
Excel data validation

When you’re working in a spreadsheet, it’s useful to ensure that users values meet certain conditions. Here's how to use data validation in Excel.

When you’re working in a spreadsheet, it’s often useful to ensure that users input values that meet certain conditions. These are easy to set up, and help you control your files. Let’s learn how to use data validation in Microsoft Excel.

How to Use Data Validation in Excel

Imagine that you’re sharing a spreadsheet that requires a date to be entered into a certain cell. Or, you have a file that asks for a five-digit zip code to be entered into cell D1. If other people are responsible for those inputs, it’s useful to ensure that Excel won’t accept inputs that don’t match the given condition.

This feature is built into Excel, and it’s called data validation. It’s a quick and easy way to ensure you see the desired inputs when users edit a certain cell.

Using zip codes as an example, go ahead and click into cell D1. Since zip codes have five digits, you’ll want Excel to reject any input with more or less than five digits in it.

With the cell selected, go to the Data tab on Excel’s ribbon. Over on the right, you’ll see the Data Validation dropdown menu. Click on it. The Data Validation tab will open up.

Data validation

On the Settings tab, you’ll see the Allow dropdown underneath Validation Criteria. This is where you begin specifying the conditions for a given cell. In this case, even though you’re using a numerical input, you’ll want to select Text Length. Other options include Date, Time, Custom, and more.

The minimum and maximum values should both be set as 5. This will lead Excel to reject any input that doesn’t have exactly five digits.

Excel data validation

When you’re finished, click OK. Then, try and type a random, non-five digit number into cell D1. Excel will block the input and return an error message:

Data validation in Excel

Type in a five-digit zip code, and the input will be accepted normally.

As you can see, data validation makes it easier to gain and maintain control over your Excel spreadsheets.

Category: Excel TutorialsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Set print area ExcelPrint Area in Excel
Next Post:Budget in ExcelColor code Excel budget

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top