Yesterday, I wrote a tutorial on how to create a static tile grid map in Excel–today, I move onto creating an interactive version. The static tile grid maps are quite nice, but an interactive version would be kind of awesome. Creating interactive data visualizations in Excel is somewhat limited and clunky, and with new drop-and-drag tools like PowerBI and Tableau, it may not even be necessary, but the approach I use here simple and fast, relying on a number of formulas, data validation, and conditional formatting. The interactive version allows the user to select three variables: state, gender, and year. The selected state is highlighted on the map and on the accompanying bar chart. I’ve added the bar chart because it enables the user to more easily compare values across the different states. I’m again providing you with the Excel file so you can modify it to use your own data and colors.
The data I have in this example is the number of men and women receiving Social Security benefits in 2003 and 2013 for all 50 states plus DC—so, 4 data series altogether. I have the visualization on one tab and the data on another. In the data tab, I have created a data table that consists of the state abbreviation (Column A), state name (Column B), and all four data series (Columns C-F). Above (in cells C1-F1), I’ve calculated the medians for each group using the MEDIAN function; other versions contained in the Excel file split the data into 3 or 4 groups.
First, the interactivity. I’m going to use Excel’s Data Validation tool. All you need to do is select the cell in which you want the user to make the selection and navigate to the Validate menu in the Data tab on the ribbon.
There are a variety of options here and I’m going to select List under the Allow dropdown and then refer to the list of state names (in my Data worksheet). Once I click OK, the cell will populate with those 51 options. I do a similar thing for Gender (cells D2:E2) and Year (cell E3:D3).
Now for the map. The data are fed into the map using a combination of VLOOKUP and MATCH statements. Take the formula for Alaska, for example (notice, obviously, this is quite a bit more complicated than the static version):
Let’s break down this formula:
- First argument: B34: This looks for the state abbreviation in another (hidden) map that sits below the main tile grid map (in cells B34:L41) with the state abbreviations as the cell values, and not number formats as in the static version (see the image below).
- Second argument: ‘DATA-2Groups’!$A$4:$F$54: This looks for the state abbreviation in the data table that contains all four data series.
- Third argument: 1+MATCH($E$3,’DATA-2Groups’!$C$2:$F$2,0)+MATCH($G$3,’DATA-2Groups’!$C$3:$F$3,0): This is the big one. I use a MATCH function here to find the position of the exact series I want. (The MATCH function pulls out a position from a given reference.) The first MATCH argument looks for gender (cell E3) and the second MATCH argument looks for the year (cell G3). With the position set this way, the VLOOKUP looks across the columns in the data table. (Note: the +1 at the start moves the cell references over one space as needed because of how the data are set up).
- Fourth argument: 0: This is the exact match argument in the VLOOKUP. (Note: Many people often ignore this argument, but it is important to specify).
- Another note: If you’re not familiar with the VLOOKUP, INDEX, and MATCH formulas, I urge you to learn them, as they are really important. Dave Bruns over at ExcelJet has some great video tutorials.
Because the formula for each state is the same, except for the first argument, you can just copy and paste to every state (this works because I’m using absolute references in the formulas, denoted by the dollar signs). Each cell is also given a custom number format: “AK: “#,##0 will show the state abbreviation and a colon, followed by a space, followed by the data value. Again, this part is tedious because you have to format each cell separately.
I again use Conditional Formatting to apply the colors to the map. The dark blue (for the selected state) is assigned using the State Value cell (cell H6 in the DATA worksheet) and the yellow-blue split for the other states are assigned using the Median (cell H5). The median value is pulled from the calculated values in the top row of the data tab (cells C1:F1) using an INDEX and MATCH combination formula. I won’t describe how to do that here–you can see take a look in the Excel file–and again, it’s worth learning these formulas.
Onto the accompanying bar chart.
There are a few things that need to happen here to get the bar chart set up so that it is sorted from highest value to lowest value.
- First, a separate data table in the data tab includes the state name (Column O) and state abbreviation (Column Q), each hard-coded so that they don’t change.
- The selected data series appears in Column R, and is populated by using a similar VLOOKUP formula as the one described above to pull the selected series into that column.
- The rank of the selected data series (now in column R) appears in Column P, by simply using the RANK formula.
That’s just the set up. The series used in the actual bar chart are found next door in Columns J-M.
- Column J is the rank of the states, and this is hard-coded because I want the largest values at the top of the chart.
- Column K consists of the state abbreviations, which get filled in by using a VLOOKUP [=VLOOKUP(J4,$P$4:$R$54,2,0)] on the rank of the data series. In other words, Column K maps the rank from Column P over to the sorted rank in Column J.
- Column L is the first series of the bar chart and embeds a VLOOKUP in an IF statement [=IF(K4=’2Groups’!$C$3,0,(VLOOKUP(J4,$P$4:$R$54,3,0)))]. Here, if the state abbreviation in Column K matches the selected state, it gets assigned a value of zero; otherwise, it’s assigned the data value. In the chart, this series is filled with a grey color.
- Column M does the opposite of Column L: it fills the cell with the value of the selected state, and zero otherwise. In the chart, this series is filled with the dark blue color used in the conditional formatting for the map.
In other words, I’m using a stacked bar chart so that the selected state—with the darker color—sits along the vertical axis because it is assigned a value of zero in the (grey-filled) series with the values for all the other states.
There you have it: An interactive map-bar chart combination in which the user can choose the state, gender, and year. Once you have the template set up, it’s easy to expand the data by modifying the lookup formulas.
Hopefully, this tutorial has shown you some basic Excel interactivity, conditional formatting, and some good formulas to use. Let me know if you have any questions or if you get stuck.