I’ve been recently using the great guides from (what used to be) SpreadsheetStyle.com.
I’m a real fan of the 3 crayon rule.
I see other Excel users with a rainbow of colours on their sheets. I ask them “why all the colours?” and they reply “see, the red cells are errors and the green cells do this and the grey cells do that and… well… noone else uses this spreadsheet so only I need to know what the colours mean”.
Only they need to know! Until they get another job. Why were those cells highlighted again?
Here is where it’s nice to have a worksheet legend:
Leaving some cells dedicated to a Legend can be a little cluttered, so here is a way to put the legend in as a cell comment.
I used an Excel add-in for exporting my range as a picture. That saves me from writing a macro to do the same.
There are a few add-ins which can do this. I use Andy Pope’s Graphics Exporter
Highlight your range, say, A1:D15
From the Tools menu, select Graphics Exporter.
Note the Output folder and click Export.
Click a blank cell. From the Insert menu, select Comment.
Delete the text in the new comment box.
Notice how the border style is diagonal lines. Click it and it changes to a dotted border style.
From the Format menu, select Comment.
The Format Comment window appears
From the Colors and Lines tab, click the Fill Color dropdown box and select Fill Effects.
The Fill Effects window appears
From the Picture tab, click Select Picture.
Browse to where you exported that picture, select the file and click Insert.
Tick the ‘Lock picture aspect ratio’ box.
Click OK then OK again.
Depending on your comment box defaults, you’ve probably got what appears to be a tiny picture in the comment box.
You can resize it to whatever dimensions you like, but here is a tip to get your comment box matching the dimensions of your picture.
(This will only work if you ticked the ‘Lock picture aspect ratio’ as described earlier)
Resize the comment box so that it is quite tall.
Nudge the width wider and wider until it starts scaling the picture.
The point where it starts scaling the picture is where both the picture and comment box dimensions match.
Then, with the Shift key held down, drag the bottom-right handle so it resizes both height and width at the same time.
(The Shift key maintains the width/height ratio)
In the end you should end up with something like this: