• 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

August 14, 2021 by Andrew Childress
Use data validation in Excel

Spreadsheets are often shared with others, and users can make changes to them. Let’s learn how to use Data Validation in Microsoft Excel.

Spreadsheets are often shared with others, and users can make changes to them. But you may find yourself wanting to control the values that users can insert into given cells. For example, you may need users to insert a value between 1 and 10 into cell D5. Fortunately, Excel lets you control inputs to ensure that correct data is used. Let’s learn how to use Data Validation in Microsoft Excel.

How to Use Data Validation in Excel

In the above example, you’re seeking inputs in cell D5 equal to at least 1, but no greater than 10. You want to stop others from erroneously entering in a value like 25, for example. You could add written instructions, but this isn’t a fully effective way to control your data. Excel’s Data Validation feature is here to help.

Make sure that you have the cell selected that you wish to set conditions for. To use Data Validation, navigate to the Data tab on Excel’s ribbon.  You’ll see a variety of options here. The one you’re searching for is the Data Validation button on the right side of the Data tab. Click on it.

Data validation in Excel

Excel will launch the Data Validation window. Here, you can specify conditions that the value in the cell should meet. On the Allow dropdown, you can choose from categories of data. For this example, click Whole Number.

The Data dropdown helps you specify ranges. You can choose Between in this example, or other conditions like Greater Than or Equal To. Finally, in the Minimum and Maximum boxes, you can numerically define your range.

Use data validation in Excel

When you’ve set your conditions, click OK. Excel will apply the Data Validation rules to your selected cell. If you or another user tries to type a number not matching the conditions, Excel will reject the input and display an error message.

Data validation rules in Excel

As you can see, Data Validation is a quick and easy way to control inputs to cells on your spreadsheets.

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

About Andrew Childress

Previous Post:Add background in ExcelBackground in Excel
Next Post:Watch Window in Excel

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