HomeApplicationOffice information

Limit input values ​​using Data Validation in Excel

The numbers in Excel spreadsheets are extremely important, even the confusion of 1 digit in the spreadsheet has huge consequences. To avoid data confusion, you should set a limit for the column of data cells to minimize the consequences of confusion. The following article will guide you in detail how to Limit the value entered by Data Validation in Excel.

Step 1: Select the data range you want to limit the value (for example, here limit the value entered in the position allowance column) -> go to the Data tab -> Data Validation:

Limit input values ​​using Data Validation in Excel Picture 1

Step 2: A dialog box appears and click the Setting tab as follows:

- The Allow section includes the following options:

+ Whole number: Only allow integer input

+ Decimal

+ List: Limiting the value entered in the list available.

+ Date: Limiting data type to date.

+ Also type Text Length, Custom.

- Data section includes options for the range of data, for example when selecting Whole number has the following ranges:

+ Between (not between) : the limit between a certain range of values, for example, in the range from 1 to 100. Where minimum: Limit the minimum value, maximum: Limit the maximum value.

+ Equal to (not equal to): Equal to a specified value.

+ greater than, less than: Greater than (less than) a specified value

+ Greater than or equal to, less than or equal to: Greater than (less than) or equal to a specified value.

Limit input values ​​using Data Validation in Excel Picture 2

The example here limits values ​​from 250,000 to 900,000:

Limit input values ​​using Data Validation in Excel Picture 3

Step 3: After limiting the value, you need to adjust the content displaying the error message so that users can understand and re-enter the value by clicking on the Error Alert tab :

Limit input values ​​using Data Validation in Excel Picture 4

Step 4: Return to the worksheet, enter the value for the Position Allowance column, when entering the value beyond the limited range, an error message appears specifying the error content for you to edit:

Limit input values ​​using Data Validation in Excel Picture 5

However, with Data Validation only limiting values ​​when that value is entered directly from the keyboard, the case of entering indirect values ​​for cells using the Data Validation formula has no effect.

The above is a detailed guide on how to limit the value entered by Data Validation hope to help you. Good luck!

4 ★ | 1 Vote

You should read it

May be interested

  • How to enter formula data in ExcelHow to enter formula data in Excel
    show you how to enter formula data in excel. working with excel you can not help but work with formulas and functions in excel. formula data input is a type of data entry through data that is available based on a calculation or 1
  • How to compare data on 2 Excel columnsHow to compare data on 2 Excel columns
    users can compare data on 2 excel columns with the countif function.
  • Find and delete duplicate data in ExcelFind and delete duplicate data in Excel
    find and delete duplicate data in excel. in the process of working with excel spreadsheets, there will be excel files with large amounts of data so duplication of data is very common. there are cases where duplicate data is useful, but sometimes values ​​are
  • How to Find Duplicate Data in ExcelHow to Find Duplicate Data in Excel
    when working on microsoft excel spreadsheets with lots of data, it is likely that you will encounter duplicate values. microsoft excel's conditional formatting feature will correctly display duplicate locations, while the remove duplicates action will remove those entries. reviewing and removing duplicates ensures the accuracy of your data and presentation.
  • Steps to export data from photos to MS Excel sheet on AndroidSteps to export data from photos to MS Excel sheet on Android
    the ms excel android app allows you to easily create and edit spreadsheets on your smartphone. however, it can be quite difficult to be productive on a small screen, where many menus and options are hidden by default, and the ability to input is limited.
  • How to copy values ​​in ExcelHow to copy values ​​in Excel
    how to copy values ​​in excel. in excel, if we use the usual copy-paste method (ctrl + c - ctrl + v), you will copy the entire contents of that cell including the function.
  • MS Excel - Lesson 5: Excel formulas and functionsMS Excel - Lesson 5: Excel formulas and functions
    the formula in excel is a program that performs calculations on data tables. these formulas perform very precise operations such as adding, multiplying, or comparing values ​​in worksheets.
  • Warning of duplicate values ​​in Excel - Warning of data duplicationWarning of duplicate values ​​in Excel - Warning of data duplication
    microsoft excel gives you lots of useful features to help your data entry work best. one of those features is the duplicate value warning feature.
  • How to turn off content suggestions on ExcelHow to turn off content suggestions on Excel
    when you enter content in excel, the system will save the content and make suggestions for other content, if the first character is the same. so how to turn off input suggestions in excel?
  • The input () function in PythonThe input () function in Python
    python's built-in input () function allows users to enter data, convert it into a string, and return the entered content.

Office information