For several years, the Wall Street Journal has occasionally used a square-format US map like the following image. But when I noticed a recent version of it at Axios recently, I decided to set one up in Excel. You can download it at this link.
The Axios version was easy to set up because it had only two colors, and it uses the Axios data.
The advantage to using a map like this is that we can set it up easily with ordinary Excel. All it requires is an Excel Table that its conditional-formatting formulas can reference.
The point of the figure is that the US government is paying unemployed people an extra $300 per week because of Covid. But many businesses are finding it difficult to hire people, because many of them would rather stay home and draw unemployment—rather than work.
Therefore, to encourage people to go back to work, slightly more than half of the states have stopped paying that extra $300 each week.
To create the map, I first set up the Table by searching online for state abbreviations, and I found many such examples. The version I used included the data in columns B, C, and E, which I’m not using for the map.
I added column F to the Table, where I entered the value 1 for each state that Axios said had ended the extra payments. I could have entered TRUE instead, but using 1 took less typing.
I set up a grid with the fat and skinny rows and columns that you see for the map. And then I added the gray fill for all 51 of the state abbreviations. (The 51st “state” is the District of Columbia.)
Then it was time to apply conditional formatting to the map, which you can see again here:
I selected the range B2:X16. In the Home, Styles group in my Ribbon, I selected Conditional Formatting, New Rule. In the New Formatting Rule dialog, I selected Use a formula to determine which cells to format.
Then, in the Format values where this formula is true edit box, I entered this formula:
=IF( B2<>””, INDEX(xt.State.Early, MATCH(B2,xt.State.Code,0)) )
First notice the two references to cell B2. I used B2 in the formula because that was my active cell. And I was very careful to make those references relative. That is, neither of the references had the dollar signs that would have made the references mixed or absolute.
By setting up relative references like that, I was telling Excel to apply the rule to each cell in my selected range, B2:X16. In other words, as Excel’s conditional-formatting engine works on each cell, it treats each cell as the active cell for the purpose of applying the formula.
The IF statement tells Excel’s conditional formatting rule to ignore all empty cells. Notice that this statement doesn’t include a Value_if_false argument. That’s because the Value_if_false argument is optional, with a default value of FALSE. To see what I mean, enter this formula…
=IF(0, 99)
…in some cell, and you’ll see that the formula returns FALSE. (Excel treats zeros as false and all other numbers as true.)
The INDEX function is where the real work is done. To see how the formula works, suppose Excel is working on cell H6, which contains the text “MT”, for Montana. Here’s the formula for that cell:
=IF( H6<>””, INDEX(xt.State.Early, MATCH(H6, xt.State.Code,0)) )
The rule’s INDEX function tells Excel to return the value from a row in the xt.State.Early column. And the MATCH function tells Excel which row to return that value from. To do so, MATCH searches the xt.State.Code column for the “MT” text—which is in the current cell—and then returns the row-index number in which “MT” is found. And in the last argument for the MATCH function, the value of zero tells MATCH to look for an exact match.
Because INDEX always will return the values 1 or 0—which Excel evaluates as TRUE or FALSE—the conditional-formatting rule applies the formatting only when INDEX returns 1.
Setting up the formatting is easy. In the New Formatting Rule dialog, after I had set up the formula, I clicked Format…, which launched Excel’s Format Cells dialog. And it was there that I set up the red fill and the bold, white text that you see here and in the square-format map.
To change the color, you can change it in either the Custom or Standard tab, and then choose OK.
You can download the workbook at this link.
Two Updates
First, as one reader suggested, you could use thick border lines to separate each box, rather than narrow rows and columns. The border lines won’t be as thick as the columns, but the map could be easier to set up.
Second, in response to questions from other readers about this topic, check out How to Set Up Multiple Conditional Formats with Formulas, which takes the square-format map a step further. And also check out How Excel Manages Conflicting Conditional Formats with Formulas, which explains how both conflicting and overlapping conditional formats work.
