WPS Office

Free All-in-One Office Suite with PDF Editor


Edit Word, Excel, and PPT for FREE.


Read, edit, and convert PDFs with the powerful PDF toolkit.


Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android


How to use data validation in excel (Formula, Examples, Guide)

July 26, 2022

To guarantee that users enter specific values into a cell, use Excel's data validation feature. Excel's Data Validation function can assist users in entering accurate and pertinent information into your worksheets. The type of information that can be input in a cell is limited by data validation, which also gives the user guidance on how to enter data in a cell.

A practical and available tool for ensuring data integrity when developing a financial model is data validation in Excel. Data validation in Excel is a very helpful tool to secure your model inputs and outputs, even if it is just a simple extra step. This essay aims to give readers a better knowledge of the benefits of data validation and how to use it in straightforward steps.

Data validation in excel online, 2016 and 2019

In the following illustration, we only allow visitors to enter whole numbers between 0 and 10.

How to create data validation rule in excel:

Follow the steps below to construct the data validation rule.

1.Choose cell C2.Click Data Validation under the Data Tools section of the Data tab.

2.Under Settings:Click Whole number under the Allow list.Click between in the Data list.Enter the Lowest and Highest values.

Input message in excel

When the user selects the cell, input messages that specify what to enter appear.On the tab for Input Message:'Show input message when cell is chosen' should be checked.Enter a title.Enter a message here.

Error alert in excel

1.You can display an error message to users if they disregard the input notice and enter an invalid number.On the tab for error alerts:Show error alert after invalid data is entered must be checked.Enter a title.Enter a message of error.

2.Click on ok.

Data validation result in excel

1.Select cell C2.

2.Enter a value that is more than 10.Result:

Reminder: To remove data validation from a cell, select the cell, then click Data Validation, followed by Clear All, on the Data tab, in the Data Tools group. To quickly pick all cells with data validation, use Excel's Go To Special feature.

Note: This above written article is an attempt to show you how to use data validation function in excel online, 2016 and 2019. This above stated method can be applied in both windows and mac.You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.                                  

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.