Adding a color bar to a chart

Question

If I create an x-y-scatter plot in Excel and use Coloring each point of a chart based on data using sequential or divergent color scales to colour the points according to some data, how can I add a colour bar to the chart to show the colour scale? For example like MATLAB's colorbar.

Dan Dan · Accepted Answer · 2016-03-17T07:23:38

Full example on GitHub: https://github.com/DanGolding/Scatter-plot-with-color-grading-in-Excel

For this answer, I am assuming you used the last method in this answer to colour your data. In other words, somewhere on your sheet, you have a a list of RGB triples over 3 columns, 1 colour per row. So something like columns C - E in this screenshot:

The strategy for creating the colour bar is to create it on a blank sheet by colouring cell backgrounds of cells with very short row height to make the colour bar and then using cell border to make the tick marks. The tick mark labels will use formulae so that the colour bar is dynamic. Some cells are merged due to the tiny row height. All of this is automated by the macro that follows. We then create a linked image of the colour bar and position that over our chart.

This is the macro. You need to make a couple of changes depending on your sheet. For example, in this macro that data that defined the colouring (so not the colours themselves) is in the range Colour Map (Divergent)'!I:I which you need to define the max and min of your colour bar. With divergent data you might actually want to make these values the same (i.e. choose the absolute maximum and the negative of that). It also assumes that your colour triples are in columns C - E of the sheet Colour Map (Divergent).

Run this macro on a new blank sheet:

Sub MakeColourBar()
    'NB!!! Only run this on a blank sheet!
    'NB!!! You need to put the min (Start), max (End) on the sheet yourself manually
    Range("A260").Value = "Start"
    Range("D260").Value = "=MIN('Colour Map (Divergent)'!I:I)"
    Range("A261").Value = "End"
    Range("D261").Value = "=MAX('Colour Map (Divergent)'!I:I)"
    Range("A262").Value = "Step"
    Range("D262").Value = "=(D261-D260)/8"

    Dim n as integer
    n = 256

    'This assumes there are RGB colour data on another sheet. Change the sheet name and columns below as needed
    Dim sheetMap As Worksheet
    Set sheetMap = Worksheets("Colour Map (Divergent)")
    Dim row As Integer
    For row = 1 To 256
        Range("B" & row + 1).Interior.color = RGB(sheetMap.Range("C" & n - row + 1).Value, sheetMap.Range("D" & n - row + 1).Value, sheetMap.Range("E" & n - row + 1).Value)
    Next row

    ActiveWindow.DisplayGridlines = False
    Rows("2:257").RowHeight = 2
    Rows("1:1").RowHeight = 7.5 'This is for the tick mark labels
    Rows("258:258").RowHeight = 7.5 'This is for the tick mark labels
    Columns("B:B").ColumnWidth = 2.14

    With Range("B2:B257")
        .Borders(xlEdgeTop).Weight = xlMedium
        .Borders(xlEdgeRight).Weight = xlMedium
        .Borders(xlEdgeBottom).Weight = xlMedium
        .Borders(xlEdgeLeft).Weight = xlMedium
    End With

    Range("D1:D6").Merge
    Range("D1").Value = "=D261"
    Range("D253:D258").Merge
    Range("D253").Value = "=D260"
    'Merge rows for tick marks
    Dim mark As Integer
    For mark = 1 To 8
        Range("C" & (mark - 1) * (256 / 8) + 2 & ":C" & (mark) * (256 / 8) + 1).Merge
        Range("C" & (mark - 1) * (256 / 8) + 2).Borders(xlEdgeTop).Weight = xlMedium
        'Make the tick mark labels by merging the 10 cells in column D that center around each tick label
        If mark > 1 Then
            Range("D" & (mark - 1) * (256 / 8) + 2 - 5 & ":D" & (mark - 1) * (256 / 8) + 2 + 4).Merge
            Range("D" & (mark - 1) * (256 / 8) + 2 - 5).Value = "=D" & (mark) * (256 / 8) + 2 - 5 & " + D262"
        End If
    Next mark
    Range("C257").Borders(xlBottom).Weight = xlMedium

    Columns("C:C").ColumnWidth = 0.42
    Columns("D:D").VerticalAlignment = xlCenter
    Columns("D:D").HorizontalAlignment = xlLeft

End Sub

This creates the following in your new sheet:

Note I have put the grid lines back in for this image just to illustrate what is going on here. But for this to work nicely you must turn the gridlines off, which the above code does for you. The grid lines show how the colour bar is just made up of a column of cells with their background colours adjusted with very small row height (Column B). The tickmarks are made using cell borders of merged cells (column C) and the tick mark labels also use merged cells centred around the tick marks (column D).

Now highlight cells B3:D258 of the new sheet, copy and then on the sheet with your chart paste as a linked image:

On your chart, resize the plot area to show some white space on the right of your chart area:

And then finally resize your linked image (making sure you keep the aspect ration constant!) and position it in this white space. You can then adjust the font size of column D of you new colour bar sheet to get the number sizes to match those on your chart.

The final result:

Adding a color bar to a chart

1 Answers