Build A Tile Grid Map of The United States In Excel
What Is a Tile Grid Map?
A tile grid map is a visualization technique used to represent data related to geographic regions in a simplified, abstract manner. Instead of showing regions with their actual geographic shapes and sizes (as in a typical map), a tile grid map represents each region with a uniform shape (typically a square or hexagon) and arranges these shapes in a grid that somewhat resembles the actual geographic layout of the regions.
These maps can also be referred to as:
- Geogrids
- Equal-Area Maps
- Square Maps
- Tiled Maps
Below is an example of what I will show you how to create in Microsoft Excel, representing the United States of America. This is also something that you can easily turn into a dynamic chart or incorporate as a map dashboard.
The primary benefit of a tile grid map is its ability to make regional comparisons easier. Because each region is represented by an equally sized shape, differences in color or shading (which represent data values) become more apparent. This can help avoid biases that might arise from the varying sizes of regions on a standard geographic map.
For example, on a standard map, larger regions can dominate visually even if their data values are not particularly high. A tile grid map helps to counteract this by giving equal visual weight to each region, making it easier to compare data values across regions.
How To Create A Grid Map in Microsoft Excel
Step 1: Make A Square Grid
First, you will need to create a square grid within your spreadsheet. Excel’s default cell shape is a rectangle, so you will need to select and resize your rows/columns to ensure they have the same pixel size to create a perfect square grid. In the example map I’m using, I made my square 60×60 pixels.
For this example, you can resize columns A:R and rows 1:11 to make a large enough grid. Or you can click the upper grid corner to select all cells and resize the entire spreadsheet to your liking.
Step 2: Draw Your Map With Cell Borders
Each region (for example, states in a country) is represented by a single tile, most commonly a square.
The tiles are arranged in a grid pattern. The grid doesn’t strictly follow geographic boundaries but attempts to approximate the relative positions of the regions. Neighboring regions in reality are placed adjacent to each other in the grid, if possible.
Step 3: Label Your Map Squares
Here comes the very manual part of this process. You will need to go through and add labels to your map to ensure your audience understands what each square represents. If you are building a map of the United States, you can use my clean list of the 50 state abbreviations to grab the values and start typing them in.
to add your labels, there are two methods you can choose between depending on whether you need to store data in each state that drives the shading or if you are just creating a visual that you will manually shade.
Label Method 1: Display Text Only
You can use my clean list of the 50 state abbreviations to grab the values and start typing them in.
Label Method 2: Show Text But Store Data Within
If you want to store numerical values within your map regions, you’ll need to utilize Custom Number Format rules to visually show the label and hide the actual value of the cell to your audience. This is akin to wrapping a present, even though you can only see a wrapped present, there is a specific object within the wrapping paper.
Step 4: Format Your Map For Data Visualization
Each tile can be colored or shaded based on some data metric. For instance, if you’re looking at a tile grid map of the United States, and you’re visualizing GDP by state, each state might be a square, and the color of the square would indicate the GDP value.
The below example uses conditional formatting to highlight any state with more than $20M in Sales.
To associate data with your map, you will need to write lookup formulas in each state. Again, this is unfortunately a manual process, but the work isn’t too tedious once you get going.
For this example, I used an XLOOKUP function to look up each state abbreviation from an Excel Table called “TBL_States”.
Once I wrote the first formula, I simply copied it to all of my map tiles. Then begins the manual work of clicking into each formula and adjusting the state abbreviation.
Download My Excel Example File
If you don’t want to build your map from scratch or want to further dissect how I made the example used in this article, you are more than welcome to download my Excel file by clicking the button below. Enjoy!
I Hope This Microsoft Excel Article Helped!
Hopefully, I was able to explain how you can create a tile grid map inside your Microsoft Excel spreadsheet. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.