A scatter chart in Excel shows points along an X-Y grid. There are many varieties of scatter charts. The sample chart shows several stakeholders plotted with their Influence along the vertical axis and Impact along the horizontal axis.
During a recent IMA Excel webinar, someone asked if there was a way to automatically have the labels in the chart match the fill color of the cell. This is not a built-in feature of Excel, but it can be accomplished through a Visual Basic Application (VBA) macro.
Steps to Build the Scatter Chart
Microsoft had to make some odd design decisions when it introduced scatter charts. While most charts are built with the labels as part of the source data, the scatter chart is different. Do not include the stakeholder names from column B in the selected data. Instead, choose from the X and Y headings down to the bottom of the data. On the Insert tab, open the “Scatter (X,Y) or Bubble” drop-down and choose the top-left icon, the scatter chart without lines.
The heading for the Y axis automatically becomes the chart title. While this makes sense for most charts, it’s rarely appropriate for scatter charts. Select the title and either delete it or type a new title.
Use the large plus sign icon to the right of the top of the chart and add Axis Titles. Each axis title starts with the words “Axis Title.” Click on each one, type a new title, and press Enter.
Adding the names from column B as Data Labels requires several steps. First, you want to add the wrong label to the right side of each point. Use the plus sign to the right of the chart. Hover over Data Labels to expose the > icon. Click the > icon and choose Right as the location of the label. By default, Excel uses the Y value as the label for each point. You will change this in a moment.
If the Data Labels are still selected, you can press Ctrl+1 to display the Format Data Labels task pane. Alternatively, you can use the plus icon, hover over Data Labels, click the > icon, and then choose More Options.
In the Format Data Labels task pane, notice there are two text headings at the top: Label Options and Text Options. Make sure that Label Options is selected and in bold. This will reveal four icons in the next row: paint bucket, pentagon, resize icon, and a column chart with three columns. Click on the column chart icon. Beneath the row of icons are collapsed choices for Label Options and Number. Click the > icon to the left of Label Options to expand that section and reveal the Label Options choices.
You want to replace Y Value with Value From Cells. This has to happen in a specific order. First, choose Value from Cells. Excel will ask you to identify the cells. Select the names in B3:B12.
At this point, the labels will show both the name and the Y value, such as “Andy, 9.” Once you have the name included in the label for each point, you can unselect the Y value. You will now have the names successfully added next to each point. But the names will all be in black font on a transparent background.
Manually Changing Label Color
While the Format Data Labels pane is open and the labels are selected, you can change the fill color for all labels. The easiest way is to use the paint bucket icon on the Home tab to change the fill color. In Figure 7, the background color for each label is set to orange.
Notice the new “High-Contrast Only” choice at the top of the color panel. This will help you in choosing colors that should be easy to read.
It isn’t intuitive, but you can format individual labels one at a time. When all labels are selected as shown above, carefully click on the next label. In this case, it would be the label for Barb. When you click on Barb, the task pane changes from “Format Data Labels” (plural) to “Format Data Label” (singular). Notice how each label in the figure above is surrounded by four white dots. If you’ve successfully entered Single Label Format mode, the selected label is surrounded by six white dots. With one label selected, you can change the color as shown below.
Continue by clicking on the next label and changing the color. Repeat until all labels have a color applied.
Using a VBA Macro to Change the Label Colors
The process of manually changing the color for each label is tedious. It can all be automated with a VBA macro.
It’s likely that your workbook is currently stored with an .XLSX extension. This default file type doesn’t allow macros. You need to use Save As and change the extension to .XLSM or .XLSB. Don’t skip this step or the macro will be removed when you save and close the workbook.
It’s also possible that your Excel is set up to not allow macros to run. Go to File, Options, Trust Center. On the right side of Excel options, choose the button for Trust Center Settings.
In the Trust Center, the seventh choice along the left side is Macro Settings. Open Macro Settings. By default, the first option is chosen: “Disable VBA macros without notification.” Change this to “Disable VBA macros with notification.” While both of these sound similar, the important difference is that the latter option will give you a choice when you open the workbook to enable macros.
Press Alt+F11 to open the Visual Basic Editor.
From the Insert menu, choose Module.
Copy the following code and paste it into the newly created module in the VBA editor:
Sub FormatXYLabels()
Dim chrt As Chart
Dim rng As Range
Dim i As Integer
Set chrt = ActiveChart
' Is a chart actually selected?
If chrt Is Nothing Then
MsgBox "Please select the scatter chart to continue.", vbOKOnly + vbExclamation, "Chart not selected"
Exit Sub
' Is the selected chart is a Scatter Chart?
ElseIf chrt.ChartType <> xlXYScatter Then
MsgBox "The selected chart type is supposed to be Scatter Chart.", vbOKOnly + vbExclamation, "Wrong chart type"
Exit Sub
End If
' Find the range for the X axis
Set rng = Range(Split(chrt.FullSeriesCollection(1).Formula, ",")(1))
' Reset all background colors
With chrt.FullSeriesCollection(1).DataLabels.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(146, 208, 80)
.Transparency = 0
.Solid
End With
' Change fill color for each point
For i = 1 To rng.Cells.Count
With chrt.FullSeriesCollection(1).Points(i).DataLabel.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = rng(i).Offset(, -1).Interior.Color
.Transparency = 0
.Solid
End With
Next i
End Sub
In the VBA editor, use File, Close, and Return to Microsoft Excel.
To test the macro, select the chart. Press Alt+F8 to display a list of macros. Choose the FormatXYLabels macro and click Run. The color of each label should match the fill color from the grid.
It’s possible to have the macro assigned to an icon in the Quick Access Toolbar. Follow the steps in this video. Or you could have the icon assigned to a button in the workbook grid. Follow the steps in this video. Or you could have the icon assigned to a button in the workbook grid. Follow the steps in this video.
