Home>Blogs>Excel Tips and Tricks>Excel Tips: Age data Validation Tricks for HR Pros
Data Validation for Employee Age
Excel Tips and Tricks

Excel Tips: Age data Validation Tricks for HR Pros


Are you looking to ensure accurate age data in your Excel sheets, particularly for HR purposes? Our recent YouTube video, titled “Excel Tips: Age Data Validation Tricks for HR Pros!” offers a comprehensive guide on this topic. The video focuses on implementing Age Data Validation for employee data, ensuring that only ages between 18 to 60 are entered.

Understanding the Basics

Before diving into the steps, let’s understand the data used in this example. We worked with a simple Excel sheet containing three columns: Employee Name (Column A), Supervisor Name (Column B), and Age (Column C). The key focus was on Column C, where we applied the data validation rule.

Employee Data
Employee Data

Step-by-Step Guide to Age Data Validation

Step 1: Selecting the Age Column

Firstly, select the range of cells in Column C (the Age column). This is where the age data of employees will be entered.

Step 2: Accessing Data Validation

Next, press Alt + D + L shortcut key on your keyboard. This shortcut opens the Data Validation window in Excel, a crucial tool for setting validation rules.

Step 3: Setting the Validation Criteria

In the Data Validation window, go to the ‘Settings’ tab. Here, follow these sub-steps:

  • Select “Whole Number” in the “Allow” dropdown. This ensures that only whole numbers are entered.
  • Choose “between” in the “Data” dropdown. This sets the condition for the age range.
  • Enter 18 in the minimum field and 60 in the maximum field. This restricts age entry to between 18 and 60 years.
Data Validation window
Data Validation window

Step 4: Customizing the Error Alert

Move to the ‘Error Alert’ tab. Here, you can customize the alert that appears when incorrect data is entered:

  • Choose “Stop” in the “Style” dropdown. It will prevent invalid data entry.
  • In the Title box, enter the “Incorrect Age”.
  • In the Error message box, type “Enter the Age between 18 to 60”.
Error Alert tab
Error Alert tab

Step 5: Finalizing the Validation

Click on ‘OK’ to apply this data validation. Now, age column will only allow ages between 18 to 60. If you enter the incorrect age, then it will display below error message.

error message for incorrect values
error message for incorrect values

Wrapping Things Up

So, there you have it! Setting up age validation in Excel is pretty simple and super useful, especially for HR folks. I really suggest giving it a try. You’ll see how much easier it makes your work. Plus, when you’re dealing with people’s info, you want to make sure everything is spot on, right? That’s where this neat Excel trick comes in handy.

Give It a Go!

Now it’s your turn. Crack open Excel and put what you’ve learned into practice. Trust me, it’s easier than it sounds. And the more you use it, the better you’ll get. Plus, it’s always good to know you’re keeping your data accurate and tidy.

Stay Tuned for More

And hey, don’t forget to keep an eye out for more handy tips like this. We’ve got loads of tricks up our sleeves to help make your work life a breeze. So, stay tuned, and let’s keep making work stuff easier, together!

Visit our YouTube channel to learn step-by-step video tutorials


Watch the step-by-step video tutorial:

Click here to download the practice file


Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!