Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will show you how to calculate the Aging of Accounts Receivable in Excel.

## Introduction to Aging of Accounts Receivable

Aging of Accounts Receivable is a report that details how long an invoice has been unpaid. It is useful in determining the allowance for doubtful accounts. There are some segments in the report (often 0-29,30-59, and so on), and organizations record receivable collections according to the segments. This report is the primary tool that collection personnel use to identify which invoices are overdue for repaying.

## Calculate Aging of Accounts Receivable in Excel: 3 Easy Methods

This is the dataset for todayâ€™s article. There are some orders with due dates and amounts. I will calculate the **Aging of Accounts Receivable** using this dataset.

### 1. Apply IF Function to Calculate Aging of Accounts Receivable in Excel

In the first method, I will show you how to calculate the aging of accounts receivable using the** IF** function. Letâ€™s do this step by step.

**Steps:**

- First of all, calculate the
**Days Overdue**. To do so, go to**F7**and write down the following formula

`=$C$4-E7`

- Then, press
**ENTER**to get the output. - Change the format to
**Number**.

- After that, use
**Fill Handle**to**AutoFill**up to**F22**.

- Now, to get the aging status, go to
**H7**and write down the following formula

`=IF(F7>90,"90+ Days",IF(AND(F7>=60,F7<=90),"60-90 Days",IF(AND(F7>=30,F7<=59),"30-59 Days",IF(AND(F7>0,F7<=29),"1-29 Days","Not Due"))))`

**Formula Explanation**:

The logical tests are

- F7>90
- AND(F7>=60,F7<=90)
- AND(F7>=30,F7<=59)
- AND(F7>0,F7<=29)

For **F7** (**108**), the first test is **TRUE**. So, the corresponding output is **90+ Days**.

- Now, press
**ENTER**to get the output.

- Finally, auto-fill up to
**H22**.

### 2. Use VLOOKUP Function to Calculate Aging of Accounts Receivable in Excel

Now, I will show another method. This time, I will use the **VLOOKUP** function. But, for this method, you have to modify the dataset a bit.

**Steps:**

- Now, itâ€™s easy. Go to
**H12**and write down the formula

`=VLOOKUP(F12,$F$5:$G$9,2,TRUE)`

**Formula Explanation:**

**Excel**will look for**F12**in the array**F5:G9**.**TRUE**indicates that the match is an approximate one, not the exact one.**108**is the closest to**90**. So the return is**90 or More**.

- Then, press
**ENTER**to get the output.

- After that, use
**Fill Handle**to auto-fillÂ up to**H27**.

### 3. Apply PivotTable Feature to Calculate Aging of Accounts Receivable in Excel

Now, I will show another method to calculate the aging of accounts receivable in Excel. This time, I will use the **PivotTable** feature.

**Steps:**

- Select the entire table.
- Then, go to the
**Insert** - After that, select
**PivotTable**. - Finally, choose
**From Table/Range**.

- A box will appear. Excel will automatically set the range.
- Click
**OK**.

- As a result, Excel will create a
**PivotTable**.

- Then, from the
**PivotTable Fields**, drag**Customer ID**to**Rows**,**Days Overdue**to**Columns**, and**Amount**to**Values**. - Excel will by default show the sum of the amount.

- After this, you will get the following output.

- Now, to get the aging of accounts receivable, you have to group the
**Days Overdue**. - To do so, select any cell from the row representing the
**Days Overdue**. - Then, right-click your mouse to get the context bar.
- After that, select
**Group**.

- A
**Grouping**box will appear. - Write
**0**as the starting point. - Then, keep
**307**as the ending point. - And,
**30**as the grouping range. - Finally, click
**OK**.

**Excel**will return the aging of the accounts receivable.

## Things to Remember

- Use absolute referencing to lock a cell.
- The nested
**IF**function tests the logical tests one by one.

**Download Practice Workbook**

Download this workbook and practice while going through the article.

## Conclusion

In this article, I have explained how to calculate the aging of accounts receivable in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.

**<< Go Back to ****Ageing ****| Formula List | Learn Excel**