top of page

You are learning Data Validation in MS Excel

How to use data validation with custom error icons in Excel?

Unfortunately, Excel doesn't currently allow embedding custom icons within data validation error messages. The error message format is pre-defined and offers limited customization options.

However, you can achieve a similar effect using a combination of Data Validation and Conditional Formatting:

1. Set up Data Validation Rule:
- Select the cell(s) where you want to apply data validation.
- Go to the "Data" tab and click "Data Validation" in the "Data Tools" group.
- Define your validation criteria (e.g., Allow: List, Numbers between, etc.)

2. Create Conditional Formatting Rule for Error Indication:
- Select the same cell(s) where you applied data validation.
- Go to the "Home" tab and click "Conditional Formatting" in the Styles group.
- Choose "New Rule..." from the dropdown menu.
- Select "Use a formula to determine which cells to format" under "Select a Rule Type."

3. Formula for Error Indication:
- In the "Format values where this formula is true" box, enter a formula that checks if the data validation is successful. Here are two options:
- For simple validation rules: `=NOT(ISVALID(A1))` (Replace A1 with your cell reference). This formula checks if the data entered is NOT valid based on the validation rule.
- For more complex scenarios, you can use a custom formula based on your specific validation criteria.

4. Set Error Formatting:
- Once you have the formula in place, click "Format" to define how you want to highlight invalid entries. You can use features like:
- Fill color (e.g., red for error)
- Border style (e.g., thicker border)
- Font color or icon sets (limited options but can provide visual cues)

5. Finalize:
- Click "OK" on both the Conditional Formatting and Data Validation windows.

With this setup, when a user enters invalid data that triggers the data validation error, the conditional formatting will also apply, visually indicating the error with your chosen format (e.g., red fill). This approach doesn't embed a custom icon, but it provides a clear visual cue for invalid entries.

bottom of page