Creating Your First Validation Rules

Opening the Data Validation Dialog

All validation rules are created in the same place. Here's how to get there:

1. Select the cell or range you want to restrict;
2. Go to the Data tab in the ribbon;
3. Click Data Validation in the Data Tools group;
4. The dialog opens with three tabs: Settings, Input Message, and Error Alert — for now, work in Settings.

Setting the Rule

In the Settings tab you'll find the Allow dropdown — this is where you choose the validation type. Each type reveals additional fields below it.

List

1. Select the Status column cells (M2:M51);
2. Open Data Validation → Settings;
3. In Allow, choose List;
4. In the Source field, type: Open,Closed,Pending;
5. Make sure In-cell dropdown is checked;
6. Click OK.

A dropdown arrow will now appear in each cell. Only these three values are accepted.

Whole Number

1. Select the Quantity column cells (H2:H51);
2. Open Data Validation → Settings;
3. In Allow, choose Whole number;
4. In Data, choose greater than;
5. In Minimum, enter 0;
6. Click OK.

This blocks decimals, zeros, and negative numbers.

Decimal

1. Select the Discount % column cells (J2:J51);
2. Open Data Validation → Settings;
3. In Allow, choose Decimal;
4. In Data, choose between;
5. Set Minimum to 0 and Maximum to 30;
6. Click OK.

Values like –5 or 45 will be rejected; 15.5 is accepted.

Date

1. Select the Order Date column cells (B2:B51);
2. Open Data Validation → Settings;
3. In Allow, choose Date;
4. In Data, choose greater than or equal to;
5. In Start date, enter 01/01/2026;
6. Click OK.

Any date before January 1, 2026 will be blocked.

Task

The first four rules above are already applied as examples. Your task is to add the fifth one independently:

Salesperson column → apply a Text Length rule:

• Allow: Text length.
• Data: between.
• Minimum: 2.
• Maximum: 50.

Once done, test all five rules by trying to enter invalid values in each column:

• Type Cancelled in Status.
• Enter 0 or -3 in Quantity.
• Enter 85 in Discount %.
• Enter a date from 2025 in Order Date.
• Type a single letter in Salesperson.

Observe the error that appears — it's Excel's default generic message. Notice it gives no context about what went wrong or what the user should do instead.