Data Validation Custom Formulas

This tutorial will demonstrate you how to create custom formulas in Data Validation in Excel and Google Sheets

Data Validation – Must Begin With – Excel

We can write a custom formula to ensure that the data in a cell begins with certain text.

1. Highlight the range required eg: B3:B8.
2. In the Ribbon, select Data > Data Tools > Data Validation.

3. Select Custom from the Allow drop-down box, and then type the following formula:

=EXACT(LEFT(B3,4),”FRU-“)

The formula uses 2 functions EXACT and LEFT to determine if the first 4 characters entered into the cell are identical to “FRU-”

4. If you want to add an input message for your user, click Input Message.

5. Make sure the tick box “Show input message when cell is selected” is checked and then type in the title and message you require.
6. If you want to add an error alert, click Error Alert.

7. You can change the Style from Stop to either Warning or Information if you require, and then type in your title and error message.
8. Click OK.
9. If you have used the Input Message option, a comment will appear on the screen informing the user of the rule.

10. Type “FRI-124” in cell B3. If you have selected Stop, the following message box will appear.

11. If you selected Warning, then this message box will appear. This allows you to continue if you determine that the data is correct.

12. If you have selected Information, then this message box will appear.

13. If you click OK, you will be allowed to continue with the incorrect data entered into the cell.
14. Click Cancel to exit the message or OK to enter the text into the cell.

Data Validation Allow Uppercase Only in Excel

We can write a custom formula to ensure that the data in a cell only allows upper case if text is entered into the cell.

1. Highlight the range required eg: B3:B8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Custom from the Allow drop-down box, and then type the following formula:

=EXACT(B3,UPPER(B3))

The formula uses 2 functions EXACT and UPPER to determine if the text entered into the cell is in uppercase. Cells with a mixture of number and text are considered text and the numbers are ignored in the rule.

4. Click OK.
5. Type “fru-124” in cell B3.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.
8. Type “123456” in cell B3.
9. This will be allowed as it is a number and not text.

In our next example, we will ensure that only uppercase text can be entered into the cell.

Data Validation Allow Uppercase Text Only in Excel

We can write a custom formula in Data Validation can be used to ensure that the data in a cell only allows upper case text

NOTE: if you enter information into a cell that begins with text but contains numbers, Excel will consider the information text.

1. Highlight the range required eg: B3:B8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Custom from the Allow drop-down box, and then type the following formula:

=AND(EXACT(B3,UPPER(B3)),ISTEXT(B3))

The formula uses 4 functions AND, EXACT, UPPER and TEXT to determine if the text entered into the cells is in Upper Case AND to determine if the information entered is actually text and not a pure number.

4. Click OK.
5. Type “fru-124” in cell B3.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.
8. Type “123456” in cell B3.
9. You will once again get the error message.
10. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Prevent Spaces in Excel

We can write a custom formula to ensure that no spaces are entered into the data entered in a range of cells.

1. Highlight the range required eg: B3:B8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Custom from the Allow drop-down box, and then type the following formula:

=B3=SUBSTITUTE(B3, ” ” , “”)

The formula uses the SUBSTITUTE Function to check that spaces do not exist.

4. Click OK.
5. Type “FRU – 124” in cell B4.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Prevent Duplicates in Excel

We can write a custom formula to prevent us from entering duplicate information into a range of cells.

1. Highlight the range required eg: B3:B8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Customer from the Allow drop-down box, and then type the following formula:

         =COUNTIF($B$3:$B$8,B3)<2

The formula uses the COUNTIF Function and uses ABSOLUTES in the range B3:B8 to ensure that this is the list that the COUNTIF Function looks at when it checks to see if there are any duplicate values.

4. Click OK.
5. Type “FRU-123” in cell D4.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Exists in List in Excel

We can write a custom formula ensure that only specific text is entered into a cell.

 

1. Highlight the range required eg: D3:D8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Custom from the Allow drop-down box, and then type the following formula:

         =COUNTIF($F$6:$F$8,D3)>0

The formula uses the COUNTIF function and uses ABSOLUTES in the range F3:F8 to ensure that this is the list that the COUNTIF function looks at when it checks to see that the correct text is being entered.

4. Click OK.
5. Type “Single” in cell D4.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Does not Exist in List in Excel

We can use a custom formula ensure that specific text is not entered into a cell.

1. Highlight the range required eg: C3:C8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Customer from the Allow drop-down box, and then type the following formula:

         =COUNTIF($F$6:$F$8,C3)=0

The formula uses the COUNTIF function and uses ABSOLUTES in the range F3:F8 to ensure that this is the list that the COUNTIF function looks at when it checks to see that the correct text is being entered.

4. Click OK.
5. Type “Beef” in cell C4.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Only Numbers are Entered in Excel

We can use a custom formula to ensure that only a number is entered into a cell.

1. Highlight the range required eg: F3:F8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Customer from the Allow drop-down box, and then type the following formula:

          =ISNUMBER(F3:F8)

The formula uses the ISNUMBER function to ensure that a number is entered into the cells in the range.

4. Click OK.
5. Type “nine” in cell F4.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Does not Exceed Value in Excel

We can use a custom formula ensure that the values entered into a cell do not exceed a specified value.

1. Highlight the range required eg: E3:E8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Customer from the Allow drop-down box, and then type the following formula:

         =E3<=$G$6

The formula an ABSOLUTE in the range G6 to ensure that this is the value that the rule checks when data is entered into E3.

4. Click OK.
5. Type “9” in cell E4.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Does not Exceed Total in Excel

We can use a custom formula ensure that the values entered into a range of cells do not exceed a specified total value for the range

1. Highlight the range required eg: F3:F8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Customer from the Allow drop-down box, and then type the following formula:

         =SUM($F$3:$F$8)<=$H$6

The formula uses the SUM Function and uses ABSOLUTES in the range F3:F8 to ensure that this is the list that the SUM Function looks at when it checks to see that the total of the range is not greater than the value entered in H6.

4. Click OK.
5. Type “40” in cell F4.
6. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel to exit the message or Retry to re-enter correct text into the cell.

Data Validation Weekday Dates Only in Excel.

We can use a custom formula to ensure that only weekdays are entered when dates are used in Excel.

1. Highlight the range required eg: G3:G8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Customer from the Allow drop-down box, and then type the following formula:

         =WEEKDAY(F3,2)<6

The weekday function checks to make sure that the day contained within the date is not a Saturday or a Sunday.

4. Amend the date in G5 to show a Saturday (eg 9^th May 2020).
5. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

7. Click Cancel or Retry to enter an alternative date.

Data Validation Future Date Only in Excel

We can create a custom formula to only allow the user to enter a date that is in the future.

1. Highlight the range required eg: G3:G8.
2. In the Ribbon, select Data > Data Tools > Data Validation.
3. Select Customer from the Allow drop-down box, and then type the following formula:

=G3>TODAY()

The formula uses the TODAY Function to check if the date entered into the cell is greater than today’s date.

4. Amend the date in G5 to yesterday.
5. If you have used the Error Alert option, your custom warning and error message will appear. If you have not used this option, the standard warning will appear.

6. Click Cancel or Retry to enter an alternative date.

 

Data Validation Must Begin With in Google Sheets

1. Highlight the range required eg: B3:B8.
2. In the Menu, select Data > Data Validation.

3. The cell range will already be filled in.

4. Select Custom Formula is from the Criteria drop down list.

5. Type in the formula.

=EXACT(LEFT(B3,4),”FRU-“)

6. Select either Show Warning or Reject Input if the data is invalid.

7. You can type in some Validation help text if you require.

8. Click Save.

9. Click in B3 to see the Validation help text

10. Type FRI-123
11. If you have selected Show Warning, the following message will appear.

12. Alternatively, if you have selected Reject Input on Invalid Data, you will be prevented from entering the data and the following message appears on the screen.

The rest of the custom formula examples in Google Sheets work in exactly the same way.