Data validation is a feature in Excel which is used to control what users can enter into a cell. It allows you to dictate specific rules. It also allows users to display a custom message if users try to enter invalid data. Show
What Is Data Validation in Excel?Data validation in Excel is a technique that restricts user input in a worksheet. It is often used to limit user entry. PCP in Business AnalysisIn partnership with Purdue UniversityVIEW COURSESettings TabThe settings tab is where you enter the validation criteria. There are eight options available to validate for user input:
Input Message TabYou can set the input message to explain what data is allowed in a given cell. This tab is optional.
Your Career as a Data Scientist is Waiting!Professional Certificate Program In Data ScienceExplore ProgramError Alert TabYou can show the error message if the user tries to enter the invalid data. On the error message tab:
Now, when you try to enter the value beyond the range, you will get an error message. Now that we are familiar with the basic concepts, let’s look at a step-by-step process to implement data validation in excel. How to Validate Data in Excel?Step 1 - Select The Cell For ValidationSelect the cell you want to validate. Go to the Data tab > Data tools, and click on the Data Validation button. A data validation dialogue box will appear having 3 tabs - Settings, Input Message, and Error Alerts. Step 2 - Specify Validation CriteriaOn the settings tab, specify your validation criteria. Free Course: Introduction to MS ExcelMaster the Fundamentals of MS ExcelEnroll NowStep 3 - Under Allow, Select The CriteriaUnder Allow, select an option from Whole Number, Decimal, List, Date, Time, Text Length, and Custom. Step 4 - Select ConditionUnder Data, select a condition and set required values based on what you choose for Allow and Data. Step 5 - Input MessageYou can enter the input message if you want. This step is optional. Step 6 - Custom Error MessageYou can also set your custom error message. This step is optional. Step 7 - Click OkClick OK. Now, if you try entering a value outside the specified range (10, 50), it will result in an error. Business Analyst Master's ProgramGain expertise in Business analytics toolsExplore ProgramExcel Data Validation List (Drop-Down)To add the drop-down list, follow the following steps:
You will get the following result. Date ValidationYou can set-up the date validation in Excel. Select date in the allow box and pick up the appropriate criteria. Follow these steps to set-up date validation:
The above date validation will only accept date values between 9th Nov 2020 to 18th Nov 2020. Kick-start your career growth story with PGP in Business Analysis. Get a chance to master Excel, Tableau, and Python tools. Start learning now! ConclusionThe data validation in Excel provides a way of limiting the mistakes from the user and collecting valid data from the user. It results in standard data entry and systematic reporting. Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel certification course, which includes Power BI training. You can even enroll for the Post Graduate Program in Business Analysis that helps you build extensive business expertise with real-world projects The Business Analytics course is a comprehensive program that provides a very thorough understanding of the basic concepts of data analysis and statistics and how they help in data-driven decision making. The Business analytics training introduces you to Power BI, one of the most popular BI tools today, and allows you to learn the key statistical concepts to derive insights from the available data sets and help you present your findings using executive-level dashboards. If you have any questions, do reach out to us by placing your queries in the comments section of this article (at the bottom). Our subject matter experts will promptly answer them for you. Find our Post Graduate Program in Business Analytics Online Bootcamp in top cities:NameDatePlacePost Graduate Program in Business AnalyticsCohort starts on 5th Jan 2023,Weekend batchYour CityView DetailsPost Graduate Program in Business AnalyticsCohort starts on 25th Jan 2023, Weekend batchYour CityView DetailsPost Graduate Program in Business AnalyticsCohort starts on 15th Feb 2023, Weekend batchYour CityView Details About the AuthorAryan GuptaAryan is a tech enthusiast who likes to stay updated about trending technologies of today. He is passionate about all things technology, a keen researcher, and writes to inspire. Aside from technology, he is an active football player and a keen enthusiast of the game. How do I find where a dropGo to Data > Data Validation. On the Settings tab, click in the Source box, and then on the worksheet that has the entries for your drop-down list, select all of the cells containing those entries. You'll see the list range in the Source box change as you select.
How do I find the location of Data Validation?Go to the Data tab > Data tools, and click on the Data Validation button. A data validation dialogue box will appear having 3 tabs - Settings, Input Message, and Error Alerts.
How do I find hidden Data Validation in Excel?This is how to do it:. Click on the cell which is being used for the drop-down list.. Select the *Data* menu, and then click on *Validation… ... . A *Data Validation* window should appear (shown above). On the default *Settings* view you should be able to see the Validation criteria.. Why can't I see Data Validation option in Excel?In order to fix this issue, make sure you unprotect your worksheet. The other option is to copy the data set into a new worksheet that is not shared. This will allow you to reactivate the data validation tool. Unshare an Excel File.
|