I decided that having dynamic text would be best (change it in one place and it changes in the other). The first step was to create the text that I wanted each legend section to have. To make the Legend clearer, I deleted all rules except the one I needed, which meant doing each part of the legend separately.Īlthough the result was more than satisfactory, it takes more than a couple of steps to do it, depending on the number of conditional formatting rules you have. So after trying that suggestion, I had an overnight epiphany, use the paintbrush tool to copy the rules and change the value that triggers each rule. Here is how it looks with a brief explanation next to it: To make it make any sense you would then have to match the formatting to what it means - no easy task if you have a lot of rules. Although the formatting that would result if the rule was true is there, it just says 'AaBbCcYyZz' for each and every rule. Why not just do it this way? The answer was relatively easy for me, first of all, the function of a legend should be explanatory, this really wasn't. The result will be similar to the one below. Then either insert it or paste it into your spreadsheet.

Use any screenshot tool (I used the Windows 10 built-in snipping tool) to capture and either copy or save the selection. I went to the Conditional Formatting dropdown and selected 'Manage Rules'Īfter selecting Manage rules, the conditional Formatting Rules Manager appears

Each screenshot I pasted into the spreadsheet.

This screenshot method required me to take successive screenshots of the rules (since a screenshot can only get 4-5 at a time, I needed 5 screenshots.). The result of my searches came down to 'Take a screenshot of the rules manager.' Which works, but the result is not as useful as you might think. As I usually do, I searched both EE and the rest of the internet for the best ways to make this legend. I decided that one way to make this clearer was to incorporate a legend. It was then that I realized I had to include some better instructions. This guy is a former VP of Pepsico, so no slouch. A colleague asked me to go over how it worked with him because he was a little confused. I had created about 25 different rules that determined what font and background each row was assigned (those were the conditional formatting rules). The spreadsheet I created just tracked membership for a relatively small group (less than a thousand). I was wrong about this, although it took an oblique comment in order for me to find that out. I thought the spreadsheet I created was not only fairly cool, but since it had a bunch of different conditional formatting (read more about conditional formatting in my article here ) that highlighted different rows depending on the values entered, explained itself fairly well.

Conditional formatting excel 2016