After seeing an article by the NPR Visuals Team about an alternate way to map out data using a tile grid map, I wanted to see if the process could be replicated using Excel. While Excel is mostly a spreadsheet application, its cells can be manipulated for other purposes. This tutorial outlines the steps used to recreate a tile grid map using Excel.
Tile grid maps are a unique way of visualizing geographic data without needing to accurately represent geographic areas. One challenge when mapping out state specific data at the level of the entire United States is that the smaller states along the northeastern section of the country can be hard to see. Tile maps address this dilemma by making each state a uniform size with a rough spatial arrangement.
Setting up the Tile Grid Map
Please note: I have outlined all the steps to create a tile grid map from scratch in this article. However, the Excel file template I created is available for downloading at the end of this page.
The first step is to set up the cells to create the arrangements of the states for the tile grid map. For this tile grid map of the United States, I set the column and row widths so that each cell is a 1″ x 1″ square. The layout developed by the NPR team was replicated with Alaska in the upper left corner of the grid, Hawaii in the lower left corner, and the remaining 48 contiguous states and the District of Columbia taking up the rest of the grid. While the spatial arrangement isn’t entirely accurate, the layout roughly approximates the geography of the United States. While the shading will be overwritten later in this tutorial, the cells representing each states was shaded a light grey to help with navigating during the setup.
Free weekly newsletter
Fill out your e-mail address to receive our newsletter!
By entering your email address you agree to receive our newsletter and agree with our privacy policy.
You may unsubscribe at any time.
Labeling the Tile Grid Map Template With Each Two Letter State Abbreviation
Next, I added labels to the cells representing the states. For the actual shading of the cells, I will be pulling in the values from another section of the spreadsheet. For the final map, I don’t want to display the value but the two letter abbreviation for the state instead. The visible value of a cell can be overriden through a custom format. To do this, right click on the cell and select “Format Cells” from the drop down menu.
From the GUI that appears, select Number –> Custom. In the box labeled Type, enter the text you want to appear in the cell. To prevent Excel from interpreting the individual letters as special commands (such as T for time), put a backslash in front of each letter. For example, to label Alaska as AK, I entered \A\K. You can verify that the custom format will show up as expected by making sure it is written properly in the sample section located about the type box.
I repeated these steps for each of the grids to create a labeled template of the United States.
Now I want to populate each state cell with a value derived from another section of my spreadsheet. This way, I can simply change the column for the values as needed instead of one by one in the grid. Associating the values from each of the cell is done by selecting the cell and in the formula bar enter “=” plus the cell column letter and row number. Hit enter and the cell will return to the custom format label but will contain the actual cell value pulled from the reference cell.
For the tile grid map that I am going to create, I used the percentage of area that is water calculations as discussed in this article: Which States Have the Highest Percentage of Water Area?
Setting up the Symbolization Using Conditional Formatting
Now I want to shade each of the cells based on a categorization of the values. To do this, I will use conditional format to apply the background color based on which category that particular cell’s value falls into. You can also set rules for the font type and color as well as border using conditional format.
First, I want to set the selection area that the rule will be applied to. To do this, highlight the cells and then in the menu bar under Format, select Conditional Formatting –> New Rule.
Next, I want to set a rule that keeps the cells that are empty white. In the rule GUI, I selected Classic from the Style menu dropdown and then “Format only cells that contain”. Then I want to set a rule for all cells that are blank. Under Format With, I selected custom format… to pull up another window to set the actual shading.
In the second window, I selected the Fill option and set the background to white. Then I hit the OK button for both windows to apply the conditional formatting.
Now I want to set up categories to set how the different state cells will be shaded depending on the values. In the interest of making sure the tile grid map is readable, I will limit the total categories to four based on natural breaks in the data: less than 5%, 5% to 11.9%, 12% to 29.9%, and more than 30%. As with the previous I stepped through the initial steps of setting the selection area and selecting new rule again. This time, I based the rules on the cell values. To do so, I selected “Cell Value” then “less than” and finally I entered “o.o5” since my percentages for each of the cells are represented as fractions. This will set a rule that affects the visualization of all cells that have a cell that is less than 5%. Under custom format I set the background to a light gray shade. Once the parameters are set for the rule, I hit the OK button and the cells that meet the new conditional formatting are shaded as set.
For the middle two categories, I set the parameter to “between” and for the last category as “greater than”.
Editing the conditions can be done by going to Conditional Format –> Manage Rules. The GUI that appears provides an overview of all rules for a selection or the entire worksheets. There, any rules can be edited or deleted or new rules added.
Once I have my conditional formatting rules set, I can add in a legend, metadata, and title and my tile grid map is ready. To create an image from the spreadsheet, simply copy the cells and paste into a graphics program such as Photoshop.
Microsoft Excel Tile Grid Map Template
If you’re interested in developing your own tile grid map for the United States, you can download the Excel file I created here: template-tile-grid-map.xlsx.
