Chapter 38: Advanced Data Validation

Introduction

Advanced Data Validation in Excel helps ensure data accuracy by setting specific rules for what can be entered into cells. This chapter will cover advanced data validation techniques, including custom formulas for more complex scenarios.

1. List Validation

• Description: Allows users to select from a predefined list of items. This ensures that only values from the list can be entered.

2. Date and Time Constraints

• Description: Restricts input to dates or times within a specified range. Useful for managing deadlines or schedules.

3. Custom Validation with Formulas

Custom validation using formulas enables you to create tailored rules for cell entries. Below are solutions to various scenarios using custom formulas.

1. Allow Numbers Only

Formula: =ISNUMBER(A1)

1. Explanation: This formula ensures that only numeric values are allowed in the cell.
2. Allow Text Only

Formula: =ISTEXT(A1)

2. Explanation: This formula restricts cell entries to text only.
3. Allow Uppercase Only

Formula: =EXACT(A1, UPPER(A1))

3. Explanation: This formula ensures that the entered text is in uppercase. EXACT checks if the text is exactly equal to its uppercase version.
4. Allow Weekday Only

Formula: =WEEKDAY(A1, 2) <= 5

4. Explanation: This formula ensures that the date entered falls on a weekday (Monday to Friday). WEEKDAY returns the day of the week, and the <= 5 condition checks for weekdays.
5. Allow Contains Specific Text Only

Formula: =ISNUMBER(SEARCH(“specific text”, A1))

5. Explanation: This formula ensures that the cell contains the specified text. SEARCH looks for “specific text” within the cell content.
6. Allow Date in Next 30 Days Only

Formula: =AND(A1 >= TODAY(), A1 <= TODAY() + 30)

6. Explanation: This formula restricts the date to be within the next 30 days from today.
7. Allow Only Specific Year

Formula: =YEAR(A1) = 2024

7. Explanation: This formula ensures that the date entered is within the year 2024.
8. Allow Data Exists in List

Formula: =ISNUMBER(MATCH(A1, ListRange, 0))

8. Explanation: This formula ensures that the entered value exists in a predefined list. Replace ListRange with the range of your list.
9. Allow Data Must Begin With Specific Text

Formula: =LEFT(A1, LEN(“SpecificText”)) = “SpecificText”

9. Explanation: This formula ensures that the cell value begins with the specified text.
10. Allow Data Must Not Be in List

Formula: =ISNA(MATCH(A1, ListRange, 0))

10. Explanation: This formula ensures that the entered value is not in a predefined list. Replace ListRange with the range of your list.
11. Allow Data Unique Numbers

Formula: =COUNTIF(A:A, A1) = 1

11. Explanation: This formula ensures that the value entered is unique in the specified range.
12. Allow Data Between Two Dates

Formula: =AND(A1 >= DATE(2024, 1, 1), A1 <= DATE(2024, 12, 31))

12. Explanation: This formula restricts the date to be between January 1, 2024, and December 31, 2024.

4. Input Messages

• Description: Provides users with guidance or instructions when they select a cell. This message appears when the cell is active.

5. Error Alerts

• Description: Displays an error message when invalid data is entered into a cell. Helps prevent incorrect data from being saved.

6. Circle Invalid Data

• Description: Highlights cells with invalid data entries. Useful for quickly identifying and correcting errors.

Practice Assignment

1. Create a Data Validation Rule for Numbers Only:
   • Set up a cell to accept only numeric values.
2. Allow Text Only:
   • Configure a cell to accept only text entries.
3. Enforce Uppercase Text:
   • Ensure that text entries are in uppercase only.
4. Restrict to Weekdays Only:
   • Set up validation so that only weekdays are accepted as dates.
5. Validate Presence of Specific Text:
   • Ensure the cell contains a specified text.
6. Allow Dates within the Next 30 Days:
   • Restrict a date entry to the next 30 days.
7. Restrict to a Specific Year:
   • Allow only dates within a specific year.
8. Ensure Data Exists in a List:
   • Validate that the cell value matches an item from a list.
9. Data Must Begin with Specific Text:
   • Set up validation to ensure that data starts with given text.
10. Data Must Not Be in List:
    • Validate that the cell value is not in a predefined list.
11. Unique Numbers Only:
    • Configure the cell to accept only unique values.
12. Data Between Two Dates:
    • Restrict data to be within a specific date range.

Pro Tip

• Advanced Data Validation Shortcuts: Use Alt + D + L to quickly open the Data Validation dialog. To copy validation rules across cells, use the Format Painter tool or paste special.