Excel Data Validation

Contents:
Data Validation criteria setting | To allow Text length less than 10 characters in cells  | To allow Whole number between 0-9 in cells | To allow only positive numbers in cells Input message |  excel Error Alertexcel input message

You can use Data validation to control what can be entered into a cell. such as: just allow to be entered positive numbers, only allow to be entered Whole number between 0-9 in cells, just allow to be input the text that the length is less that 10 characters. This post will guide you how to use data validation in excel cells.

Data Validation Criteria Setting

1. To Allow Text Length Less Than 10 Characters In Cells

Step1# Select one cell or range , such as : A2

Step2# Go to “DATA” tab on the ribbon, then click “Data Validation”, Data Validation window will appear.

excel data validation 1

Step3# Select “Text length” from“Allow” drop-down list.

excel data validation 2

Step4# Select “less than” from “Data” drop-down list.

excel data validation 3

Step5# Enter the length value that you wish to restrict. This example need to input “10”.

excel data validation 4

Step6# Once you enter the characters is more than 10, one error dialog box will appear, it will let you know that the value you entered is not valid.

excel data validation 5

2. To Allow Whole Number Between 0-9 In Cells

x
How to Highlight Every Other Row Using Conditional Formatting in Excel

Set up to allow Whole number between 0-9 in cells, just execute the following steps:

Step1# Select one cell

Step2# Clicking “Data Validation” command in “Data Tools” group in “DATA” tab.

excel data validation 1

Step3# Select “Whole number” from“Allow” drop-down list.

excel data validation 6

Step4# Select “between” from “Data” drop-down list.

excel data validation 8

Step5# Enter “0” value in “Minimum” text box, and enter “9” value in “Maximum” text box.

excel data validation 7

3. To Allow Only Positive Numbers In Cells

Set up to allow only positive numbers in cells via the following steps:

Step1# Select one cell(A2)

Step2# Clicking “Data Validation” command in “Data Tools” group in “DATA” tab.

Step3# Select “Decimal” from“Allow” drop-down list.

excel data validation 9

Step4# Select “greater than” from “Data” drop-down list.

excel data validation 10

Step5# Enter “0” value in “Minimum” text box

excel data validation 11

Input Message

When you select the cell that set up data validation, the input message will showThe “Input Message” is enabled by default, but there is no any Input message. The below steps will guide you how to define the Input Message.

Step1#  Select one cell (A2)

Step2#  Clicking “Data Validation” command in “Data Tools” group in “DATA” tab.

Step3#  click “Input Message” tab, enter messages in “Input message” text box, click “OK” button

excel data validation input message1

Step4#  let’s see the result.

excel data validation input message2

Error Alert

When user enters invalid data in cells , one Error Alert box will appear. The  alert message will show you that the value you entered is not valid.  This following steps will guide you how to define Error message.

Step1# Select one cell (A2)

Step2# Clicking “Data Validation” command in “Data Tools” group in “DATA” tab.

Step3# click “Error Alert” tab, enter messages in “Error message” text box, click “OK” button.

excel data validation error alert1

Step4# Let’s see the result.

excel data validation error alert2

Related Posts

Delete Rows Based on Cell Value
remove rows based on values3

This post will guide you how to use the Find & Replace feature to delete or remove all rows based on certain cell value in Microsoft Excel. Or how to delete all rows that contain certain value with VBA code ...

How to Add Prefix or suffix to Cell
add prefix to cell113

This post will guide you how to add common prefix or suffix to all cells or the range of cells in your worksheet in Excel. How do I add the same prefix or suffix to a range of cells without ...

Quickly Insert Multiple Rows or Columns in Excel
insert multiple rows or columns13

This post will guide you how to quickly insert multiple rows or columns in your Microsoft worksheet. You will see that how to add new multiple rows or columns with shortcut or insert feature, or insert copied cells in excel. ...

Split Excel workbook into separate Files
split workbook into separate file3

This post will guide you how to split a large workbook to separate files and each worksheet should be saved as a single file. How do I split the whole workbook into several excel worksheets or single excel file. Assuming ...

How to Get the List of File Names From a Folder
get list of file names from a folder11

This post will teach you how to get the list of file names in a given directory to a worksheet in Excel. You will learn that how to use Excel to view Files and Folders in your worksheet by get ...

Find Duplicate Values in Two Columns
find duplicate cells in two columns111

This tutorial will show you how to find duplicate values in two given columns in Excel. Or How to compare two columns and find duplicate entries in Microsoft Excel worksheet. And you will learn two methods to compare columns so ...

Remove Leading and Trailing Spaces
remove leading and trailing spaces112

In this tutorial you will learn that how to remove leading or trailing spaces from a text string in a cell in Excel. This post will guide you how to delete leading or trailing spaces of all cells in a ...

Generate Random Passwords
generate random passwords5

This post will teach you how to quickly generate a random password with Formula in Excel. You can combine a series of worksheet functions to create a complex formula to generate a random password in Microsoft Excel. Generate Random Password ...

Sort by Second or Third character in a Column
sort string by second character13

This post will guide you how to sort text string by second or third character in a Column in Excel. You may be want to sort a string in a Cell by the Second or last character in excel, how ...

Copy a Cell to Clipboard with Single Click
copy a cell to clipboard3

This post will teach you how to use the Excel VBA macro code to copy a Cell to clipboard with only single click in Excel. How do I automatically copy the content of cell to Clipboard while clicking on the ...

Sidebar