Stick a Shape to a Point in an Excel Chart

This article was inspired by the Mr Excel forum. A member asked Charts: Any way to make a shape stick to a point in a scatterplot, not an absolute location on the chart?

The answer, of course, is Yes, but you have to know the trick, which I’m about to share. Not for the first time.

The Problem – Shapes Won’t Stick to Points in the Chart

Suppose I have some simple X-Y data:

With this data I make a simple X-Y chart:

Now I draw a couple shapes on the chart to highlight two points with larger values than the others.

So far, so good. But when I add some more data…

… the chart’s axes rescale. The points move to stay at the same numerical position along the axis, but the shapes stay where they were originally placed.

So now I have to drag those shapes around whenever the chart axis scales change.

The Solution – Making Shapes Stick to Points

There is actually a pretty simple way to make the shapes stick to the points. The trick is to add a second series to the chart, with data duplicating only the points you want to draw attention to, and use the desired shape as the markers for this series.

Here is the data, with a third column containing the Y values I want to highlight. Also visible is the shape I’ll use.

Here is the original X-Y chart, with blue circles as markers.

Here is the same chart with the second series added, as orange squares that indicate which points will be highlighted.

The trick is simple. Copy the shape, select the series that I want to use the shape as its markers, and paste using a simple Ctrl+V.

Now when I add data, including another highlighted Y value in column C…

… the chart’s axis rescales, but the highlighted points keep their highlighting shape, and the added Y value is highlighted as well.

Enhancements to the Technique

You could use formulas in column C to place values where highlighting is desired, and #N/A errors where it isn’t desired. Below the formula in cell C2 might be


or better, the threshold for highlighting might be placed into another cell, say E2, to make it easier to modify the highlighting:


These formulas are then copied down column C as far as needed.

You don’t even need to draw a special shape if all you want to do is circle selected points. Instead of copying the shape and pasting it onto a series, simply format the series to use large circles as markers. Below the circular markers are size 17, with 2.25 point borders and no fill.

Peltier Tech Update

It’s been a busy few months for me, and I haven’t blogged very much lately. I spent a week in Amsterdam at the Excel Summit, where I met a lot of smart Excel users and Excel MVPs for the first time, and caught up with some old friends. I also did some on-site Excel VBA training for a couple companies, and this has reminded me that I want to do more activities like that.

I’ve also spent some time planning and working on new Peltier Tech products and services. Excel 2016 for both Windows and Macintosh will be released by Microsoft in the next several months, and I will release a major upgrade to the Peltier Tech Chart Utility.

Since traveling to training sites is a pain for me and for trainees, I’ve begun work on some live hour-long video training topics. Topics will include “Tricks to Make Excel Charting Less Painful” (based on a session at the Amsterdam Excel conference), “Getting Started with Excel VBA” (based on my recent training workshops), and advanced topics in charting, programming, and programming charts.

Peltier Tech Chart Utility

Removing Recent Colors from Microsoft Excel

Recent Colors

When you customize a color and apply it to an object in Microsoft Office, the color is stored as part of the Office file, and presented in a “Recent Colors” category on the various colors dropdowns. Up to ten of these custom colors are displayed, with the most recent at the left of the list and the oldest moving further to the right until a new color pushes it off the list.

The screenshot below shows three custom colors applied to cells B4:B6 of a worksheet, and where they appear in the color dropdown. The pink color was the latest applied, so it is furthest left. It is highlighted with a thin red outline because it is also the fill color of the active cell.

The numerical and alphanumeric codes in columns C and D are the decimal and hexadecimal representations of these colors. They were generated using the following VBA procedure:

Sub OutputColorInfo()
  Dim c As Range
  For Each c In Selection.Cells
    c.Offset(, 1).Value = c.Interior.Color
    c.Offset(, 2).Value = WorksheetFunction.Dec2Hex(c.Interior.Color)
End Sub

Colors are the combination of the three color channels (red, green, and blue), and each channel can have a value between 0 and 255 (or between 0 and FF in hexadecimal notation). The yellow color above has a blue channel value of 66, and red and green channel values of FF, so the code for this yellow is 66FFFF. Note that VBA presents the channels in BGR order, but it’s still called “RGB”.

A customer asked how to get rid of some but not all recent colors used in Excel. This is not too hard to do, but you should remember that any time you format an object with a new custom color, this new color becomes a new Recent Color.

Where are Recent Colors Stored?

Recent Colors appear in the color dropdowns only when the file they were defined for is the active file in its Office application (in this case, the active workbook in Excel). This means they must be stored somewhere within the document.

The first step in modifying the Recent Colors is to close the Office file. Below we see the workbook “recent colors.xlsm” in a Windows Explorer window.

Office files are really zip files which contain xml and other files within a well-defined directory structure. You can access this structure by simply adding the file extension “.zip” to the file name, and dismissing the warning about changing file extensions.

Double click the zip file to see the top-level structure of the file.

Double click on the “xl” directory within the file.

Finally, copy the “styles.xml” file, navigate out of the zip file to the directory the Office file was saved in, and paste the xml file here.

Open “styles.xml” in your favorite text editor. I use Notepad++, which can apply syntax-based color coding to files it recognizes. This is the entire content of my particular styles.xml file. Note the <mruColors> tag near the end of the file. This contains three <color rgb="blahblah"> tags that contain the definitions of our MRU (Most Recently Used) colors.

Changing Recent Colors

Microsoft Office doesn’t bother making the XML easy to see by using line feeds and spaces, but you can go ahead and apply your own, as I’ve done below.

Note that the RGB values in the <color> tags begin with an extra FF (for the transparency channel), and list the red, green, and blue pairs in the RGB order, not the BGR order produced by our VBA routine above. No big deal, Office uses the XML RGB for its user interface and VBA uses BGR, and they don’t talk to each other, so they don’t confuse each other, they only confuse us.

Delete the <color> tag(s) you don’t want (you could also change the color or add new ones if you can determine the desired RGB values). Save the xml file, and copy it back into the “xl” folder in the zip file. Delete the “.zip” extension you added to the Office file earlier, so it is left with its original Office extension (“.xlsm” for our workbook), and reopen the file.

The screenshot below shows the three colored cells, but only the two non-pink Recent Colors in the dropdown. We have successfully removed the recent color from the workbook’s remembered list, but we haven’t removed the color from any objects we formatted with it.

Note that reapplying the pinkish custom color to any object in the workbook will reestablish it as one of our Recent Colors.

Thanks to PowerPoint MVP Steve Rindsberg for showing me the <mruColors> tag I was too busy to notice the first time I looked. Check out Steve’s PowerPoint FAQ Pages and his PPTools – PowerTools for PowerPoint.

Peltier Tech Chart Utility

Error Bar Ideosyncrasy

After using Excel 2007 for a while, getting used to the idea that everything had changed and we weren’t going back to 2003 ever again, I started fooling around with charts and chart elements. I began to find lots of little changes, most of them actually good ones, even if they were implemented in a funny way (at least until service packs and Excel 2010 cleaned up things).

Anyone who used my first charting utility knows things ain’t always done right the first time, and mine were tiny bits of software. I can’t imagine Microsoft’s task trying to coordinate thousands of developers and a bazillian lines of code.

One of the neat changes I discovered was that error bars were now treated as AutoShape lines, just like any lines in any shapes in Office. This means you had a lot of formatting options available to you, including nice embellishments like arrowheads on the ends of the error bar line segments. Then I forgot about it until a colleague brought it up recently.

To show this nice formatting, let’s look at a simple line chart with simple error bars. For clarity, and to avoid spoiling the story before I’m ready to tell it, I’ll start with positive error bars only.

Simple Line Chart with Simple Error Bars

Select the error bars and click Ctrl+1 (numeral one) to open the Format Error Bars task pane. In the main tab (below the bar chart icon, shown below left) change End Style from Cap to No Cap. Then on the formatting tab (below the paint can icon) check out the Begin and End Arrow Types.

This is the Format Error Bars task pane in Excel 2013; the Format Error Bars dialog in Excel 2007 and 2010 is substantially the same.

Format Error Bar Dialog

The error bar begins at the point, right? And ends at the, uh, end of the error bar, right? At least that’s what seems to make sense. So let’s pick a nice big round ball for the Begin Arrow Type.

Balls at Beginning of Error Bars

And let’s pick a nice big old arrow for the End Arrow Type. I’ve chosen the largest size for both ends of the error bar.

Arrowheads at End of Error Bars

And here’s our line chart with error bars, where the markers of the line chart could be replaced by the balls at the beginning of the error bars, and the arrows point away from the line.

Simple Line Chart with Fancy Ball and Arrowhead Error Bars

This is a very nice embellishment. I’ve used it in several projects already, and shown it to many people.

Anyway, back to the beginning of the story: the message from my colleague was that Excel 2010 and 2013 seem to define the beginnings and ends of the error bars differently.

Well, for positive error bars, Excel 2007, 2010, and 2013 all look the same.

Simple Line Chart with Simple Error Bars

But if we look at negative error bars, only Excel 2013 shows them beginning at the points and ending at the ends of the error bars, pointing downward. Excel 2007 and 2010 show the balls at the ends of the error bars and the arrowheads at the points, pointing upward. That’s bizarre.

Simple Line Chart with Simple Error Bars

Even more bizarre, if the error bars go both ways, only Excel 2013 has symmetric arrows. Excel 2010 shows both sets of arrows pointing upward, the negative ones toward the points, the positive ones away from the points. And Excel 2007 only has one set of arrows, beginning at the end of the negative error bars, ending at the ends of the positive error bars. Though if you had to, you could just pretend the end was the beginning and vice versa. Whatevs.

Simple Line Chart with Simple Error Bars

Excel 2007’s charting infrastructure was finished quickly at the end of the development cycle, so a few glitches weren’t ironed out of the final product. Excel 2010 fixed a lot of inconsistencies in Excel 2007’s charts, but these error bars show that not everything was totally fixed. In Excel 2007 or 2010 you could fake it with a second, hidden plotted series, and format two sets of error bars so they looked right. Excel 2013 has gotten the error bars working nicely, though.

Peltier Tech Chart Utility

Highlight a Specific Data Label in an Excel Chart

I was asked recently whether it was possible to change the font color of a data label in an Excel chart to highlight the maximum value.

Well, sure, anything is possible. And there are at least two ways to accomplish this task. Because I’ve been doing a lot of coding lately, my first thought was an approach using VBA. Then of course I came to my senses, and worked out a non-programmatic approach. If possible, it’s usually advantageous not to rely on VBA for such tasks.

The VBA Approach

Because I thought of it first, I’ll show the VBA method of formatting the label for the maximum value first.

Here is the simple data and chart, with all labels formatted with white text.

Highlight Max Data Label with VBA - Data and Chart 1

For this simple example, I want the tallest bar to have a black label, like this:

Highlight Max Data Label with VBA - Data and Chart 2

Here is the simple VBA routine I worked out to accomplish my task.

Sub HighlightMaxDataLabel()
  Dim srs As Series
  Dim vY As Variant
  Dim iPt As Long, nPts As Long
  Dim dMax As Double
  Dim iHighlightColor As Long

  ' do nothing if user hasn't selected a chart
  If Not ActiveChart Is Nothing Then
    Set srs = ActiveChart.SeriesCollection(1)

    ' highlight color: change to suit
    iHighlightColor = RGB(0, 0, 0)

    ' reset all labels to original font color
    With srs.DataLabels.Font
      .Color = .Color
    End With

    vY = srs.Values
    nPts = srs.Points.Count

    ' find maximum value
    dMax = vY(1)
    For iPt = 2 To nPts
      If dMax < vY(iPt) Then
        dMax = vY(iPt)
      End If

    For iPt = 1 To nPts
      ' highlight all labels at maximum value
      If vY(iPt) = dMax Then
        srs.Points(iPt).DataLabel.Font.Color = iHighlightColor
      End If

  End If

End Sub

When the data changes, the labels don’t immediately change.

Highlight Max Data Label with VBA - Data and Chart 3

Run the code again, and the labels are now properly highlighted.

Highlight Max Data Label with VBA - Data and Chart 4

Of course you could modify the code and stick it into a Worksheet_Change event procedure to make the labels change when the data changes.

I made sure that the code checked all values, without stopping at the first maximum. This way, both labels are highlighted if there’s a tie for first.

Highlight Max Data Label with VBA - Data and Chart 5

The Non-Programmatic Approach

Here is the same data with a couple extra columns, and the column chart without data labels. The added columns provide data for hidden line chart series which will contain the differently formatted data labels.

Assuming the data is in A1:D6, the formulas are:

Cell C2: =IF(B2<MAX(B$2:B$6),B2,NA())

Cell D2: =IF(B2=MAX(B$2:B$6),B2,NA())

These formulas result in only one of the two line chart series having a marker for each column of the column chart.

Highlight Max Data Label without VBA - Data and Chart 1

Start by making the chart using all of the data (left), or if you’ve already got the chart, add the extra series. Then change the chart type of the additional series* to line chart (right).

* right click on the series, choose Change Series Chart Type from the pop up menu, and select the desired chart type.

Highlight Max Data Label without VBA - Charts 2

Add data labels to each line chart* (left), then format them as desired (right).

* right click on the series, choose Add Data Labels from the pop up menu.

Highlight Max Data Label without VBA - Charts 3

Finally format the two line chart series so they use no line and no marker.

Highlight Max Data Label without VBA - Data and Chart 4

When the data change, the chart labels change just as quickly as Excel can calculate the new values in columns C and D. No need to hassle with VBA event procedures.

Highlight Max Data Label without VBA - Data and Chart 5

If more than one value matches the maximum, each will be highlighted as the maximum.

Highlight Max Data Label without VBA - Data and Chart 6


Peltier Tech Chart Utility

Color Plotted Points to Match Cells

This week in the Mr Excel forum, someone wanted to know how to set Graph Colors to match cell colors. A couple years back, my good buddy Mike Alexander presented code that Color Pie Chart Slices to Match their Source Cells. But I thought of a couple enhancements and I need to increase my posting frequency, so here goes.

Basic Data and Chart

Start with a simple data set:

Simple Data

Create a stacked column chart:

Simple Chart

This will work with stacked or clustered column charts, stacked or clustered bar charts, and pie charts.

Formatted Data and Chart

Enhancement Number One: Work on all reasonable chart types that use fill colors for each point.

Apply the fill colors to the cells that you want applied to the plotted points. Just fill colors; the VBA code will ignore borders and skip any cells that don’t have a simple “Solid” fill pattern.

Data with colored cells

Select the chart and run the procedure. The points (bars) will be filled with the same colors as the corresponding cells.

Chart with colored points

It works on bar charts too.

Chart with colored points

Note that the legend colors haven’t changed, because we’ve changed the points one-by-one, and haven’t changed the series. Even if you changed all points in a series to the same new color, the legend will still show the old color. See?

Legend colors do not change

Partially Highlighted Data and Chart

Enhancement Number Two: Apply fill colors only to certain highlighted points.

That was pretty cool. What if I only want to change the colors of some of the points, indicated by the selective colors of the following data set?

Data with highlighted cells

Look, it works! The program skips any cells with no fill color (in code, it skips cells with a fill pattern of “None”).

Chart with highlighted points

The VBA Procedures

The first procedure is a stub that is used to reformat the active chart.

Sub ColorActiveChartPointsToMatchCells()
  If Not ActiveChart Is Nothing Then
    ColorPointsToMatchCells ActiveChart
  End If
End Sub

You can call the main procedure inside of any other code to format a chart you’re working on, like this:

    ColorPointsToMatchCells ChartIAmWorkingOn

The second procedure accepts a chart as input. It cycles through all series in the chart. If the chart type of the series isn’t column, bar, or pie, it skips that series. Otherwise it parses the series formula to find the formatted range containing the series Y values. The code then loops through the points in the series (and the cells in the source data range), and if the cell has a simple “Solid” fill pattern, it uses the fill color of the cell as the fill color of the corresponding point.

Sub ColorPointsToMatchCells(cht As Chart)
  Dim srs As Series
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sYvals As String
  Dim rYvals As Range
  Dim iPt As Long
  Dim nPts As Long

  If cht Is Nothing Then GoTo OuttaHere

  For Each srs In cht.SeriesCollection
    Select Case srs.ChartType 
      ' only do pie, bar, column charts
      Case xlPie, xlBarClustered, xlBarStacked, xlBarStacked100, _
          xlColumnClustered, xlColumnStacked, xlColumnStacked100

        On Error GoTo SeriesError

        ' get series information
        sFmla = srs.Formula
        nPts = srs.Points.Count
        vFmla = Split(sFmla, ",")
        sYvals = vFmla(LBound(vFmla) + 2)
        Set rYvals = Range(sYvals)

        For iPt = 1 To nPts
          ' don't change point color if cell has no fill color
          If rYvals.Cells(iPt).Interior.Pattern = xlSolid Then
            srs.Points(iPt).Interior.Color = rYvals.Cells(iPt).Interior.Color
          End If
    End Select

    On Error Resume Next


End Sub

Note that I used

    srs.Points(iPt).Interior.Color = rYvals.Cells(iPt).Interior.Color

to format the points of the series. The Interior property of a chart series has been deprecated, and the official syntax has changed to the much simpler and easier to remember

    srs.Points(iPt).Format.Fill.ForeColor.RGB = rYvals.Cells(iPt).Interior.Color

But it seemed to make sense to use Interior for both cell and point, especially in this case deprecated doesn’t mean “no longer works”.

Peltier Tech Chart Utility

Peltier Tech Chart Utility


Create Excel dashboards quickly with Plug-N-Play reports.