**Aging Analysis** is usually done in companies to find out the financial situation of the company by sorting out the status of the invoices and how much these invoices are past due. In this article, we will discuss a step-by-step procedure to do an aging analysis in **Excel**.

Here is a brief overview of the aging analysis and its outputs in **Excel** that we will be discussing in this article.

## 📁 Download Excel File

Download the practice workbook below.

## What is Aging Analysis?

Aging analysis is a technique that is employed by accountants and investors to assess and detect any abnormalities in a company’s accounts receivables. It gives an idea about a company’s financial health by sorting the duration within which an invoice has been past due.

## Learn to Do Aging Analysis in Excel with These 4 Steps

To demonstrate** Aging Analysis** in **Excel** properly, we have to prepare a dataset involving clients and their invoice numbers, the expiry date of those invoices, and list a category of invoices based on the number of days. Using these values, we will at first calculate the overdue days and thereby the status of the invoices. The dataset is shown as follows.

**Step**

### Step 1: Merging IF and TODAY Functions to Calculate Overdue Days

The first step in doing aging analysis is to calculate the number of overdue days. Overdue days means the number of days that the invoice is past due from its expiration date. We will use the combination of **IF** and **TODAY** functions to find the number of overdue days. To do so, we need to follow these steps.

⬇️⬇️ **STEPS **⬇️⬇️

- At first, we click on cell
**E6**and write down the following formula.

`=IF(TODAY()>D6,TODAY()-D6,0)`

**🔨 Formula Breakdown**

**IF(TODAY()>D6,TODAY()-D6,0)**

👉 In this formula, the **TODAY() **function gives the current date as output.

👉 The **IF **function has three arguments. The first argument **TODAY()>D6 **checks the condition of whether today’s date is greater than the date in cell **D6**.

👉 The second argument in **IF **function is the value to give output if the condition **TODAY()>D6 **is satisfied. The value of if condition is true results **TODAY()-D6**. If the condition is not satisfied, the output will be zero (**0**) as written in the third argument.

- Then, we drag the
**Fill Handle**at the bottom of cell**E6**and drag it up to cell**E15**.

- The overdue days will be calculated as shown in the following image.

**Step**

### Step 2: Finding Status of Invoice Using VLOOKUP Function

The next step in doing aging analysis is to find the **Status of Invoice** by comparing it to an existing category present in our dataset. We will be using the **VLOOKUP** function to find the values of **Status of Invoice**. So, to classify the **Status of Invoice** based on those categories, we go through the following steps.

⬇️⬇️ **STEPS **⬇️⬇️

- At first, we need to create a
**Named Range**based on the**Days**and the**Category of Invoice**to the present dataset. - We select the cell range
**H6:I13**. - Then, we go to
**Formulas**tab and click on**Define Name**option under the**Defined Names**section.

- A
**New Name**box appears. We give the name ‘**Categories**’ to the selected range of cells, then press**OK**.

- Thus, a named range with the name
**Categories**will be created. - Next, we move to cell
**F6**and click on it. - We go to formula bar and write down the following formula.

`=VLOOKUP(E6,Categories,2,TRUE)`

**🔨 Formula Breakdown**

**VLOOKUP(E6,Categories,2,TRUE)**

👉 In this formula, the first argument **E6 **in the **VLOOKUP **function is the lookup value, that we want to look for.

👉 The second argument **Categories **is the table array where we will look for the lookup value. The table array has been specified as a **Named Range** consisting of cell range **H6:I13**.

👉 The third argument **2 **represents the column number from the selected range from which we will extract the value. In this case, it is the **I** column.

👉 The last argument **TRUE **tells us to give output if there is an approximate match.

- Now, we drag the
**Fill Handle**at the bottom right of cell**F6**and drag it up to cell**F15**.

- We will get all the output values for
**Status of Invoice**as shown in the following image.

**Step**

### Step 3: Creating Pivot Table

Now, we will be creating **Pivot Table** to organize and view the data table. It will help us to see comparisons and trends in the data. To create **Pivot Table**, we have to go through these steps.

⬇️⬇️ **STEPS **⬇️⬇️

- In the beginning, we select the entire dataset within the cell range
**B5:F15**. - After that, we go to
**Insert**tab and select**Pivot Table**option.

**PivotTable from table or range**box appears.**Table/Range**will be selected automatically. We select**New Worksheet**, then press**OK**.

- A
**PivotTable Fields**menu will be visible on the right. We check the**Client**,**Overdue Days,**and**Status of Invoice**options. - We drag the
**Client**option to the**Rows**segment,**Status of Invoice**to the**Columns**segment, and**Sum of Overdue Days**to the**Values**segment.

- Thus, we will get the following
**Pivot Table**as an outcome.

**Step**

### Step 4: Generating a Dynamic Summary

In this final approach, we will generate a dynamic summary of the analysis. To represent this kind of summary, we will be using a **2D **column chart. We can do this by the following steps.

⬇️⬇️ **STEPS **⬇️⬇️

- At first, we select the entire pivot table.
- Then, we move to the
**Insert**tab and from the**Charts**section. - Then, select the
**Insert Column**option.

- From the drop-down menu, we select the
**2D****Clustered Column Chart**.

- As a result, we will get the following chart. This chart is a dynamic summary of the available data.

## Aging Formula for Less than 30, 60, or 90 Overdue Days in Excel

We might need to calculate a certain invoice status that has less than **30**, **60**, or **90 **overdue days. In this approach, we will be using conditional formatting using **AND** function to filter out the cells that have an overdue days number within **30**. To do so, we need to follow these steps.

⬇️⬇️ **STEPS **⬇️⬇️

- At first, we select the column that has the invoice expiry dates. We select the cell range
**D6:D15**. - Then, we go to
**Conditional Formatting**option from the**Home**tab and click on the**New Rule**option.

- We select a
**Rule Type**as**Use a formula to determine which cells to format**. - Then, on the
**Format values where this formula is true**box, we write down the following formula.

`=AND(D6<= TODAY(), TODAY() <=( D6+30))`

**🔨 Formula Breakdown**

**AND(D6<= TODAY(), TODAY() <=( D6+30))**

👉 The **AND** function gives an output if both the first argument **D6<= TODAY() **and the second argument **TODAY() <=( D6+30) **are satisfied.

👉 The first argument checks if the value of cell **D6** is smaller than today’s date.

👉 The second argument checks whether today’s date is less than the value of **D6+30**.

- Now, we click on the
**Format**option.

- In the newly opened
**Format Cells**window, we go to**Fill**section and select a color as shown in the image. - Then, we press
**OK**.

- We press
**OK**again on the**Edit Formatting Rule**box.

- Now, all the dates that have
**Overdue Days**with less than**30 days**will be highlighted.

Similarly, we can do the same for **60 **or **90 **days by changing the value in the formula to **60 **or **90 **in place of **30**.

## 📄 Important Notes

🖊️ The **Named Range** scope has to be selected as **Workbook** to use it all throughout our **Excel** workbook.

🖊️ We will use **IF** function along with** TODAY** function to eliminate the possibility of **Overdue Days** being negative. If **Overdue Days** value is zero or less than zero, which means there are no **Overdue Days**.

## 📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌 ** **We can do the aging analysis by finding out the number of **Overdue Days** in the first step.

📌 Then, we can use the number of **Overdue Days** to look up the categories of invoices based on the **VLOOKUP** function.

📌 Finally, we can generate pivot table and a dynamic chart to represent the data of our analysis.

## Conclusion

To conclude, I hope that this article has helped you to understand how to do aging analysis in **Excel**. For any queries, reach out to us by leaving a comment below. Follow our website **ExcelDen** for more informative articles related to **Excel**.