Create Age Analysis Report Using Google Sheet Pivot Table

1
248
Create Age Analysis Report Using Google Sheet Pivot Table

Pivot Table is one of the area where I think Google Sheet fell short to Excel. The features in Google Sheet Pivot Table is limited. But I’m trying to make the best use of Google Sheet Pivot Table. Earlier I’ve posted a complete tutorial on creating a powerful ageing analysis report using Google sheet formulas. That report is highly customisable. Now we use the Google Sheet Pivot Table to create ageing analysis. It’s simple, but customisation is limited. We can easily create age analysis report using Google Sheet Pivot Table with a tweak.

Create Data for Age Analysis Report

In order to create age analysis report using Google Sheet Pivot Table, tweak your data with a formula first. Let us directly move to the steps. I will explain all the details below.

data for age analysis report in google sheet

The above is the type of data which you require first to create a Pivot Table Ageing Analysis report.

We are working on a very simple ageing analysis report using Google Spreadsheet. So learn the steps first then if you want you can change your report to some extent later.

Once you finish with the above steps, I mean creating the data, follow the rest of the tutorial below.

We need two more columns as below to apply formulas. Here we used Columns “E” and “F” for this purpose.

formula for pivot table

Under the column title “Due by” apply the below formula and then copy paste to down.

=today()-C2

Finished? Then under the column title “Ageing” apply the formula, which is little bit lengthy, that you can find below.

=if(E2<30,”1. Current”,if(and(E2>29,E2<60),”2. 30-60 days”,if(and(E2>59,E2<90),”3. 60-90 days”,if(and(E2>89,E2<119),”4. 90-120 days”,”5. 120 days & above”))))

Helpful Tips: You can apply Alt+Enter inside your formula to make new lines. So that you can quickly understand the formula.

Copy the formula applied and paste to adjoining cells down. Don’t try to copy paste the formula from this post as it can create parse error. You should type it in the cell mentioned above and copy paste.

Also no need to apply dollar symbols in this formula while copy and paste. At this juncture, I know a GIF screenshot would make the things easy for you. But I opted NO because I want this page to load fast and save some data.

tweaked data for pivot table report

Now we are ready with the data including the essential two formulas. Let’s create Pivot Table Ageing Report using this data in a flash.

Steps to Create Age Analysis Report Using Google Sheet Pivot Table

Steps:

1. Select the data.

2. Go to the Data Menu > Pivot Table.

3. On the right side panel you can see the Pivot Table “Report Editor”. Click “Add field” against “Rows” and add the fields as follows.

Pivot Table Editor Row Settings

4. Similarly set the “Columns” and “Values” as below by adding fields.

Pivot Table Column Editor

That’s all you have to do. Your Pivot Table Age Analysis Report is ready now.

Customisation of Age Analysis Report in Google Sheet

Select your pivot table report and just go to the “Format” menu “Conditional formatting” and select “Alternating colours”. Change the colour as you wish.

As a side note if you have multiple customers in your report, do select the “Show details” in Report editor as below.

Sub Total in Pivot Table

This way you can get subtotal in your Pivot Table report in Google Sheets. See the finished ageing analysis report created using Pivot Table below.

finished ageing analysis report created by using Pivot Table

I know there are lots of limitations in using Google Sheet Pivot Table report. Hope Google will add more features to this wonderful tool in future.

1 COMMENT

  1. You can simplify your formula:
    =IF(E2<30,"1. CURRENT",IF(E2<60,"2. 30-60 DAYS",IF(E2<90,"3. 60-90 DAYS",IF(E229,E2<60) (and others like it) because the earlier tests would include those under 30 days.

    Plus, don't most aging reports do <30, 31-60, 61-90, 91-120, and 121+ so those at 30, 60, 90, and 120 days are explicitly in a single group? Thus:
    =IF(E2<30,"1. CURRENT",IF(E2<60,"2. 31-60 DAYS",IF(E2<90,"3. 61-90 DAYS",IF(E2<119,"4. 91-120 DAYS","5. 121 DAYS & ABOVE"))))

LEAVE A REPLY

Please enter your comment!
Please enter your name here