Excel for quartetpress.com 365 Excel for quartetpress.com 365 for Mac Excel for the website Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm nhâm thìn Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less

Use data validation to lớn restrict the type of data or the values that users enter into a cell. One of the most common data validation uses is khổng lồ create a drop-down list.

Bạn đang xem: Học excel


*

Try it!

Select the cell(s) you want to lớn create a rule for.

Select Data >Data Validation.

*

On the Settings tab, under Allow, select an option:

Whole Number - to restrict the cell khổng lồ accept only whole numbers.

Decimal - to restrict the cell khổng lồ accept only decimal numbers.

List - lớn pick data from the drop-down list.

Date - to restrict the cell to lớn accept only date.

Time - khổng lồ restrict the cell to lớn accept only time.

Text Length - to lớn restrict the length of the text.

Custom – for custom formula.

 Under Data, select a condition.

Set the other required values based on what you chose for Allow and Data.

Select the Input Message tab and customize a message users will see when entering data.

Select the Show input đầu vào message when cell is selected checkbox lớn display the message when the user selects or hovers over the selected cell(s).

Select the Error Alert tab lớn customize the error message and to choose a Style.

Select OK.

Now, if the user tries lớn enter a value that is not valid, an Error Alert appears with your customized message.


Download our examples

tải về an example workbook with all data validation examples in this article

If you"re creating a sheet that requires users to lớn enter data, you might want khổng lồ restrict entry to lớn a certain range of dates or numbers, or make sure that only positive whole numbers are entered. Excel can restrict data entry to certain cells by using data validation, prompt users to lớn enter valid data when a cell is selected, và display an error message when a user enters invalid data.

Restrict data entry

Select the cells where you want lớn restrict data entry.

In the Allow box, select the type of data you want lớn allow, and fill in the limiting criteria and values.


Note: The boxes where you enter limiting values will be labeled based on the data and limiting criteria that you have chosen. For example, if you choose Date as your data type, you will be able to enter limiting values in minimum & maximum value boxes labeled Start Date và End Date.


Prompt users for valid entries

When users click in a cell that has data entry requirements, you can display a message that explains what data is valid.

Select the cells where you want to prompt users for valid data entries.

On the Input Message tab, select the Show đầu vào message when cell is selected kiểm tra box.

In the Title box, type a title for your message.

In the Input message box, type the message that you want lớn display.

Display an error message when invalid data is entered

If you have data restrictions in place và a user enters invalid data into a cell, you can display a message that explains the error.

Select the cells where you want khổng lồ display your error message.

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

In the Error message box, type the message that you want lớn display if invalid data is entered.

Do one of the following:

To

On the Style pop-up menu, select

Require users lớn fix the error before proceeding

Stop

Warn users that data is invalid, and require them lớn select Yes or No khổng lồ indicate if they want lớn continue

Warning

Warn users that data is invalid, but allow them khổng lồ proceed after dismissing the warning message

Important


Add data validation to lớn a cell or a range


Note: The first two steps in this section are for adding any type of data validation. Steps 3-7 are specifically for creating a drop-down list. 


Select one or more cells lớn validate.

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

On the Settings tab, in the Allow box, select List.

In the Source box, type your các mục values, separated by commas. For example, type Low,Average,High.

Make sure that the In-cell dropdown check box is selected. Otherwise, you won"t be able to see the drop-down arrow next to lớn the cell.

To specify how you want khổng lồ handle blank (null) values, select or clear the Ignore blank kiểm tra box.

Test the data validation lớn make sure that it is working correctly. Try entering both valid and invalid data in the cells to lớn make sure that your settings are working as you intended and your messages are appearing when you expect.


Notes: 

Remove data validation - Select the cell or cells that contain the validation you want to delete, then go lớn Data > Data Validation & in the data validation dialog press the Clear All button, then click OK.


The following table lists other types of data validation và shows you ways to showroom it to your worksheets.

To vày this:

Follow these steps:

Restrict data entry to whole numbers within limits.

Xem thêm: Hoạt Hình Madagascar 3 : Thần Tượng Châu Âu, Xem Phim Madagascar 3: Europes Most Wanted

Follow steps 1-2 above.

From the Allow list, select Whole number.

In the Data box, select the type of restriction that you want. For example, khổng lồ set upper & lower limits, select between.

Enter the minimum, maximum, or specific value khổng lồ allow.

You can also enter a formula that returns a number value.

For example, say you"re validating data in cell F1. Khổng lồ set a minimum limit of deductions to two times the number of children in that cell, select greater than or equal to in the Data box và enter the formula, =2*F1, in the Minimum box.

Restrict data entry to a decimal number within limits.

Follow steps 1-2 above.

In the Allow box, select Decimal.

In the Data box, select the type of restriction that you want. For example, to lớn set upper và lower limits, select between.

Enter the minimum, maximum, or specific value to allow.

You can also enter a formula that returns a number value. For example, to set a maximum limit for commissions và bonuses of 6% of a salesperson"s salary in cell E1, select less than or equal to in the Data box và enter the formula, =E1*6%, in the Maximum box.


Note: To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style

*
in the Number group on the Home tab.

Restrict data entry to a date within range of dates.

Follow steps 1-2 above.

In the Allow box, select Date.

In the Data box, select the type of restriction that you want. For example, to lớn allow dates after a certain day, select greater than.

Enter the start, end, or specific date to allow.

You can also enter a formula that returns a date. For example, to lớn set a time frame between today"s date và 3 days from today"s date, select between in the Data box, enter =TODAY() in the Start date box, & enter =TODAY()+3 in the End date box.

Restrict data entry lớn a time within a time frame.

Follow steps 1-2 above.

In the Allow box, select Time.

In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select less than.

Enter the start, end, or specific time to allow. If you want khổng lồ enter specific times, use the hh:mm time format.

For example, say you have cell E2 set up with a start time (8:00 AM), and cell F2 with an end time (5:00 PM), and you want lớn limit meeting times between those times then select between in the Data box, enter =E2 in the Start time box, và then enter =F2 in the End time box.

Restrict data entry to lớn text of a specified length.

Follow steps 1-2 above.

In the Allow box, select Text Length.

In the Data box, select the type of restriction that you want. For example, to lớn allow up to lớn a certain number of characters, select less than or equal to.

In this case we want lớn limit entry khổng lồ 25 characters, so select less than or equal to in the Data box & enter 25 in the Maximum box.

Calculate what is allowed based on the nội dung of another cell.

Follow steps 1-2 above.

In the Allow box, select the type of data that you want.

In the Data box, select the type of restriction that you want.

In the box or boxes below the Data box, click the cell that you want lớn use to specify what is allowed.

For example, to lớn allow entries for an tài khoản only if the result won"t go over the budget in cell E1, select Allow >Whole number, Data, less than or equal to, và Maximum >= =E1.

To make sure that

Enter this formula

The cell that contains a sản phẩm ID (C2) always begins with the standard prefix of "ID-" & is at least 10 (greater than 9) characters long.

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

*

The cell that contains a sản phẩm name (D2) only contains text.

=ISTEXT(D2)

*

The cell that contains someone"s birthday (B6) has to lớn be greater than the number of years phối in cell B4.

Bài viết liên quan