How to apply multiple data validation in one cell in Excel worksheet?
In an Excel worksheet, it is quite common to apply a single data validation rule to a cell. This helps maintain data consistency and accuracy. However, there are scenarios where you might need to enforce several validation criteria within the same cell, such as allowing either a valid number or a value from a specific list, or combining specific text requirements with allowed date ranges. Handling these more complex data validation requirements in Excel can help you better control your data input processes, prevent errors, and improve data quality.
Below, this article walks you through different practical examples for enforcing multiple data validation rules within a single cell in Excel. Each example covers a unique scenario, so you can select the approach that best fits your specific needs. In addition, alternative methods such as using VBA are also introduced for cases that require more flexibility or advanced logic.
Apply multiple data validation in one cell (Example 1)
Apply multiple data validation in one cell (Example 2)
Apply multiple data validation in one cell (Example 3)
Apply multiple data validation using VBA (Advanced)
Apply multiple data validation in one cell (Example 1)
Suppose you want to set up a cell so that it can only accept values that meet either of two criteria:
- If the entered value is a number, it must be less than 100.
- If it is a text entry, the text must exist in a specific list (for example, the range D2 to D7).
This situation is often encountered when you need to collect either quantitative codes or predefined categorical answers within the same field. By combining validation rules, you avoid having separate fields for numbers and text, improving clarity and efficiency.
1. Select the cell or range where you want to enable multiple data validation criteria. Then, on the Data tab, click Data Validation > Data Validation from the ribbon, as shown below:
2. In the Data Validation dialog, go to the Settings tab and configure as follows:
- (1.) From the Allow drop-down, select Custom.
- (2.) In the Formula field, enter the following formula: =OR(A2<$C$2,COUNTIF($D$2:$D$7,A2)=1)
Note: In this formula, A2 is the address of the cell to validate, C2 contains the maximum allowed value, and D2:D7 lists the permitted text entries. Update these references to match your worksheet as needed.
3. Click OK to apply. Now, the selected cells will only accept entries that are either numbers less than 100 or text strings found in D2:D7. If a user attempts to enter a value that does not meet either condition, Excel displays a warning prompt, so users are immediately notified of invalid entries.
This method is ideal for straightforward scenarios with clear rule boundaries. However, for more nuanced or interactive requirements, such as conditional prompts or multi-step logic, a formula-based data validation may have limitations. In such cases, the VBA method introduced below can provide more flexibility.
Apply multiple data validation in one cell (Example 2)
In this scenario, you may want to only allow data entry if it meets either of these two conditions:
- The entered value is the exact text "Kutools for Excel"
- The entered value is a date falling between 12/1/2017 and 12/31/2017
This type of multiple validation is practical when your survey or data form requires either a confirmation code (a precise text string) or a date within a project range.
1. Open the Data Validation dialog box for your target cell(s). In the dialog, carry out these steps:
- (1.) Go to the Settings tab.
- (2.) Choose Custom from the Allow drop-down list.
- (3.) Type this formula into the Formula area: =OR(A2=$C$2,AND(A2>=DATE(2017,12,1), A2<=DATE(2017,12,31)))
Note: Here, A2 refers to the validation cell, C2 should contain the target text "Kutools for Excel", and the date range is defined with DATE(2017,12,1) and DATE(2017,12,31). Adjust references according to your sheet setup.
2. Confirm by clicking OK. The cell(s) will now only allow the specified text or a date within the defined range. Any other input type or text outside of those boundaries will be blocked, providing immediate feedback as shown here:
This approach works well for strict input scenarios where only exact match or fixed-date values are acceptable. However, if your validation involves complex dependencies, calculations, or user interaction, consider exploring a VBA-based solution for greater control.
Apply multiple data validation in one cell (Example 3)
For the third example, let's consider a situation where the cell should only allow entries with specific start text and corresponding character counts:
- The cell must start with "KTE" and be exactly 6 characters long
- Or start with "www" and be exactly 10 characters long
Such criteria are common when enforcing format standards for codes or URLs. Applying character length and prefix checks greatly reduces entry mistakes.
To address this, set up data validation with the below formula:
1. Open the Data Validation dialog. In the Settings, complete these steps:
- (1.) Choose the Settings tab.
- (2.) Select Custom from the Allow drop-down.
- (3.) In the Formula field, enter: =OR(AND(LEFT(A2,3)="KTE",LEN(A2)=6),AND(LEFT(A2,3)="www",LEN(A2)=10))
Note: Replace A2 with your actual cell reference if needed. You may also vary "KTE", "www", and character counts for your specific context.
2. Click OK. The cell now only accepts values matching your prefix and length rules. Input violating either condition will trigger a validation error, as shown:
Tip: If you have other multiple-criteria validations in mind, you can create custom formulas using Excel's built-in functions to suit your requirements.
A limitation of formula-based validation is that it may become difficult to manage or edit when rules get more complex or interactive—for example, if you want to show custom error messages or handle dynamically changing criteria. In such situations, employing a VBA solution can greatly enhance flexibility.
Apply multiple data validation using VBA (Advanced)
In cases where formula-based data validation is not sufficient—such as requiring validation based on a combination of factors, enforcing rules that change based on other cell values, or providing custom real-time messages—you can use VBA (Visual Basic for Applications) macros to apply advanced or dynamic validation rules within a cell.
Typical scenarios include:
- Validating input based on more than two simultaneous conditions
- Allowing user interaction, such as pop-up messages with detailed guidance
- Automatically reverting invalid data and providing custom instructions
Below is a VBA solution example where data entry in B2 must meet either:
- Be a whole number between 1 and 50
- OR be one of the allowed words in range D2:D5
Note: You can modify criteria, target ranges, or validation logic in the code as needed.
1. Press Alt+F11 to open the Visual Basic for Applications editor. In the VBA editor, double-click the worksheet you want to add multiple data validation in the Project pane. Then copy the following macro into the code window for that sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ValidList As Range
Dim InputValue As Variant
Dim IsValid As Boolean
Dim xTitleId As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Only validate B2 (you can set this to your desired cell or range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
InputValue = Target.Value
Set ValidList = Range("D2:D5") ' Change as needed
IsValid = False
' Check for whole number between 1 and 50
If IsNumeric(InputValue) And InputValue = Int(InputValue) Then
If InputValue >= 1 And InputValue <= 50 Then
IsValid = True
End If
End If
' Check if input matches allowed list
If WorksheetFunction.CountIf(ValidList, InputValue) > 0 Then
IsValid = True
End If
If Not IsValid Then
MsgBox "Entry must be an integer between 1 and 50 OR one of the values listed in D2:D5.", vbExclamation, xTitleId
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If
End If
End Sub2. Try entering values into cell B2. If you enter a whole number between 1 and 50, or a word found in D2:D5, the input remains. If not, a message appears and the invalid entry is immediately removed. You can adjust the target cell(s) and the valid values range within the VBA to match your requirements.
Tips and Troubleshooting:
- Always save your workbook before running VBA, as unintended code can cause data loss.
- If your worksheet has multiple validation cells, you can modify the code to validate any range, not just B2.
- If the code does not run, double-check that macros are enabled and the code is in the correct worksheet.
- You can enhance the code to provide different messages or log invalid entries, depending on your needs.
VBA-based solutions are highly flexible and ideal for advanced validation demands. However, keep in mind that macros require users to enable VBA in their Excel environment, and may not be available in all security settings.
In summary, when working with multiple validation rules for a cell in Excel, you can select from formula-based methods for most straightforward requirements, or leverage VBA for more dynamic and sophisticated data validation workflows. Carefully consider which approach best matches your goals, data complexity, and user environment for optimal results.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in