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.

data validation must begin with

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

data validation data ribbon

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

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

data validation dialog box 1

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

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

data validation dialog box - input message

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

data validation dialog box - error alert

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

data validation message bo

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

data validation incorrect input stop

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

data validation incorrect input waring

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

data validation incorrect input informtion

  1. If you click OK, you will be allowed to continue with the incorrect data entered into the cell.
  2. 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.

data validation exact

  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))

data validation exact dialog

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.

  1. Click OK.
  2. Type “fru-124” in cell B3.
  3. 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.

data validation exact error

  1. Click Cancel to exit the message or Retry to re-enter correct text into the cell.
  2. Type “123456” in cell B3.
  3. 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.

data validation exact and

  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))

data validation exact and settings

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.

  1. Click OK.
  2. Type “fru-124” in cell B3.
  3. 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.

data validation exact error

  1. Click Cancel to exit the message or Retry to re-enter correct text into the cell.
  2. Type “123456” in cell B3.
  3. You will once again get the error message.
  4. 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.

data validation substitute

  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, ” ” , “”)

data validation substitute settings

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

  1. Click OK.
  2. Type “FRU – 124” in cell B4.
  3. 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.

data validation subsitute error

  1. 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.

data validation countif

  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

data validation countif settings

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.

  1. Click OK.
  2. Type “FRU-123” in cell D4.
  3. 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.

data validation countif error

  1. 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.

data validation does not exist

 

  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

data validation exists in list settings

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.

  1. Click OK.
  2. Type “Single” in cell D4.
  3. 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.

data validation exists in list error

  1. 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.

data validation does not exist

  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

data validation does not exist settings

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.

  1. Click OK.
  2. Type “Beef” in cell C4.
  3. 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.

data validation does not exist error

  1. 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.

data validation isnumber

  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)

data validation is number settings

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

  1. Click OK.
  2. Type “nine” in cell F4.
  3. 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.

data validation isnumber error

  1. 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.

data validation does not exceed

  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

data validation does not exceed settings

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.

  1. Click OK.
  2. Type “9” in cell E4.
  3. 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.

data validation does not exceed errror

  1. 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

data validation does not exceed total

  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

data validatino does not exceed total settings

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.

  1. Click OK.
  2. Type “40” in cell F4.
  3. 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.

data validation does not exceed total error

  1. 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.

data validation weekdays only

  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

data validation weekdays only settings

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

  1. Amend the date in G5 to show a Saturday (eg 9th May 2020).
  2. 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.

data validation weekdays only error

  1. 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.

data validation future date only

  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()

data validation future date only settings

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

  1. Amend the date in G5 to yesterday.
  2. 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.

data validation future date only error

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

 

Data Validation Must Begin With in Google Sheets

data validation exact google sheets

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

data validation exact google sheets settings

  1. The cell range will already be filled in.

data vlidation exact google sheets cell range

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

data validation exact google sheets formula

  1. Type in the formula.

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

data validation exact google sheets formula 1

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

data validation google sheets show warning

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

data validation google sheets validation text

  1. Click Save.

data validation exact google sheets validation

  1. Click in B3 to see the Validation help text

data validation exact google sheets message

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

data validation exact google sheets warning

  1. 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.

data validation exact google sheets stopbox

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