Kiến thức

 Learn Excel – Lesson 10: How to use Data Validation in Excel – ElectroDealPro

 Learn Excel - Lesson 10: How to use Data Validation in Excel

IT Tricks

Random

 Learn Excel – Lesson 10: How to use Data Validation in Excel

Learn Excel – Lesson 10: How to use Data Validation in Excel

You can use Data Validation to limit the type of data or user values ​​entered into cells in Excel. One of the most common uses for Data Validation is to create drop-down lists.

Microsoft Excel Microsoft Excel for Android Microsoft Excel for iOS Microsoft Excel Online

Learn Excel – Lesson 10: How to use Data Validation in Excel

  • How to use Data Validation in Excel on Windows
  • How to use Data Validation in Excel on macOS

    • Office 2013 and above
    • Office 2011
  • How to use Data Validation in Excel on the Web?

    • Add data validation to a cell or a range
    • Add other types of data validation
  • Examples of formulas in data validation in Excel

How to use Data Validation in Excel on Windows

1. Select the cell where you want to create the rule.

2. Select Data> Data Validation.

Data Validation on Excel

3. On the tab Settings, in Allow, tick one option:

  • Whole Number – Restrict cell to accept integer values ​​only.
  • Decimal – Restrict cell to accept only decimal numbers.
  • List – Select data from drop down list.
  • Date – Limit cells to only accept date values.
  • Time – Restrict cells to only accept time values.
  • Text Length – Limit the text length.
  • Custom – Customizing formulas.

4. In Data, choose a condition:

  • between – Between
  • not between – Not in the middle
  • equal to – Equal
  • not equal to – Not equal
  • greater than – Bigger
  • less than – Less than
  • greater than or equal to – Greater than or equal to
  • less than or equal to – Less than or equal to

5.On the tab Settings, in Allow, select one of the available options.

6. Set other required values, based on what you choose for Allow and Data. For example, if selected between, then select the values Minimum: and Maximum: for those cells.

7. Check the box Ignore blank If you want to skip blank spaces.

8. If you want to add a title – Title and inform your rule, select the tab Input Message, then type in the desired title and message.

9. Earn Show input message when cell is selected to show notification when the user selects or mouse over the selection box.

10. Select OK, got it.

Now, if the user enters an invalid value, a pop-up will appear with the message “This value doesn’t match the data validation restrictions for this cell.”

How to use Data Validation in Excel on macOS

If you are creating a sheet that requires users to enter data, you can restrict the entry to a specific date range or number, or ensure that positive integers are entered. Excel can restrict data entry for certain cells by using Data Validation, prompting users to enter valid data when selecting a cell and displaying an error message when they enter the wrong data.

Office 2013 and above

Bạn đang xem:  Learn Excel – Lesson 10: How to use Data Validation in Excel – ElectroDealPro

Data entry limit

1. Select the cell you want to restrict data entry.

2. On the tab Data, click Data Validation> Data Validation.

Note: If an authentication order is not available, this sheet can be protected or the workbook shared. You cannot change the data validation settings in this case.

3. In the box Allow, select the type of data you want to allow to enter, fill in criteria, and restrict values.

Note: Restricted value boxes will be labeled based on the data and limit criteria you selected. For example, if Data is selected as the data type, you can enter limited values ​​in the minimum and maximum values ​​boxes labeled. Start Date & End Date.

Prompt user for valid entries

When the user clicks on a cell with data entry requirements, you can display a message explaining how the data is valid.

1. Select the box to prompt the user for valid data entry.

2. On the tab Data, click Data Validation> Data Validation.

3. On the tab Input Message, check box Show input message when cell is selected.

4. In the box Title, type the message title.

5. In the box Input message, type the message you want to display.

Xem thêm: Giới từ (Prepositions)-Chức năng của giới từ trong câu

An error message is displayed when entering invalid data

If there are data restrictions in place and the user enters invalid data into a cell, you can display a message explaining that error.

1. Select the cells you want to display the error message.

2. On the tab Data, click Data Validation> Data Validation.

3. On the tab Error Alert, in the box Title, type a title for your notice.

4. In the box Error message, type the message you want to display if someone enters invalid data.

5. Do one of the following:

Action Click the following option on the Style pop-up menu
Ask the user to fix errors before processing Stop
Warn users of invalid data, asking them to choose Yes or No If you want to go back or continue Warning
Warn users of invalid data, but allow them to continue after ignoring the warning Important

Office 2011

Restrict data entry

1. Select the cell you want to limit data entry.

2. On the tab Data, in Tools, click Validate.

Excel's Box Tools on macOS

3. On the pop-up menu Allow, select the type of data you want to allow.

4. On the menu Data, select the desired limit criteria type, and then enter the limited values.

Xem thêm: Giải Toán lớp 11 Bài 2: Giới hạn của hàm số-Bài tập hay

Prompt user for valid entries

When the user clicks on a cell containing data entry requests, you can display a message explaining which data is valid.

1. Select the box to prompt the user to enter valid data.

2. On the tab Data, in Tools, click Validate.

3. On the tab Input Message, check box Show input message when cell is selected.

4. In the box Title, type the message title.

5. In the box Input message, type the message you want to display.

Show error message when invalid data entry

If there are data restrictions in place and the user enters invalid data into that cell, you may display an error message explaining the following:

1. Select the cells you want to display the error message.

2. On the tab Data, in Tools, click Validate.

3. On the tab Error Alert, in the box Title, type the message title.

4. In the box Error message, type the message you want to show.

5. Select one of the following options in the Style pop-up menu

  • Stop – Ask the user to fix errors before continuing.
  • Warning – Warn users of invalid data, select Yes or No to return or continue.
  • Important – Warning data is invalid but allows them to continue after ignoring the warning.

How to use Data Validation in Excel on the Web?

Add data validation to a cell or a range

Note: The first two steps in this section are for adding data validation types. Step 3-7 to create a drop down list.

  1. Select one or more cells to authenticate.
  2. On the tab Data, in groups Data Tools, click Data Validation.
  3. On the tab Settings > Allow, choose List.
  4. In the box Source, type the values ​​listed, separated by commas. For example, type Low, Average, High.
  5. Make sure ticked In-cell dropdown. Otherwise, you will not be able to see the drop down arrow next to that cell.
  6. To determine how you want to handle blank values, select or uncheck the checkbox Ignore blank.
  7. Verify data validation to ensure it works correctly. Try entering both valid and invalid data into the cells, so you’ll know the settings are working as intended and the message appears when you expect.

Note:

  • After creating the drop down list, make sure it works the way you want.
  • Remove data validation – Select the cell containing the authentication data you want to delete, then Data> Data Validation and in the dialog box Data Validation, Press the button Clear All, then click OK, got it.

Add other types of data validation

The following table lists other types of data validation and how to add it to the worksheet.

Mission Steps to take
Restrict data entry to an integer within the limit
  1. Follow steps 1-2 above.
  2. From the list Allow, choose Whole number.
  3. In the box Data, select the desired type of limit. For example, set upper & lower limits, select between.
  4. Enter the minimum, maximum or specific values ​​allowed.

You can also enter formulas that return numeric values.

For example: You are validating the data in cell F1. Set a minimum deduction limit equal to 2 times the number of children in that cell, select greater than or equal to in the box Data > enter the formula: = 2 * F1 into the box Minimum.

Restrict data entry to fractions in bounds
  1. Follow steps 1-2 above.
  2. In the box Allow, choose Decimal.
  3. In the box Data, select the type of limit you want.
  4. Enter the minimum, maximum or specific values ​​allowed.

You can enter formulas that return numeric values. Example: Set a maximum limit for bonuses and commissions of 6% of salesperson’s salary in cell E1, select less than or equal to in Data> enter the formula = E1 * 6% in the Maximum box.

Note: To allow the user to enter a percentage, select Decimal in Allow, select the type of restriction you want in the Data box, enter the minimum, maximum, or specific value as a decimal number (e.g. .2), and then display the data validation cell as a percentage by Select the cell, then click Percent Style in the Number group on the Home tab.

Restrict data entry to a date in a date range
  1. Follow steps 1-2 above.
  2. In the box Allow, choose Date.
  3. In the box Data, select the type of restriction you want. For example, lets display the days after a specific date, select greater than.
  4. Enter the start date, end date allowed.

You can also enter formulas that return dates. For example: Set a time frame between today and 3 days after, select between in Data, enter = TODAY () into the box Start day, import = TODAY () + 3 into the End date.

Restrict data entry to the time in a time frame
  1. Follow steps 1-2 above.
  2. In Allow, choose Time.
  3. In Data, select a limit type. For example, allow a specific time before the time of the day, select less than.
  4. Enter the start time, end time or specific allowance. If you want to enter a specific time, use the time format hh: mm.

For example, set cell E2 at the start time (8:00 AM) and cell F2 with the end time (5:00 PM). You want to limit the meeting time between this time frame, select between In the Data box, enter E2 in the box Start time, import = F2 in the box End time.

Restrict text data entry to the selected length
  1. Follow steps 1-2 above.
  2. In the box Allow, choose Text Length.
  3. In the Data box, select a limit type. For example, allow a limit to a specified number of characters, select less than or equal to.
  4. In case you want to restrict the entry to 25 characters, select less than or equal to in Data, then type 25 into the box Maximum.
Limit calculations are allowed based on the content of another cell.
  1. Follow steps 1-2 above.
  2. In Allow, select the type of data you want.
  3. In the box Data, select the type of limit you want.
  4. In the boxes below the box Data, click the cell you want to use to select the data to allow.

For example, allow only items for the account if the result does not exceed the budget in cell E1, select Allow> Whole number, Data, less than or equal to, and Maximum> = = E1.

Examples of formulas in data validation in Excel

Note:

  • These examples use options Custom where you write the formula to set the condition. You do not need to worry about any data shown in the Data box because it has been disabled by Custom.
  • This screenshot is in Excel 2016 but its function is similar to Excel Online.
Guaranteed Recipe
Cells that contain the product ID (C2) always start with the standard “ID-” prefix and are at least 10 characters long

= AND (LEFT (C2,3) = “ID -“, LEN (C2)> 9)

Formula examples in data validation in Excel

The cell containing the product name (D2) contains only text

= ISTEXT (D2)

Example 2 of formulas in data validation in Excel

The cell containing the date of birth (B6) must be greater than the number of years set in cell B4

= IF (B6

Example 3 of formulas in data validation in Excel

All data in cell range A2: A10 contain distinct values

= COUNTIF ($ A $ 2: $ A $ 10, A2) = 1

Listing 4 on formulas in data validation in Excel

Note: You must enter the data validation formula for cell A2 first, and then copy A2 to A3: A10 so that the second argument to COUNTIF will match the current cell. Specifically, part A2) = will change to A3) = 1, A4) = 1 …

Make sure the email address item in cell B4 contains the @ symbol

= ISNUMBER (FIND (“@”, B4))

Example 5 of formulas in data validation in Excel

See more: excel learn excel microsoft excel
5 ★ 6 👨

597

.

Related Posts

Gear Reviews

Random

Xem thêm: Điều chỉnh nguyện vọng ở giai đoạn nước rút, làm sao để nhiều lợi thế?

Benefits when you use the Android operating system: Many customizations, the ability to intervene deeply as well as easily install APK files

Game Review

Random

This new martial arts work exclusively on TapTap, let me see a real and warm “jianghu” original

Chuyên mục: Kiến thức

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Check Also
Close
Back to top button