How to Set Up Multiple Conditional Formats with Formulas

In How to Set Up a Square-Format U.S. Map in Excel, I showed how to create a square-format US map in Excel. I also explained how to set up one conditional format with formulas to show which states had stopped paying a $300 bonus unemployment rate.

But then a reader asked how to set up more than one format, with formats that overlay. So, for example, she said, what if she wanted to show the blue, red, and purple states that had stopped paying the $300 unemployment rates.

That is, she wanted to know how to set up a map like this. (You can download the workbook with this map.)

Because the answer to her question would rely on conflicting or overlapping conditional formats, I first wrote How Excel Manages Conflicting Conditional Formats with Formulas.

When you look at the map, and if you follow U.S. politics at all, there’s a good chance that you’ll disagree with my red, blue, and purple assignments. When I searched the web, I could find no two sites that could agree about the political leanings of many of the states. Therefore, this map uses colors assigned by one site I picked at random.

If you disagree with the colors, you can download the workbook and then assign the colors you prefer.

All of the squares in this map have overlapping—or conflicting—color rules. For example, look at the red square for Alaska, in the top-left corner of the map. Alaska normally would be solid red. But because it also is one of the “early states,” the conditional formatting rules also assign the diagonal white format you see for Alaska.

So, as I started to explain this map, I realized that the overlapping and conflicting conditional formats needed extra explanation. This is why I first posted How Excel Manages Conflicting Conditional Formats with Formulas.

To create this version of the map, I started by adding the “RBP” column to the Table in Square-Format article. This column contains the first letters for Red, Blue, and Purple.

And then, with this new column in place, I could set up my rules for the square-format map shown again here:

We need four conditional-formatting rules for this map:

Notice in the Applies to column of this dialog that the rules apply to the entire map, which includes the white areas. The white areas stay white because, for any white cell, all four formulas in the Rule column return false. And that means that none of the formats are assigned.

For the Alaska square in cell B2 of the map, here are the first three formulas, which assign the red, blue, or purple colors:

= INDEX( xt.State.RBP, MATCH(B2, xt.State.Code, 0) ) = “R”
= INDEX( xt.State.RBP, MATCH(B2, xt.State.Code, 0) ) = “B”
= INDEX( xt.State.RBP, MATCH(B2, xt.State.Code, 0) ) = “P”

(When the INDEX part of these formulas compares the letter it’s returned to the  “R”, “B”, or “P” constants, we don’t need to use IF. This is because, if there’s a state abbreviation in the cell, Excel will return TRUE for one of those tests and FALSE for the other two. And if there’s anything else in the cell, or if it’s empty, all three tests will return FALSE.)

Each of these uses an INDEX-MATCH formula to assign a purple, blue, or red color to each square. In the first of these, the formula says…

• For the INDEX chunk: Return the R, B, or P letter from the xt.State.RBP column for the row specified by my second argument.
• For the MATCH chunk in INDEX’s second argument: Look up the contents of my cell (“AK” in this instance) in the xt.State.Code column, and return the row number where that text is found. MATCH’s third argument, the zero, tells MATCH to return an error value if “AK” isn’t found.

If your version of Excel includes the XLOOKUP function, you could use these formulas instead:

= XLOOKUP( B2, xt.State.Code, xt.State.RBP )=”R”
= XLOOKUP( B2, xt.State.Code, xt.State.RBP )=”B”
= XLOOKUP( B2, xt.State.Code, xt.State.RBP )=”P”

Each of these formulas says: Look up the contents of my cell (“AK” in this instance) in the xt.State.Code column, and then return the R, B, or P letter from the xt.State.RBP column from the row in which the B2 value is found.

(When we compare the letter returned by the XLOOKUP function to the  “R”, “B”, or “P” constants, we don’t need to use IF. This is because, if there’s a state abbreviation in the cell, Excel will return TRUE for one of those tests and FALSE for the other two. Or, if a cell is empty or contains any other text, all three tests will return FALSE—which means that no fill color will be applied.)

The final rule applies the diagonal pattern for each square that’s an early state. Its formula is either…

=IF(B2<>””, INDEX(xt.State.Early, MATCH(B2,xt.State.Code,0)) )

…or…

=IF(B2<>””, XLOOKUP(B2,xt.State.Code,xt.State.Early))

…depending on your version of Excel.

Notice that this last rule overlaps the first three rules, but it doesn’t conflict. Any one of the first three rules applies a fill color, and the fourth rule applies the Pattern Color and Pattern Style shown here in the top area of the Format Cells dialog:

For the legend at the bottom of the chart, I just formatted the six cells manually. Keep this trick in mind, because formatted cells that contain text can make great chart legends.

Finally, remember that all of the colors and patterns shown in the map are completely controlled by the values in the Table. So if you would like to see New York displayed as a Red state, for example, just replace the “B” character with an “R” character in the Table.

Again, you can download the workbook with the map.