A tile grid map can be a clear way to show ranges of values for a country or a selection of countries without representing the exact geography of each area. This can be useful for creating a simple and clear visualization of values that would not be otherwise easily seen for areas that have a small geography. This is particularly true when creating a choropleth map of the entire United States. Large states like Alaska, California, and Texas can be easily seen but determining the value of smaller areas like Rhode Island are difficult when viewed at the scale of the entire United States. A tile grid map, by sizing each geographic unit uniformly, makes it easy to interpret the values for all states. (Related: Data Visualization Strategies Using Tile Grid Maps)
In a previous tutorial, instructions were provided for how to create a tile grid map using Excel, Microsoft’s spreadsheet program. This tutorial provides instructions on how to create a tile grid map using Google Sheets.
Tile Grid Map Template for Google Sheets
A template for creating a tile grid map of the United States using Google Sheets is available here. To use this template, you will need to have a Google login. Then, create a copy of the tile grid map to your own Google Drive account:
Add State Based Data
Once you have a copy of the tile grid map, it’s time to add your own data. To the right of the tile grid map, you will see three columns. One for the state name, one for the state abbreviation, a value column. In the value column, you will need to enter in your own data. The states (along with D.C. and Puerto Rico) are listed alphabetically by the respective abbreviations. You can either copy and paste from a list that is also arranged alphabetically or manually type in your new values.
If you click on any cell within the tile grid map area, you will notice that the reference cell number from the value list is shown.
Adjust the Conditional Formatting
Once you have the values entered, you will need to adjust the conditional formatting to meet your range and color shading needs. The tile grid map is currently set up with a blue shading scheme with three categories and a fifth category shaded gray for states with no values. You can use conditional formatting to change the values for each of your categories, change the colors for each categories, and add more categories.
To start adjusting the conditional formatting, click on any of the cells within the tile grid map. From the menu choose Format –> Conditional formatting…
This will bring up the Conditional format rules window on the right hand side of the screen. In this window you will see five conditional format rules. The first rule defines all of the white space around the cells representing each of the states. The next four cells contain the conditional formatting for each of the cells representing the states.
To change the value range and/or color scheme for each of the categories, click on a category. In the next window, you will be able to adjust the value range and select a new color scheme.
Adjusting the Tile Grid Map Label
The tile grid map has cell formatting that labels the cell by the state abbreviation instead of the cell’s value. This is achieved by applying a custom number format to each cell. You can adjust this by selecting Format –> Number –> More Formats –> Custom number format for each cell whose label you wish to change.
Text-based labels need to have each letter separated by a blackslash (\):
As you type in the custom format, the sample text below the box will show you how your label will be interpreted.
Enjoy experimenting with making a tile grid map in Google Sheets.