During the past week when working on our first client project, I encountered a colouring problem when building my dashboard. Specifically, the value I wanted to use for colouring the bars has a couple of extremely high values, so the whole viz was looking very pale and it was hard to tell the difference between most values.



I came out a way to adjust the end value so that the colour difference was more obvious among most bars. However, the number I put in the end value would show up in the colour legend. This was misleading because actually the highest value is much higher than what the legend said.




I asked our coach Bethany for suggestion, and got a very useful tip – build my own colour legend in a separate sheet. First, make a copy of the origin sheet. Then calculate the window_min of the value I want to use for colour and put it into detail. Next edit the sheet title to build the customised colour legend. Select a character or icon like rectangle bar and colour it with the two ends of colours on my legend. Insert the window_min value as the start of legend, and manually type “> (the end point value I’ve adjusted on the legend)”. It should look something like this:


The last step is to put everything on column and row to detail because I don’t need a chart. Don’t remove them since I still need these details to make my window_min calculation correct. The final customised colour legend look like this, which is quite neat in my opinion:


I found this tip simple yet useful to deal with similar situation. The same method could be applied if I’ve got an extremely low value. And I can still keep one side of number on the legend dynamic by using window_min or window_max calculation. Hope you find this useful too!


Ming-Hsuan Lee
Author: Ming-Hsuan Lee