Data Validation - Free Excel Tutorial

January 14, 2019

How to Prevent Duplicate Entries in Columns in Excel

This post will guide you how to prevent duplicate entries in one or multiple columns in Excel. How do I use Data Validation feature to prevent duplicate values in columns in Excel.

Prevent Duplicate Values

If you want to prevent duplicate values when entering data into your cells or columns in Excel, you can use the Excel’s Data Validation feature to reject duplicate entries.

Assuming that you want to prevent duplicate values in range B1:B4 when entering values into cells, you just do the following steps:

#1 select the range of cells in which you want to prevent duplicate values. Such as: B1:B4

#2 go to DATA tab, click Data Validation command under Data Tools group. And select Data Validation menu from the drop down menu list. And the Data Validation dialog will open.

#3 click the Settings tab in the Data Validation dialog, choose Custom from the Allow list box, and then enter the following formula into the formula box. And click Ok button.

=COUNTIF($B$1:$B$4,B1)=1

#4 click the Error Alert tab, enter the text “Duplicate Value” in the Title text box. And enter a meaning full description in the Error message text box. Such as: “The value is a duplicate value, and it is not valid”. Click Ok button.

#5 you can enter a duplicate value in range B1:B4, the value will be rejected. And when it happens, just click Cancel button to clear the error message and re-enter a valid value.

January 3, 2019January 3, 2019

How to Limit Data Entry in a Cell in Excel

This post will guide you how to limit cell entries to specific length and number of digits in Excel. How do I limit data entry to allow only text or only numbers entered into in a cell using Data Validation option in Excel. How to limit entries to numeric values in Excel.

Limit Data Entry to Allow Only Text
Limit Data Entry to Allow Only Numeric Values
Limit Maximum Cell Value
video: Limit Data Entry

Assuming that you want to limit data entry for a range of cells in your worksheet, such as: only allow to enter text values or numeric values in the selected range of cells. How to do it. You can refer to the following introduction.

Limit Data Entry to Allow Only Text

If you want to only allow entering Text values in the selected range of cells, you can use the Data Validation feature to achieve it. Just do the following steps:

#1 select the range of cells that you want to limit data entry.

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISTEXT function in Formula Text box. And click Ok button.

=ISTEXT(A1)

This formula will check if the cell value is a text value or not, if TRUE, it returns TRUE, otherwise, returns FALSE, and the Data Validation rules will be triggered.

Note: A1 is the first cell in your selected range.

Limit Data Entry to Allow Only Numeric Values

If you want to only allow entering numeric values in the selected range of cells, you can use the Data validation feature in combination with the ISNUMBER function to achieve the result. Do the following steps:

#1 select the range of cells that you want to limit data entry.

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

#3 click Settings tab, and select Custom from the Allow drop down list box. And then type the following formula based on the ISNUMBER function in Formula Text box. And click Ok button.

=ISNUMBER(A1)

The ISNUMBER function returns TRUE when cell value is a number and returns FALSE if not.

Note: A1 is the first cell in your selected range.

Limit Maximum Cell Value

If you want to limit data entries are not greater than or euqal to a given value (200) in a range, just do the following steps:

#1 Select the range of cells that you want to limit maximum cell value.

#2 go to DATA tab, click Data Validation command under Data Tools group. And the Data Validation dialog will open.

#3 click Settings tab in the opening Data Validation dialog, select Whole number in the allow drop down list box. And select less or equal to from the Data drop down list box. And then enter a numeric value 200 into text box of Maximum. Click OK button.

Video: Limit Data Entry

Related Functions

Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
Excel ISTEXT function
The Excel ISTEXT function used to check if a value is text. If so, returns TRUE; if the text is not text, the function will return FALSE.The syntax of the ISTEXT function is as below:=ISTEXT(value)…

August 27, 2018

Create Dynamic Drop Down List

This post will guide you how to create a dynamic drop down list in Excel. How do I quickly create a dynamic drop down list in Excel.

Create Dynamic Drop Down List

Assuming that you want to create two drop down list in your current worksheet, and when choose a value in one drop down list, and the values in another drop down list will be updated automatically. How to achieve it. Let’s see the below detailed steps.

#1 select the range of cells A1:B1, go to FORMULAS tab, click Define Name command under Defined Names group. The New Name dialog will open.

#2 type one Name in the Name text box. Then click OK button.

#3 select the range of cells A1:A4 to create another defined name.

#4 select the range of cells B1:B4 to create one more defined name.

#5 create the first drop down list, go to DATA tab, Click Data Validation command under Data Tools group. And select Data Validation item from the pop up menu list. The Data Validation dialog will open.

#6 click Settings tab, select List from the Allow drop down list box. And type the formula =Names in the source text box. Click Ok button.

#7 the first drop-down list has been created in Cell C1.

#8 click Cell D1 to select this cell, go to DATA tab, Click Data Validation command under Data Tools group to create the second drop down list. Click Settings tab, select List from the Allow drop down list box. And type the formula =indirect(C1) in the source text box. Click Ok button.

#9 choose one value from the first drop down list, and you will see that the values in the second drop down list will be updated.

November 23, 2017December 19, 2017

Data Validation for Specified Text only

If you want to check if the values that contain a specified text string in one cell, you can use a combination of the FIND function and ISNUMBER function as a formula in the Data Validation.

Data Validation for Specified Text only

Suppose that you want to check that if the values in column B contain the specified text string “@gmail.com”, IF True, then return TRUE, otherwise, it returns FALSE.

You can use the following formula:

=ISNUMBER(FIND("@gmail.com",B1))

In the above formula, the FIND function will locate the position of the searched text string “@gmail.com” in Cell B1. If the text string is searched, then return the starting position of the searched text string in cell B1. If not, it will return the Excel #VALUE! Error.

The returned results of the FIND function will be as the first argument of the ISNUMBER function. So if the FIND function returns the correct numeric position, then the ISNUMBER function returns TRUE. Otherwise, returns FALSE.

Last, you can add the above formula into Data Validation as rules to B1:B3.

When you adds or change the values in range B1:B3, the DATA validation will be triggered.

Related Functions

Excel Find function
The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND (find_text, within_text,[start_num])…
Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

May 4, 2017May 16, 2017

Excel Basics

MS Excel Basics

This section will introduce the basics of Excel, such as: Excel Window, Ribbon, Workbook, Worksheet, Cells, Templates, Data Validation, print ..etc.

1. Excel window

when you start the excel application, excel window will appear and this section will let you learn the various important parts of excel window.

2. Excel Ribbon

The Ribbon is the strip of buttons and icons located above the work area. and it is organized into various tabs, such as: File, Home,Insert, Page Layout, Formulas, Data, Review, and View. while clicking on a tab, you will saw all of program’s features and commands. this section will guide you how to customize the Ribbon.

3. Excel Workbook

The workbook is a file that contains one or more worksheets , by default, the excel will create a blank workbook that contains 3 worksheets. this section will guide you how to create a new workbook or open an existing workbook or close a workbook.

4. Excel Worksheet

when you open an new workbook or start excel application, it will create 3 new blank worksheets. this setion will explain you how to create/insert/delete/move/copy/rename a worksheet.

5. Excel Cells

A Cell can be used in a formula to hold differnt types of data like Numbers, Dates, Currency, Text.. this section will guide you how to format cells /set the cell type/create or change a cell reference…

6. Excel Rows

How do I insert a new row in excel? How do I delete a row in excel? How to delete multiple rows in Excel workbook? How to hide a row from being displayed in excel? How to unhide the hidden rows in excel? This section will guide you how to Insert/Delete/Hide/Unhide/Sizing columns in Microsoft Excel.

7. Excel Columns

How do I insert a new column in excel? How do I delete a column in excel? How to delete multiple columns in Excel workbook? How to hide a column from being displayed in excel? How to unhide the hidden columns in excel? This section will guide you how to Insert/Delete/Hide/Unhide/Sizing columns in Microsoft Excel.

8. Excel Ranges

Excel Ranges is a collection of cells (two or more cells). Thispost will guide you how to use excel ranges from the below 5 topics:

9. Excel Templates

Excel template is also a workbook, the only different is that it can be as a template or module to create other similar workbooks. there are many free excel templates in the internet or you can create your own excel template.

10. Excel Data Validation

You can use Data validation to control what can be entered into a cell. such as: you can restricts the values allowed in a cell.

11. Excel Protect

How do I encrypt an excel file in excel 2013? How to encrypt excel workbook? How to encrypt an excel file with a password in Microsoft excel? This section will guide you how to encrypt excel file, protect workbook and protect worksheet in excel.

12. Excel print

How do I print a worksheet in excel? How do I print a workbook in Microsoft Excel 2013? How to quick print a excel work sheet? How to print a selection excel range? How to change the print setting to print the excel file base on your request. This section will explain you how to print a worksheet/ change the page scaling setting.