Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Two-way lookup with VLOOKUP in Excel
  • Multi-criteria lookup and transpose in Excel
  • How to reference named range different sheet in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • Count rows with at least n matching values

Data Analysis Examples

  • Get column index in Excel Table
  • How to Create One and Two Variable Data Tables in Excel
  • Understanding Anova in Excel
  • How To Insert and Customize Sparklines in Excel
  • How to Create Gantt Chart in Excel

Data Validation Examples

  • Data validation must not exist in list
  • How To Create Drop-down List in Excel
  • Excel Data validation only dates between
  • Excel Data validation number multiple 100
  • Excel Data validation no punctuation

Prevent invalid data entering in specific cells

by

— Set criteria in your worksheet to accept specific data.

Use data validation in Excel to make sure that users enter only values that meet a set criteria into a cell.

Steps to navigate to Data Validation icon in Excel

Data Tab → Data Tools group → Data Validation

  • Data Validation Example
  • Create Data Validation Rule
  • Input Message
  • Error Alert
  • Data Validation Result

Data Validation Example

In this example, we restrict users to enter a whole number between 0 and 10.

Worked Example:   Select, Insert, Rename, Move, Delete Worksheets in Excel

Create Data Validation Rule

To create the data validation rule, execute the following steps.

1. Select cell C2.

2. On the Data tab, in the Data Tools group, click Data Validation.

click-data-validation

On the Settings tab:

3. In the Allow list, click Whole number.

4. In the Data list, click between.

5. Enter the Minimum and Maximum values.

validation-criteria

Input Message

Input messages appear when the user selects the cell and tell the user what to enter.

Worked Example:   How to reference named range different sheet in Excel

On the Input Message tab:

1. Check ‘Show input message when cell is selected’.

2. Enter a title.

3. Enter an input message.

enter-input-message

Error Alert

If users ignore the input message and enter a number that is not valid, you can show them an error alert.

On the Error Alert tab:

1. Check ‘Show error alert after invalid data is entered’.

Worked Example:   Protect and Unprotect Worksheet in Excel

2. Enter a title.

3. Enter an error message.

enter-error-message

4. Click OK.

Data Validation Result

1. Select cell C2.

input-message

2. Try to enter a number higher than 10.

Result:

error-alert

Note: to remove data validation from a cell, select the cell, on the Data tab, in the Data Tools group, click Data Validation, and then click Clear All. You can use Excel’s Go To Special feature to quickly select all cells with data validation.

Post navigation

Previous Post:

Change Case: Uppercase, Lowercase, Propercase in Excel

Next Post:

IF, AND, OR and NOT Functions Examples in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Get last name from name with comma — Manipulating NAMES in Excel
  • Two ways to Compare Text in Excel
  • Convert Text to Numbers in Excel
  • Replace one character with another in Excel
  • How to count total words in a cell in Excel
  • Count birthdays by month in Excel
  • Series of dates by day
  • Find Last Day of the Month in Excel
  • Add years to date in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
  • MDURATION function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles