Creating a caption for conditional formatting in Excel 2016 and hyperlinking to that caption


Veteran of computer systems, malware removal and ransomware related topics. I have been working in the field since 1985.


Browse all articles > Creating a caption for conditional formatting in Excel 2016 and a hyperlink to that caption

This article addresses the sometimes extreme need for a caption that may not be easy to create, depending on the conditional formatting features you’ve used. Almost all spreadsheets need some sort of legend.

I thought the spreadsheet I created was not only pretty cool, but had a bunch of different conditional formatting (read more about conditional formatting in my article here ) which highlighted different rows depending on the values ​​entered, was pretty self explanatory. I was wrong about that, although it took a sideways comment for me to find out. The spreadsheet I created just tracked the membership of a relatively small group (less than a thousand). I had created about 25 different rules that determined the font and background assigned to each row (these were the conditional formatting rules). A colleague asked me to explain to him how it worked because he was a little confused. This guy is a former vice president of Pepsico, so no slouch. That’s when I realized I needed to include better instructions.

I decided that one way to make this clearer was to incorporate a caption. As I usually do, I researched both EE and the rest of the internet to find the best ways to create this legend. The result of my research boiled down to “Take a screenshot of the Rules Manager”. Which works, but the result isn’t as useful as you might think.

This screenshot method required me to take successive screenshots of the rulers (since a screenshot can only get 4-5 at a time, I needed 5 screenshots) screen.). Every screenshot I pasted into the spreadsheet. The process went as follows:

I went to the Conditional Formatting dropdown and selected “Manage Rules”

After selecting Manage Rules, the Conditional Formatting Rules Manager appears

Use any snipping tool (I used Windows 10’s built-in snipping tool) to capture and copy or save the selection. Then insert or paste it into your spreadsheet. The result will be similar to the one below.

Why not just do it that way? The answer was relatively easy for me, first of all the function of a legend should be explanatory, it really wasn’t. Although the formatting that would result if the rule were true is there, it just says “AaBbCcYyZz” for each rule. For this to make sense you will then need to match the formatting to what it means – not an easy task if you have a lot of rules.

Here’s what it looks like with a brief explanation alongside:

So after trying this suggestion I had an overnight epiphany, use the brush tool to copy the rules and change the value that triggers each rule. It was not at all easy. To make the legend clearer, I removed all the rules except the one I needed, which meant doing each part of the legend separately.

Although the result was more than satisfactory, it takes several steps to do it, depending on how many conditional formatting rules you have. The end result looked like this:

Compared to the cut-and-paste method:

The task went as follows:

The first step was to create the text I wanted each caption section to have. I decided it would be better to have dynamic text (change it in one place and it changes in the other). I create a basic formula that pointed to the text I wanted (=) and the target text was the title of a column. So the cell that says “Member BH” has an underlying formula of “=Table13[[#Headers],[BH Member]]” (without the quotation marks) and so on in the list. Items that contain an ampersand (&) have a slightly different formula, such as “=CONCATENATE($AE$2,” & “,AE3)” ( without the quotation marks).

Once all the names were created, I moved on to the next step. The second stage was to use the brush tool to copy all the conditional formatting to each caption cell. I did this one at a time, otherwise it got quite confusing which rules were applied to which cells.

  1. Copy, using the Brush tool, the formatting from inside the table to the first legend cell, labeled “BH Member”.
  2. Delete all rules except the one that applies to this legend entry
  3. Change the rule to fire when equal to cell text (if equal to itself)
  4. Then apply the rule and click OK

I repeated 1-4 for each caption entry.

The end result was the caption I posted above (I entered the word LEGEND in the first cell).

The third and final step was to create a hyperlink at the beginning of the document/spreadsheet that would take you directly to the legend. There were several smaller steps to do this.

  1. Name the range from where the word LEGEND is to the last piece of legend, “Legend”
    1. Highlight the range
    2. in the range name box (in the image it reads “A1”) type the name Caption to name the range or right click and select set name.
    3. Now that the range has been named, it’s easier to hyperlink to that caption. Type something like “go to caption” in a cell near your spreadsheet’s home cell. I highlighted the cell to make it stand out.
      1. Right click on the cell and select the hyperlink.
      2. The hyperlink dialog box will appear. Select “Place in this document” on the side and set name “Caption”
      3. Click OK when finished
      4. You may need to get rid of the underline
      5. When I was done it looked like this:

This is how I created the spreadsheet with a real legend.

I hope you found this article useful. You are encouraged to ask questions, report any bugs, or make any other comments about it below.

To note: If you need further “assistance” on this topic, please consider using the Ask a question expert swap functionality. I monitor questions being asked and would be happy to provide any additional support required in questions asked in this manner, along with other EE experts…

Don’t forget to hit the “Thumb’s Up” button if you think this article was helpful and valuable to EE members.

It also provides me with positive feedback. Thanks!


About Author

Comments are closed.