Data Viz Toolkit: Tale of the Tile Grid Map

​​Choropleth is a technique in which geographic regions (e.g., states, countries) displayed on a map are filled with a color (or pattern) that represents a value or range of values. ​The following map of the United States that displays the December 2018 (seasonally adjusted) unemployment rate for each state is an example of a choropleth map:

​Although choropleth maps are a common choice for visualizing data that are location specific, they can be difficult to interpret—especially when the areas on the map vary in size. This is where tile grid maps become useful because, on a tile grid map, regions are represented as squares of equal size and shape and are arranged in a layout that approximates their real-world geographic position. In this post, I will share how to create a tile grid map in Excel using data from the KIDS COUNT Data Center on the percentage of children in families that receive public assistance.

​STEP 1: MAP SETUP
I created a Tile Grid Map Template in Excel. The workbook contains 3 worksheets: Viz, Codebook, and DataTable.
 
In the Viz worksheet, enter the 2-letter abbreviation for each state in a layout that loosely resembles the geography of the United States.

​Then, set the column width and row height of the Viz worksheet to 3.33 and 23, respectively.

​Finally, use the Fill Color button on the Home tab to set the background color of the ‘tile map’ cells to light Grey. (When conditional formatting is applied to each cell, the Grey color will be overwritten.)

STEP 2: FORMAT MAP CELLS
The next step will be to define a custom format for each cell within the map that is equal to the 2-Letter abbreviation for each state. To do this, right-click on a cell and select Format Cells…

​​Under the Number tab, select the Custom category and enter the 2-letter abbreviation for the ‘tile’ enclosed by double quotation marks, then click OK. 

​Repeat these steps, labelling each cell in the map.

​STEP 3: CREATE A CODEBOOK
A codebook is a tool that is used to describe the contents of a dataset. Most codebooks are structured as a table with rows and columns, where each row represents an item and columns contain information about each item. When developing visual displays in Excel, I often designate a single worksheet in my workbook to serve as codebook. Importantly, this codebook does not list details about the indicators or metrics shown in the visualization. Rather, it functions as a space where I can perform simple (or complex) calculations whose results will then be visualized. Using this approach pushes me to think carefully about what information to display and offers an effective means for streamlining the process of building and maintaining visualizations.
 
The Codebook worksheet in the workbook you downloaded should look something like this: 

Given that we want to build a display that showcases the percentage of children receiving assistance in each state for the year 2013, we must first populate column C of the Codebook worksheet with these data. (Later, we will link the data in these cells to their respective ‘tiles’ in the Viz worksheet.) This can easily be done using a Vlookup() function. Vlookup is a built-in Excel function that finds a value in a column of data and returns a corresponding value from another column. If you are unfamiliar with Vlookup, Microsoft Office has a tutorial on how to use the function here. 

​Enter the following Vlookup function into cell C2 of the Codebook worksheet:

The function states that we want find the exact value in A2 within DataTable!$A$1:$G$52, located in the DataTable worksheet, and return the corresponding value from the third column. (When referencing cells in another worksheet, enter the name of the worksheet followed by an exclamation point (!); click here to learn more about creating cell references in Excel.) Put another way, we want to look up the 2-letter abbreviation for each state in our Codebook worksheet and return the percentage of children who received public assistance in that state (as a decimal) for the year 2013 from our DataTable worksheet. 

​Apply the same formula to the rest of the cells in column C by dragging the lower right corner of cell A2 downwards using the left mouse button. 

STEP 4: ADD CELL LINKS TO MAP
Now, we are ready to link the Codebook worksheet cells, which contain data for each state, to their respective ‘tiles’ in the Viz worksheet!

To link cell C2 in the Codebook worksheet, which contains data for Alaska in 2013, to the ‘tile grid square’ labeled AK in the Viz worksheet:
 
Select cell C2 and then, On the Home tab, click copy. Toggle over to the Viz worksheet and locate and select the cell labeled AK. Then, on the On the Home tab, click the downward-facing arrow next to the Paste button and select the paste link option.

Use the same procedure to link the rest of the cells in column C in the Codebook worksheet to their respective cells in the Viz worksheet. The end result should look something like this:

STEP 5: APPLY CONDITIONAL FORMATTING
 
Excel's built in Conditional Formatting feature will be used to apply a unique color to each cell in our map based on the value of the cell. But before proceeding with color formatting, we must first decide what colors to use and assign a value or range of values to each color. I have to admit that, in the near past, many of my designs were not exactly friendly for individuals who have a hard time distinguishing between certain colors. Now, I use the open source tool Color Oracle to ensure my designs are color accessible for all.
 
Given the range of values in the dataset, percentages were divided into five groups denoted by five colors. Red-Green-Blue (RGB) values, which are shown below, are used to specify the colors that will be used in the display.

​Now, for the fun part: adding color! 

Hold the Ctrl key (Windows) or the Command key (Mac) and select all of the cells that comprise the tile map. Then, on the Home tab, click the drop-down arrow next to the Conditional Formatting button, and select Manage Rules.

​​A Manage Rules dialog box should appear. Click the plus sign (+) at the bottom left hand side of the dialog box to add a new rule to the selected cells.

​A New Formatting Rule dialog box will appear. In this dialog box:

• Change the Style to Classic;

• Change the Rule Type to Format only cells that contain; and

• Select Blanks in the Format only cells drop-down list.

 
Next, select Custom Format… from the Format with drop-down menu. 

In the Format Cells dialog box, click the Font tab and change the font color to White. Then toggle over to the Fill tab and set the Background color to Grey. In this case, you want to apply the specific Grey color associated with the No Data Category, so click the downward facing arrow in the box below Background color, choose More Colors…  and enter the RGB values associated with the No Data category: Red = 209; Green = 209; and Blue = 209. Click OK. Then, click OK again to apply the formatting rule.

One down, four to go!
 
To highlight cells that are between two values, click the plus sign (+) at the bottom left hand side of the Manage Rules dialog box and when the New Formatting Rule dialog box appears:

• Change the Style to Classic;

• Change the Rule Type to Format only cells that contain;

• Select Cell Value from the first Format only cells drop-down list and in the second drop-down list select between and enter the minimum (left textbox) and the maximum (right textbox) values. 

Then select Custom Format… from the Format with drop-down menu, and, in the Format Cells dialog box, change the font color to White and set the background of the cell to the appropriate color.
 
To highlight cells that are greater than or equal to a specific value, click the plus sign (+) at the bottom left hand side of the Manage Rules dialog box and when the New Formatting Rule dialog box appears:

• Change the Style to Classic;

• Change the Rule Type to Format only cells that contain;

• Select Cell Value from the first Format only cells drop-down list and in the second drop-down list select greater than or equal to and enter the value. 

​Then select Custom Format… from the Format with drop-down menu, and, in the Format Cells dialog box, change the font color to White and set the background of the cell to the appropriate color.
 
Once all rules have been added, the Manage Rules list should look something like this:

​​Click OK and, voila— a color-coded tile grid map!

**BONUS: FORMATTING TIPS & TRICKS**
If you are like me, you are probably not satisfied with a simple map. In this bonus formatting section, I will walk you through some basic formatting tricks that will elevate your display.

DEFINING STATE BOUNDARIES
The first step towards designing a more professional-looking map requires defining state boundaries using Excel’s built-in borders functionality. Given the vibrancy of the colors used in the display, I would recommend setting cell border colors to White. To accomplish this task, select the map area (cells B5 through L12), then click the drop-down arrow next to the Borders button on the Home tab. Next, navigate to the arrow next to the Line Color button and, under Theme Colors, select White. Then, go back to the Borders button drop-down arrow and select the All Borders option.

​End Result:

​Next, to tackle the tacky legend…

LEGEND FORMATTING
Face it, the ‘legend’ accompanying the map is…ugly.  Let’s fix that!
 
Copy-paste cells N2 through P7 in the Viz worksheet into a new workbook. ​Next, create two new columns of data: in the first column, Category, enter the legend category labels from No Data to 36% +; and in the second, Frequency, enter the numbers 1 through 5 in ascending order. During this step, be sure to make any formatting or grammatical changes to the text (e.g., add % symbols). (For aesthetics, I added two spaces before each string in the Category column.)

​Then, select both columns of data, toggle over to the Insert tab, and create a pie chart.

​The next step will be to adjust the color of each pie slice to match the 5 colors representing the categories shown in the original legend. To change the color of a pie chart slice, double left-click on a single slice, then use the Fill Color button on the Home tab to fill the slice with the appropriate color.

Once the color of each slice has been manually adjusted, the pie chart should look something like this:

From there, delete the chart title by left-clicking once to select it, then right click and select delete. Decrease the size of the pie chart by left-clicking inside the plot area to reveal sizing handles and dragging the sizing handles to reduce the height and width of the plot.

The next step will be to decrease the chart height so that only the legend is visible by selecting the box around the chart and dragging the top-center sizing handle downward. Adjust the width of the legend to fit the chart area by left-clicking on the legend and dragging the right-center sizing handle slightly to the right; apply the same procedure using the left-center sizing handle.

Now we must format the appearance of the chart box and legend. First, remove the chart outline by left-clicking the box around the chart, toggling over to the Format tab, and, on the Format tab, clicking the drop-down arrow next to the Shape Outline button and choosing No Outline. Next, set the Background Fill of the chart area to White by clicking the Shape Fill button-located directly to the left of the Shape Outline button-and, under Theme Colors, selecting White. To ensure that the pie chart is not visible once the legend is enlarged, the legend background color must also be set to White. To do this, left click on the legend, toggle over to the Format tab, click the Shape Fill button  and, under Theme Colors, select White. During this step, be sure to change the font size and color of the legend as well. To change the font size of the legend, click on the legend, toggle over to Home tab, and enter a larger size in the Font Size box. (I entered size 14.) Use the drop-down arrow next to the Font Color button to change the color of the legend text. (The font color I chose has the following RGB values: Red = 89; Green = 89; Blue = 89.

The final step is to increase the size of the legend icons. While you cannot directly customize icon dimensions, Excel has a nifty little feature where you can format the Borders around it—effectively resizing the icon. First, left-click on an icon in the legend, then right click and choose Format Legend Entry…  A Format Legend Entry pane should appear on the right side of your screen. In the Format Legend Entry dialog pane, navigate to the Border tab, click the drop-drown arrow next to the Outline color button, choose More Colors… , and set the border to the same color (or RGB value) as the icon. In this instance, because the first icon was selected, I entered the RGB values associated with the No Data Category: Red = 209; Green = 209; Blue = 209. From there, adjust the thickness of the border by entering a number larger than 1.5 into the textbox next to the Width option (I entered 8) and set the Join type to Miter.

Apply the same procedure to the remaining legend icons.
 
The end result:

PUTTING IT ALL TOGETHER
Now all that's left to do is copy-paste our map and legend into a clean canvas. I personally prefer to use PowerPoint. PowerPoint is not just for lectures or presentations, it is a great tool for displaying visuals.
 
Open the workbook containing the tile grid map. Use the left mouse button to select the map area. Navigate to the Home tab, click on the drop-down arrow next to the Copy button and select Copy.

​Open a blank PowerPoint document. Navigate to the Home tab, click the drown-down arrow next to the Paste button, and select the paste as a picture option

Open the workbook containing your legend and left-click the box around the legend to select it. On the Home tab, click on the drop-down arrow next to the Copy button and select Copy.   

Switch back over to the PowerPoint document. Navigate to the Home tab, click the drop-down arrow next to the Paste button, and select the paste as a picture option.

​Add an informative title and resize the legend and map. The final product—a visually stunning display that is both informative and engaging: