Tile Grid Map in Excel with Sparklines

Data maps are a great tool to combine spatial and statistical data together. Nonetheless, data maps do not come without shortcomings. One such flaw is to mistakenly associate the size of a geographic area with the statistical data related to that shape or area.

One way to solve the problem above is to use a Tile Grid Map such as the one for the US Taffic Fatalities by State--as shown in the main cover picture on the left. Here, equal weight is given to all geographic boundaries—states in this case—using a grid of equal-area squares. Hence the focus is given to the statistical data rather than geographic boundaries and shapes.

Unfortunately, Excel charting library doesn't include native capabilities to produce this type of charts. Users are either obliged to resort to VBA or to take the time consuming task of following a long list of steps to create one.

So here I'm offering you an Excel template that takes a dataset as an input and creates a Tile Grid Map. The Excel model has the following capabilities:

- Handles up to 3 variables, that is, variable 1 that defines the color of the square (i.e. Fatalities/100 million), variable 2 that defines the Sparkline within each square (i.e. traffic fatalities from 2010 to 2019), and variable 3 that defines the border of the square (i.e. Safety inspection).
- Show or hide the states initials.
- Show or hide the Sparklines within each square.
- Show or hide the contours/borders around the squares.
- Define the number formatting of the labels for variable 1 (i.e. Fatalities/100 million) and variable 2 (i.e. traffic fatalities from 2010 to 2019).
- User-friendly interface to adjust the above settings.
- Data entered into Excel Table—hence the chart and formulas update automatically when the datasource expands or shrinks.
- The size of the data matrix is only limited by Excel's charting and calculating capabilities.
- Fully customizable chart using standard Excel functionality.
- Unlocked workbook/worksheets with ability to expand template to suit your needs.

System requirements

The template relies heavily on Excel's Dynamic Arrays capabilities, only available to Microsoft 365 subscribers.

What's the refund policy?

If what you see is not what you expected, just reply to the download email within 30 days, and you'll get a full refund. No questions asked.