How to Calculate Age in Excel for Entire Column

In this tutorial, I am going to show you 6 simple methods to calculate age in Excel for an entire column. You can quickly use these methods even in large datasets to perform subtraction of multiple cells. Throughout this tutorial, you will also learn some important Excel tools and functions which will be very useful in any Excel-related task.

How to Calculate Age in Excel for Entire Column: 6 Simple Methods

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 3 columns. Initially, we formatted all the cells containing date values in Date format and all the other values in General format. For all the datasets, we have 3 unique columns which are Name, Birth Date, and Age. Although we may vary the number of columns later on if that is needed.

1. Using TODAY Function

The TODAY function in Excel can generate the current date and update it each time we open a worksheet. We can use this function alongside the Fill Handle to calculate age in Excel for an entire column. Let us see how to do this.

Steps:

• First, go to cell D5 and insert the following formula:

• Now, press Enter and this will calculate the age inside cell D5.
• Finally, copy this formula to the cells below using Fill Handle.

Read More: How to Calculate Age in Excel in dd/mm/yyyy

2. Utilizing YEARFRAC Function

Using fractional years between two dates, the YEARFRAC function in Excel generates a decimal number. Let us see how to use this function to calculate the age for an entire column.

Steps:

• To begin with, double-click on cell D5 and enter the below formula:

• Next, press the Enter key and you should get the age for Paul.
• Now, drag the Fill Handle down to copy the formula to the entire column.

Read More: How to Calculate Age on a Specific Date with Formula in Excel

3. Applying DATEDIF Function

The DATEDIF function gives back the difference between two separate date values as years, months, or even days. In the following steps, we will see how to apply this function to calculate age in Excel for an entire column.

Steps:

• To begin this method, double-click on cell D5 and insert the formula below:

• Next, press the Enter key and consequently, this will find the age for Paul inside cell D5. Then copy this formula to the entire column as previously.

Read More: How to Calculate Age Between Two Dates in Excel

4. Calculate Age in Years, Months, and Days

In this method, we will use the DATEDIF function to calculate age in Excel for an entire column while showing separate years, months, and days values.

Steps:

• To start this method, navigate to cell D5 and type in the following formula:

• After that, press the Enter key or click on any blank cell.
• Immediately, this will give you the age inside cell D5. Now you can copy the formula to the entire column using Fill Handle.

5. Finding Age on Specific Date

Here, we will see how we can use the DATEDIF function to calculate age in Excel for an entire column by specifying 2 date values.

Steps:

• As previously, insert the below formula inside cell D5:

• Finally, press the Enter key and we should get the age for the inputs that we set as criteria. Next, use the Fill Handle tool as previously to calculate the ages for the entire column.

Read More: How to Convert Date of Birth to Age in Excel

6. Using VBA Code to Calculate Age

The VBA method can generate formulas to calculate age in Excel for an entire column. Let us see the details steps.

Steps:

• First, go to the Developer tab and select Visual Basic.

• Now, select Insert in the VBA window and click on Module.

• Next, type in the formula below in the new window:

Sub Age_Calculate()
Dim TargetRow As Long
With ThisWorkbook.Worksheets("Sheet5")
    TargetRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("D5").Formula = "=(YEAR(NOW())-YEAR(C5))"
End With
End Sub

• Then, click on cell D5 and open the macro from the Developer tab by clicking on Macros.

• Now, in the Macro window, select the Age_Calculate macro and click Run.

• Immediately, the VBA code will insert the necessary formula in cell D5 to calculate the age. Similarly, you can now use this formula in the entire column using Fill Handle.

Read More: Excel VBA: Calculate Age from Date of Birth

Things to Remember

• Make sure to rename the sheet in the VBA method as Sheet5.
• You can use the shortcut keys Ctrl+D instead of Fill Handle.
• Use the shortcut Alt+F11 to open the VBA window as an alternative.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to calculate age in Excel for an entire column. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.

Related Articles

• How to Calculate Current Age in Excel
  
• How to Calculate Age in Excel in Years and Months