How to Use Data Validation in Excel

How to Use Data Validation in Excel

1. Set up a validation list sheet

• Create a separate area or sheet that holds the approved values for your drop-downs
• In the video, this includes lists such as:
  • Technician names
  • Cities
  • Job types
  • System types
• Keeping these lists separate makes your workbook easier to manage and update over time

2. Create a drop-down list for technician names

• Select the cells where technicians will be entered
• Go to Data
• Select Data Validation
• In the validation settings, choose List
• For the source, point to the range on the validation list sheet that contains the technician names
• Once the first cell works correctly, copy and paste the validation down the rest of the column

3. Repeat the process for other drop-down fields

• Apply the same Data Validation setup to the other columns
• In the video, this is repeated for:
  • Cities
  • Job types
  • System types
• Each field gets its own approved list so users can only choose valid values

4. Test the drop-down restriction

• Try entering a value that is not in the approved list
• In the example, typing a city that is not part of the drop-down triggers an error
• This confirms the field is restricted to approved entries only

5. Add numeric validation for labor hours

• Select the labor hours column
• Go to Data Validation
• Choose Decimal
• Set the rule to Between
• Enter the minimum and maximum allowed values
• In the video, labor hours are restricted to a valid range so users cannot enter unrealistic values

6. Test the labor-hours rule

• Enter a valid number to confirm it is accepted
• Then enter a number outside the allowed range
• Excel blocks the invalid entry and shows an error message
• This helps protect operational data from outliers and typing mistakes

7. Add numeric validation for customer ratings

• Select the customer rating column
• Open Data Validation
• Choose Decimal
• Set the allowed range
• In the video, the rating is limited to a scale such as 1 to 5
• This prevents invalid scores such as 0, 6, or numbers above the rating scale

8. Test the customer-rating rule

• Enter a valid rating to confirm it works
• Enter invalid values such as:
  • A value below the minimum
  • A value above the maximum
  • A decimal above the allowed ceiling
• Excel rejects the entry and preserves the quality of the rating data

9. Add a custom error alert

• In the Data Validation window, go to the error alert settings
• Create a message that tells users exactly what is allowed
• In the video, the example message explains that the customer rating must be between 1 and 5
• This makes the workbook easier for non-technical users to complete correctly

10. Explore other validation types available

• The video also points out that Data Validation can be used for more than lists and number ranges
• Other available validation types include:
  • Dates
  • Times
  • Text length
  • Custom logic
• This gives you more control over how data is entered in each field

11. Use validation to support more advanced logic

• The video explains that Data Validation can become even more powerful when combined with logic from functions like IF
• This allows you to create rules based on another selection
• Example from the video:
  • If one system type is selected, labor hours can follow one range
  • If another system type is selected, labor hours can follow a different range
• This is the foundation for more advanced conditional input rules

12. Plan for dynamic and dependent drop-downs

• As your workbook grows, you may need validation lists that update automatically when new items are added
• You may also want dependent drop-downs, where the second list changes based on the first selection
• In the video, this is explained with examples like limiting job types based on city or service availability
• This makes your workbook more scalable and easier to maintain

13. Use data validation to protect analysis quality

• The final takeaway in the video is that data validation helps prevent small entry errors from creating large reporting problems
• A simple typo or incorrect number can distort averages, dashboards, and KPI calculations
• By controlling what users can enter, you make the dataset more reliable and easier to analyze