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.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 6 Simple Methods to Calculate Age in Excel for Entire Column

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:

`=INT((TODAY()-C5)/365)`

- 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 Easy Ways)**

### 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:

`=ROUNDDOWN(YEARFRAC(C5, TODAY(), 1), 0)`

- 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: ****Excel formula to Calculate Age on a Specific Date**

### 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:

`=DATEDIF(C5, TODAY(), "y")`

- 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 (6 Useful Methods)**

### 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:

`=DATEDIF(C5,TODAY(),"Y") & " Years, " & DATEDIF(C5,TODAY(),"YM") & " Months, " & DATEDIF(C5,TODAY(),"MD") & " Days"`

- 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**.

**🔎 ****How Does the Formula Work**?

**DATEDIF(C5,TODAY(),”Y”)**: This portion returns the year value as**26**.**DATEDIF(C5,TODAY(),”Y”) & ” Years, “**: This returns the value**26 Years**.

**Read More: ****How to Calculate Age from Birthday in Excel (8 Easy Methods)**

### 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**:

`=DATEDIF(C5,D5, "y")`

- 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 (3 Easy Ways)**

### 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.

## 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. Lastly, to learn more **excel** techniques, follow our **ExcelDemy** website. If you have any queries, please let me know in the comments.