Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
Data Validation in excel is used to limit a user to provide inputs to a certain cells or input ranges, for a data with a large number of users there can be multiple formats for the same data so we use data validation where we provide a pre-defined inputs for a user to select among to avoid multiple different formats of same data, data validation is available in the data tools of data tab.
Data Validation in Excel (Table of Contents)
- Data Validation in Excel
- Purpose of Excel Data Validation
- How to Use Data Validation in Excel?
- Examples of Excel Data Validation
Data Validation in Excel
- Data validation allows you to – make a list of the entries that are allowed/ not allowed to be entered in a worksheet cell, create an error/alert/warning message to explain more about the allowed values in a worksheet cell, create messages to be shown when an invalid data is entered in a cell.
Purpose of Excel Data Validation
- The purpose of data validation in excel is to restrict users from entering the invalid data values in the worksheet.
- It is also useful to quickly gather, sort and study the data having said that the values have a criterion defined and can be seen via the Data Validation window.
- This is acts as a guideline to the users while entering the data as custom messages mentioning the valid data format is displayed when a worksheet cell is selected.
How to Use Data Validation in Excel?
Here we are going to discuss the steps to create data validation in excel.
- The first step to create data validation in excel is to select the cells or column containing data.
- Goto Data tab and click on Data Validation
- Choose option 1 i.e. Data Validation from the list of options.
- Once you click on Data Validation, a new window will appear as shown in the following screenshot.
- 1st tab is Settings. It contains validation criteria and the data format which is to be allowed.
- One can choose to ignore blank values by clicking ‘Ignore blank’ checkbox.
- 2nd tab is Input Message where one can enter the message to be displayed when a cell is selected.
- 3rd tab is Error Alert. This is used to configure the error message/s to be displayed whenever an invalid data entry is made in the configured cells or column. The types of error and hence the sign displayed for the error can be Stop, Warning or Information.
Different Data Formats of Allowed Data
- The whole Number – states that the format to be allowed is the whole numbers.
- Decimal – states that the format to be allowed is decimal numbers.
- List – states that the format to be allowed is a drop-down list.
- Date – states that the format to be allowed is the dates.
- Time – states that the format to be allowed is the time values.
- Text Length – states the allowed length of the text to be entered in a cell.
- Custom – indicates any custom formula.
Different Criteria for Excel Data Validation
- between – states that the value should be between the specified minimum and maximum numbers.
- not between – states that the value should not be between the specified minimum and maximum numbers.
- equal to – states that the value should be equal to the given number.
- not equal to – states that the value should not be equal to the given number.
- greater than – states that the value should be greater than the given number.
- less than – states that the value should be lower than the given number.
- greater than or equal to – states that the value should be greater than or equal to the given number.
- less than or equal to – states that the value should be less than or equal to the given number.
Different Alert Styles in Excel Data Validation
- Stop – It stops users from entering invalid data in a worksheet cell. The Stop alert window gives two options to the user – Retry and Cancel.
- Warning – It warns users that the invalid data is entered. However, a warning does not stop the user from entering the invalid data. It gives three options to the user – Yes, No and Cancel.
- Information – It informs user that the data entered is invalid. Like Warning, it does not stop the user from entering the invalid data. It displays two options to the user – OK and Cancel.
Examples of Excel Data Validation
It is very simple and easy to use. Let understand the working of Data validation by some examples.
Example #1 – Number Validation
As shown in figure. 1, -2.5 is marked red indicating an invalid data. A tooltip with message – ‘Enter number greater than zero’ is shown. A rule assigned to column. B is as follows.
As shown in figure. 2, the validation criteria entered is, the number “decimal” number should be greater than or equal to zero. Meaning, negative values are restricted.
Things to Remember
- It is not full proofed and be breached easily.
- If the data from cells with no-validation is copied into the cells with validation in place, then the entered data is not validated and data validation overrides the data validation resulting into no validation on the copied data.
This has been a guide to Data Validation in Excel. Here we discuss how to create Data Validation in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- How to Use Percentile Formula in Excel? (with Excel)
- Examples of Paste Special in Excel VBA
- Excel VBA in GoTo
- Formula for adding Text in Excel
- “Not Equal to” in Excel Formula
- How to Insert a Checkbox in Excel?
- Sensitivity Analysis in Excel
- Convert Numbers to Text in Excel
- PERCENTILE Excel Function
- Paste Special Shortcuts