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.

**Table of Contents**hide

## 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 uses to identify which invoices are overdue for repaying.

**Read More: ****How to Use Ageing Formula for 30 60 90 Days in Excel**

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

**Similar Readings**

**How to Use IF Formula for Aging Buckets in Excel****Use Stock Ageing Analysis Formula in Excel****How to Use Multiple If Conditions in Excel for Aging**

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

**Read More: ****How to Calculate Days with Aging Formula in Excel**

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

**Read More: ****How to Do Aging Analysis in Excel**

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