Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Loading Page Content...
Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...
Beyond built-in limits, Excel allows you to write custom formulas to enforce sophisticated business constraints (such as preventing duplicate entries or ensuring total percentages equal 100%). Furthermore, understanding the three distinct Error Alert Styles determines whether users are strictly blocked or merely cautioned.
When a user violates a validation rule, Excel displays an Error Alert modal. You can choose from three styles under the Error Alert tab:
+-----------------------------------------------------------------------------------+ | 1. STOP (Red Octagon with White 'X') | | Strict Enforcement. The user CANNOT bypass this modal. They must either: | | - Click "Retry" to enter a compliant value, or | | - Click "Cancel" to restore the previous valid entry. | +-----------------------------------------------------------------------------------+ | 2. WARNING (Yellow Triangle with Exclamation Mark) | | Moderate Enforcement. Asks: "Continue? [Yes] [No] [Cancel]". | | If the user clicks "Yes", Excel accepts the invalid value! | +-----------------------------------------------------------------------------------+ | 3. INFORMATION (Blue Circle with 'i') | | Gentle Notification. Informs the user of the deviation with [OK] [Cancel]. | | Clicking "OK" accepts the invalid entry unconditionally. | +-----------------------------------------------------------------------------------+
Selecting Allow: Custom enables you to write formulas that evaluate to TRUE or FALSE:
To prevent users from entering duplicate Invoice Numbers or Employee IDs in column A ('A2:A500'):
How it works: If the entered value already exists in the column, COUNTIF returns 2. Since 2 is not <= 1, Excel triggers the Stop modal immediately!
To ensure that department spending allocations across row cells 'B2:E2' do not exceed 100%:
Ensure clean data entry without accidental spaces:
What happens if you apply Data Validation to a sheet that already contains bad data? Existing entries are not automatically deleted.
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.
| Previous Lesson | Next Lesson |
|---|---|
| Creating In-Cell Dropdown Lists | Create an Employee Attendance Tracker Setup |
Test your understanding of this lesson with 5 questions. Each question has one correct answer.
Practice in live mentor-led sessions with real projects, mock tests, and verifiable certification at MSK Institute.