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.
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.
Under the column title “Due by” apply the below formula and then copy paste to down.
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.
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
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.
4. Similarly set the “Columns” and “Values” as below by adding fields.
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.
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.
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.