Grid charts are a great way to visualize your data in Google Sheets, as you can create one in just a few minutes. But, there isn’t a native chart editor in Google Sheets for grid charts, so they can be a little tricky.

This article discusses what a grid chart is, the formulas needed to create one, and how you can create and customize a grid chart in Google Sheets.

What Is a Grid Chart?

A grid chart visualizes data using color and symbols. It exists on a two-dimensional plane that is uniformly spaced, and it allows you to observe and study a selection of data much more easily.

A user can look at trends the data set follows over time in different sets. These charts can represent data sets like employee performance, student grades, weather, etc. They’re often used on a percentage-based figure to compare two or more groups. A grid chart is just one of the many helpful Google Sheets charts you can create inside the program.

The Formulas for Making a Grid Chart in Google Sheets

There are essentially three formulas used to create a grid chart in Google Sheets. These include the ARRAYFORMULA, IF, and SEQUENCE. Here is what each formula does:

ARRAYFORMULA

This formula allows you to display the values that return from an array into multiple columns and rows, and use non-array functions with the array. You can use it to fill large chunks of columns with other formulas results without using autofill.

Here is the syntax for the formula:

=ARRAYFORMULA(array)

The formula uses one argument, which is:

  • array: This is a range or a mathematical expression that uses one or more cell ranges of the same size or function that returns results greater than one cell. I.e., an “array” of cells.

The IF Function

The IF formula in Google Sheets returns a value depending on the output of an expression. The output of the expression can either be TRUE or FALSE.

However, you can specify which value should be displayed instead of the TRUE/FALSE output. Here is the syntax for the formula:

=IF(expression, value-for-true, value-for-false)

The formula uses three arguments. These include:

  • expression: This refers to a cell or an expression that can represent a logical value that is either TRUE or FALSE.
  • value-for-true: This is the text or value to display if the expression returns the TRUE value.
  • value-for-false: This is an optional argument used to assign a text or a value to display if the expression returns the FALSE value.

SEQUENCE

This formula creates an array of a defined number of rows and columns. The array is filled with a sequence of numbers starting from 1 and going up to the multiple of the row and column. Here is the syntax for this formula:

=SEQUENCE(row, column, start-number, step-by)

The formula uses four arguments. These include:

  • row: This is the number of rows you wish the formula to create.
  • column: This is the number of columns you wish the formula to create.
  • start-number: This is an optional argument used to define the number to start the sequence with. If no number is.
  • step-by: This is an optional argument used to define the number of increments in the next number in the sequence. If you don't use this argument, it will default to 1.

How to Make a Grid Chart in Google Sheets

The chart we make in this guide is one with two values. This is defined by a percentage we input in a cell before creating the chart. Let’s pretend our example is for employees that completed training, where the passing mark was 75%.

Here are the steps you need to follow to make a grid chart in Google Sheets:

  1. Input the percentage in a cell of your choice. Remember to add the Percentage (%) symbol at the end.
    using a sequence formula to build a 10x10 grid
  2. Choose a part of the spreadsheet which can easily fit in the chart and type in the SEQUENCE formula. Feel free to choose the number of rows and columns depending on your requirements.
  3. Adjust the row and column width to make the cells square. This step is optional.
    An example of a full grid chart formula
  4. Add in the ARRAYFORMULA with the nested IF formula and SEQUENCE formula. This allows you to check if the value in the cell is lesser or greater than the given percentage.

In this case, the final results show a grid chart showing the value "1" for the grade of 75% mentioned in cell A1 and the value “0” for the remaining 25% (fail grade).

Customizing the Grid Chart

You may notice that the chart looks bland. To make the chart pleasing to the eye, we will make a few visual changes. First, we will add color, so each value is easier to distinguish, and later we will add icons and fix the alignment to make the grid chart look clean.

Here are the steps to do so:

How to open the conditional formatting menu
  1. Select the cells to highlight the chart. This can be seen as the blue border around the table.
  2. Click on Format in the top bar.
  3. Now, select Conditional formatting.
    A screenshot of the conditional formatting rules menu
  4. In the Conditional format rules window on the right, choose Is equal to in the Format cells if part of the section.
  5. In the Value or formula textbox, enter the first number.
  6. Under Formatting style, choose the changes you wish to apply to the first part of the chart.
  7. Finally, click on Done to apply the changes.
    Adding extra rules to the conditional formatting
  8. To add another color to the remaining values in your grid chart, click on Add another rule and follow steps 4-7. In this case, we have formatted our grid chart so that the first 75% is in green, while the remaining 25% is in red.

Although we have added colors to our chart, the numbers in the grid don't seem particularly pleasing to the eye. Let's add a custom icon to the grid chart to make it look better. To do this, follow these steps:

How to open the custom number format menu
  1. Select the grid that you created.
  2. Click on Format and then click on Number.
  3. There, click on Custom number format.
  4. In the textbox, paste in an emoji or type your custom text.
  5. Click on the Horizontal align button in the top bar.
  6. Click on Center.
An example of a completed grid chart

As you can see, our grid chart looks much better. The graph is represented using an appropriately aligned icon, and we use proper color to help visualize the data.

Wrapping Up Grid Charts in Google Sheets

Creating grid charts in Google Sheets takes only a few minutes and is a great way to create visuals of your data. This can help you make comparisons and share data in an easy-to-understand way with others.