How to Limit Excel Cells to Only Be Filled with Specific Text
Another possible case is that we want to limit Excel cells can only be filled with certain text, for example, city name, country name, or product name. This is useful to ensure that the data we enter is consistent and there are no typos.
To limit Excel cells that can only be filled with certain text, we can use Data Validation with List type. Here are the steps:
- Select (selection) cells or ranges that you want to limit input to only certain text. For example, we want to limit column C which contains city names can only be filled with predefined city names.
- Click the “Data” menu on the Excel ribbon, then click “Data Validation” in the “Data Tools” group. Alternatively, we can also use the keyboard shortcut Alt + A + V + V.
- In the Data Validation dialog box, select the “Settings” tab, then select “List” in the “Allow” drop-down list.
- In the “Source” box, enter a list of texts that are allowed to be entered into cells, separated by commas. For example, if we want to limit the city name to only be filled with Jakarta, Surabaya, Bandung, or Yogyakarta, then enter Jakarta, Surabaya, Bandung, and Yogyakarta in the Source box. Alternatively, we can also select the range that contains the text list, for example, A1:A4.
- Click OK to apply the Data Validation.
After that, try entering text into column C. If the text entered is one of the specified text lists (e.g. Jakarta, Surabaya, Bandung, or Yogyakarta), then Excel will accept that input. However, if the entered text is not one of the specified text lists, then Excel will display the error message as before.
In addition, Excel will also display a small arrow to the right of the cell that has the Data Validation type List. If we click the arrow, then Excel will display a list of text that we can choose to fill the cell. This is useful to make it easier for us to select the appropriate text without having to type it.
How to Restrict Excel Cells to Only Fill with Date Format
Another case that may occur is that we want to limit Excel cells can only be filled with date format only, for example, to fill birth data, transaction data, or other data related to time. This is useful to ensure that the data we enter is valid and matches the date format we want.
To limit Excel cells can only be filled with date format, we can use Data Validation with Date type. Here are the steps:
- Select (select) cells or ranges that you want to limit input to date format only. For example, we want to limit column D which contains birth data to date format only.
- Click the “Data” menu on the Excel ribbon, then click “Data Validation” in the “Data Tools” group. Alternatively, we can also use the keyboard shortcut Alt + A + V + V.
- In the Data Validation dialog box, select the “Settings” tab, then select “Date” in the “Allow” drop-down list.
- In the “Minimum” and “Maximum” boxes, enter the date range allowed to be inserted into the cell. For example, if we want to limit birth data between January 1, 1990, and December 31, 2020, then enter 1/1/1990 in the Minimum box and 31/12/2020 in the Maximum box. If we don’t want to limit the date range, then leave the Minimum and Maximum boxes blank.
- Click OK to apply the Data Validation.
After that, try entering dates into column D. If the dates entered are within the specified range (e.g. between January 1, 1990, and December 31, 2020), then Excel will accept those inputs. However, if the date entered is outside the specified range, then Excel will display the error message as before.
In addition, Excel will also display a small calendar to the right of the cell that has Data Validation type Date. If we click the calendar, then Excel will display a selection of dates that we can choose to fill in the cell. This is useful to make it easier for us to choose the appropriate date without having to type it.