Custom Heat Map in Google Sheets for Age Analysis

Heat Maps in Google Sheets are the visualization of data using a color scale based on the values. It has nothing to do with the Charts. Here you can learn all about custom Heat Map in Google Sheets that specifically for age analysis. Also, I’ve included in this tutorial how to create Heat Maps in Google Sheets.

Heat Map in Google Sheets is readily available (actually it’s color scale) under the conditional formatting menu. There is nothing to learn to apply colorful Heat Map in Google Sheets. But a Custom Heat Map is something different. You need to use some custom formulas to generate a custom Heat Map in Google Sheets.

Here you can learn how to create a custom Heat Map in Google Sheets suitable for Age Analysis. In this custom heat map, the colors get applied based on the aging of the invoice.

Before going to learn how to create a custom Heat Map, here are the steps that involved in applying a normal Heat Map.

How to Apply Heat Map in Google Sheets

First, you should select the range of cells where you want to apply the Heat Map. Then just go to the menu Format > Conditional Formatting > Colour Scale.

How to Apply Heat Map in Google Sheets

In that conditional formatting setting, set the “Min value” to White and “Max value” to any dark color.

In my above example, I’ve opted Red for “Max value” and White for “Min value”. Your Heat Map is ready! Now let’s move to the Custom Heat Map.

How to Create a Custom Heat Map in Google Sheets for Age Analysis

Example to Custom Heat Map in Google Sheets for Age Analysis

The above screenshot is an example to the custom age analysis Heat Map. You can limit the color scaling to any number of columns that preferably column E. But I’ve extended it to the entire row.

Do you know how to conditional format entire row? I mean conditional formatting one column based on values in another column.

In this tutorial, you can learn that also. So here we begin.

Step 1:

The original data range in this age analysis report is up to column E, i.e. the range A1: E12.

In Column F you should first calculate the aging. It’s not tough. In cell F3 you can directly use the below array formula to get the aging of bills.

=ArrayFormula(TODAY()-D3:D12)

This formula automatically calculates the aging of the invoices based on the invoice dates in Column D.

This Google Sheets formula actually deducts the bill dates from today’s date and expands the results in the range F3: F12.

The two functions that I have used in this formula are ARRAYFORMULA and TODAY.

Step 2:

Our age analysis is based on the following aging period.

1. Current: Ageing of bills from 0 to 30 days (Column G).

2. 30-60: Ageing of bills from 30 to 60 days (Column H).

3. 60-90: Ageing of bills from 60 to 90 days (Column I).

4. 90-120: Ageing of bills from 90 to 120 days (Column J).

5. >120: Ageing of bills above 120 days (Column K).

For the above 5 aging periods, there are suitable formulas with me to automate the copying of the invoice amounts in Column E to corresponding aging columns from G to K.

If you don’t want the aging columns from G to K, you can skip the below points (formulas) up to “Step 3” below.

But you may want to use all the below points (formulas) in Step 3 and I’ll explain it there.

Formula 1 in G3

=IF(F3<30,E3,"")

First, apply this formula in G3 and then copy it to to the range G4: G12. I’m not using Array Formula here as it can complicate the formula for you.

Formula 2 in H3

=IF(AND(F3>29,F3<60),E3,"")

Apply in H3 and then copy to H4: H12.

Formula 3 in I3

=IF(AND(F3>59,F3<90),E3,"")

Apply this formula in I3 and then copy it to I4: I12.

Formula 4 in J3

=IF(AND(F3>89,F3<121),E3,"")

Apply in J3 and then copy to J4: J12.

Formula 5 in K3

=IF(F3>120,E3,"")

Apply in K3 and then copy to K4: K12.

Step 3:

Now you have reached the final steps of creating custom Heat Map in Google Sheets for Age Analysis. Here are that steps.

1. First select the range A3: L12.

2. Go to the menu Format > Conditional Formatting.

3. Under the conditional formatting, apply the custom Heat Map formula as below.

Custom Heat Map conditional Formatting

The formula applied here is the same formula that you can see under the above title “Step 2”, “Formula 1 in G3”.

But here there is one difference. I’ve used $ symbol in the formula here because I want the formatting fixed for entire row based on Column F.

That’s what I said, in the beginning, conditional formatting entire row based on a cell value.

Here In the conditional formatting, I’ve used a light color. Similarly, add additional rules by clicking the “Add another rule button”.

Use the above second, third, fourth and fifth formulas. In all the formulas you should place the dollar symbol as per the first formula.

One more thing. Use a light color for the first formula, little darker for the second, and so on.

You can start from the light Red color on formula 1 and finish with dark Red color on formula 5.

heat map rules in conditional formatting

Once finished click “Done”. You have created a custom Heat Map in Google Sheets for Age Analysis.

As you can see, the Heat Map is applied to the entire columns. If you want to limit it to a few columns, you can easily do this. No need to change any formulas. Then how?

1. Select the column range (for example A3: F12) where you don’t want the heat map to appear.

2 Go to the “Format” menu and click on “Clear formatting”. That’s all. You can access my Google Sheets with the above Heat Map settings Here.

Conclusion

The above are the steps to create Heat Map as well as custom Heat Map in Google Sheets. See you back again with another unique Google Sheets tutorial. Enjoy!