How To Create A Grid Chart In Google Sheets

Earlier this year, The Washington Post told a story about the effects of Coronavirus on the US workforce, and illustrated the story with grid charts.

Grid charts can show you the breakdown of the whole into constituent parts, to allow at-a-glance understanding of the big picture.

In this post, I’ll show you how to create a Grid Chart in Google Sheets.

💡 This was tip #128 of my weekly Google Sheets newsletter. Join over 35k+ others and receive the Google Sheets Tips newsletter for exclusive tips, tricks and Google Sheets news.

Grid Chart

Here’s a fictitious grid chart example in Google Sheets, showing how students fared in an exam:

Grid Chart in Google Sheets

Changing the percentages in the cells above the chart will automatically adjust the chart colors to match.

How to create a grid chart in Google Sheets

1. Enter a % value in cell A1 e.g. 73%

2. Underneath, in cell A3, enter this SEQUENCE formula:

=SEQUENCE(10,10)

This outputs a 10 by 10 grid of ascending numbers from 1 to 100.

3. Next, adjust the column widths (and row heights) so that the cells are square.

4. Wrap the sequence function with an IF statement and ArrayFormula to check whether the value in a given cell is greater than the threshold percentage:

=ArrayFormula(IF(SEQUENCE(10,10)>A1*100,2,1))

Your output now will look like this:

Grid Chart in Google Sheets

5. Highlight the 10 by 10 grid and add two conditional formatting rules:

  • Green cell background if the value “Is equal to 1”
  • Grey cell background if the value “Is equal to 2”

Conditional Formatting in Google Sheets

6. With the 10 by 10 grid highlighted, add thick white borders to separate the grids. Turn off the gridlines for the Sheet too, for an even cleaner look.

7. Keeping the grid highlighted, change the number format to a custom number format with the emoji symbol: 👤

Format > Number > More formats > Custom number format, then paste in the emmoji: 👤

Custom Number Rule in Google Sheets

This changes all the values to 👤, regardless of whether it’s a 1 or a 2.

8. Finally, center-align the values horizontally and vertically:

Grid Chart in Google Sheets

Nice!

When you change the % value, the chart will adjust automatically for you.

3-Color Grid Chart

Grid Chart in Google Sheets

To create the 3-color chart shown above, add an additional percentage value and modify the formula to compare against both percentage figures using two IF statements, e.g.:

=ArrayFormula(IF(SEQUENCE(10,10)<=A1*100,1,IF(SEQUENCE(10,10)<=((A2+A1)*100),2,3)))

In the second conditional test, you’ll notice I need to add percentage 1 and 2 together, to get the cumulative value at that point in time.

You also need to add an extra conditional formatting rule for the cells that have the value 3.

Google Sheets Grid Chart Template

Click here to open the Google Sheets Grid Chart template.

This will open a view-only version of the template. Feel free to make your own copy: File > Make a copy

(If you’re unable to open this file it may be because it’s from an outside organization, and my G Suite domain is not whitelisted at your organization. You may be able to ask your G Suite administrator about this.

In the meantime, feel free to open in an incognito window to view it.)

4 thoughts on “How To Create A Grid Chart In Google Sheets”

  1. Can you make the multiple options case a bit tidyier by using the SWITCH() function, instead of multiple nested IF()s?

    To be honest, I haven’t tried using it in an ArrayFormula, and I also don’t know if you can match expressions in a switch, or just literal values.

    1. Hi Stu,

      Great question but I don’t think you can, because the switch is for checking equality only, whereas we need to do comparisons.

      I’ve included a template link at the end of the article now if you want to download and have a mess around with the formula.

      Cheers,
      Ben

  2. Hi Ben. Struggling to find the emoji symbol towards the end.

    Followed: Format > Number > More formats > Custom number format > ?

    Please advise. Tx.

    1. Hi Lee,

      Sorry for the confusion! I recently moved servers for my site and all the emoji symbols were lost and replaced with “?”. The one you want is the bust in silhouette: https://emojipedia.org/bust-in-silhouette/

      Copy and paste this into your custom number formatting rule.

      I’ve also updated the article to show this.

      Cheers,
      Ben

Leave a Reply

Your email address will not be published. Required fields are marked *