Formatting

Properties Follow Chart Data Point

Tuesday, October 9, 2018 by Jon Peltier 9 Comments

Tuesday, October 9, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

The Problem

You’ve made a nice custom chart, and now you want to make copies of it with new data. The copies come out fine, but when you change to new data, they lose their custom formatting. What’s wrong? The culprit is an Excel setting called “Properties Follow Chart Data Point.”

I originally wrote this post several years ago, before Microsoft had any documentation about this property. I have added a shorter summary to the top of this article, but you can read to the end for more examples.

“Properties Follow Chart Data Point”

What does that phrase even mean? It’s not clear, and if you do an internet search, the first two pages of search results have lots of forum posts. The forums mention it as a way to change some unexpected and undesired behavior, but nobody describes what it means that “Properties Follow Chart Data Point”.

Microsoft has finally added some documentation in its article about Advanced Options. They say, “Select this option to have formatting and data labels follow data points when they move or change.” Okay, I know what those words mean, but I don’t know what that sentence means.

To help explain this strange setting, I’ll show a couple of simple scenarios which I’ll use to describe how the property works. Then I’ll show a real-world example where it can mess up a small multiple chart layout, for example, in your dashboard. After the examples I’ll show how to change this setting.

Which Properties Follow Which Data Point?

This option affects custom properties, including most but not all series formatting, and some but not all data labels and label formatting. This custom formatting is linked to (that is, it follows) the cells that contain the data which contains the data being formatted and the labels being displayed.

Custom Series Formatting and Data Labels

This first example shows one series in a chart with custom formatting: series ‘beta’, the second series in the chart is formatted with red and white diagonal stripes, and its data labels are bold and red. I’ve also applied the diagonal red and white pattern to the worksheet range that contains the data for ‘beta’. The chart is selected so that the plotted data range is highlighted in the worksheet.

With Properties Follow set to true (see instructions below), adjust the data so that ‘beta’ becomes the first series in the chart. The red and white striped formatting and the bold red data labels have followed series ‘beta’ as it moved from second to first position in the chart. The formatting has followed the worksheet data.

With Properties Follow set to false, adjust the data so that ‘beta’ becomes the first series in the chart. The red and white striped formatting and bold red data labels are now applied to series ‘gamma’, even though series ‘beta’ has moved from the second to the first series in the chart. The formatting has not followed the worksheet data.

I can think of cases where I’d want the formatting to follow my worksheet data, and other cases, perhaps more, where I’d want the formatting not to follow my worksheet data.

Custom Point Formatting and Data Labels

The next example shows one point in each series in a chart with custom formatting: the third point in each series (category ‘c’) is formatted with the same red and white diagonal stripes, and its data label is bold and red. I’ve also applied the diagonal red and white pattern to the worksheet range that contains the data for these points. The chart is selected so that the plotted data range is highlighted in the worksheet.

With “Properties Follow” set to true (see instructions below), adjust the data so that ‘c’ becomes the second category in the chart. The red and white striped formatting and the bold red data labels have followed category ‘c’ as it moved from third to second position in the chart. The formatting has followed the worksheet data.

With “Properties Follow” set to false, adjust the data so that ‘c’ becomes the second category in the chart. The red and white striped formatting and bold red data labels are now applied to category ‘d’, even though category ‘c’ has moved from the third to the second position in the chart. The formatting has not followed the worksheet data.

Properties Follow Consequences for Small Multiples

The phrase “Small Multiples” refers to a situation where two and often more charts are placed side by side. Each chart shows the same data for a different subgroup of the main data set. Using consistent formatting in all the charts means that once the first chart is deciphered, the subsequent charts are easier to comprehend. In our example we will show yearly Target and Actual sales for two companies.

When building a small multiple of charts, don’t make multiple charts and try to format them separately: you will never make them exactly the same. Instead, make one chart, and make it as close to perfect as possible. Then copy the chart, and change the source data of the copied chart to the next subset of data.

In his example we have ‘Target’ and ‘Actual’ sales data for ‘Company A’. Custom formatting has been applied to the ‘Target’ bars and the ‘Actual’ lines and markers, and data labels with custom font properties (bold and color) have been applied to the last point in each series.

Here we have added data for ‘Company B’.

We’ve copied the ‘Company A’ chart.

With “Properties Follow” set to true, we have changed the ‘Company B’ chart’s source data range to the data for ‘Company B’. Our carefully crafted customizations are lost, since our data no longer points to the ‘Company A’ data that the formatting is linked to. We have lost the data labels, the ‘Target’ fill color, and the ‘Actual’ line color and thickness, though the ‘Actual’ markers retain their custom formatting. The formatting has followed the worksheet data and spoiled our chart.

When Properties Follow Chart Data Point is true, custom formatting is lost when the chart's data changes.

With “Properties Follow” set to false, we have changed the ‘Company B’ chart’s source data range to the data for ‘Company B’. Our carefully crafted customizations are maintained. We still have our data labels, the ‘Target’ fill color, and the ‘Actual’ line and marker formatting. The formatting has not followed the worksheet data and our chart is formatted identically to the first.

When Properties Follow Chart Data Point is false, custom formatting is preserved when the chart's data changes.

Properties Following Data Points: Changing the Setting

The Dialogs

To get to the Properties Follow Chart Data Point setting, click on Excel’s File tab on the ribbon, choose Options, then click on Advanced. Scroll down to Chart, and you’ll see “Properties Follow Chart Data Point for All New Workbooks” and “Properties Follow Chart Data Point for Current Workbook”.

Excel Options - Properties Follow Data Points

There’s a little i-in-a-circle information icon; click on it and you’ll be able to read this description:

Sounds special, custom formatting and labels follow data points, whatever that means.

The default setting for both of these settings is checked (True), so that the properties will follow the data point.

Okay, so uncheck the box. I always just uncheck the box for the current workbook. Because of a fear of missing out on something magical, I’ll admit that I always return here and check it later, and I never check the box for all new workbooks.

Uncheck Properties Follow Chart Data Point

It still isn’t very clear what it means.

It takes a bit of clicking and scrolling to change this setting. If you have Peltier Tech Charts for Excel, there is an easier-to-access checkbox on the Peltier Tech ribbon, in the Chart Format group.

Use Peltier Tech Charts for Excel to change the Properties Follow setting.

The Visual Basic Commands

You may need to change these properties in your VBA code. The syntax to check and uncheck the box for all workbooks is:

    Application.ChartDataPointTrack = True
    Application.ChartDataPointTrack = False

The syntax to check and uncheck the box for the active workbook is:

    ActiveWorkbook.ChartDataPointTrack = True
    ActiveWorkbook.ChartDataPointTrack = False

If you are using a Mac, there is nowhere in the user interface to change this setting. I’ve reported this shortcoming to Microsoft, and they’ve placed it in their queue. Fortunately, these VBA commands work on the Mac as well as in Windows.

Examples from the original article continue below.

Small Multiple Charts

Scenario A – No Problem

Very often, in a dashboard or other report, you need to make a set of charts which are all formatted the same but show different data. The steps to accomplish this easily are straightforward: (1) arrange your data, (2) create the first chart, and format it perfectly, (3) make copies of the perfect chart, and (4) change the data in the copied charts as appropriate.

(1) Arrange your data: Below is a simple data set, with four columns of data, one for each chart in the report.

(2) Create the first chart, and format it perfectly: Here is the first chart, formatted simply as we want all of our charts to look.

Scenario A - 2 - Make the first chart, perfect.

(3) Make copies of the perfect chart: Here are our four charts, showing the original data in each.

Scenario A - 3 - Make copies of the perfect chart

(4) Change the data in the copied charts: Each chart below shows its own division’s data.

Scenario A - 4 - Change the data in the copied charts

No problem! But then, none of the points in any chart had any special formatting applied.

Scenario B – Aaagh!

In the more practical case, one or more points in our charts have custom formatting applied. Let’s apply the same protocol as above and see how it plays out.

(1) Arrange your data: Below is a simple data set, with four columns of data, one for each chart in the report. Also note that we’ve indicated that our corporation got a new CEO in 2015.

(2) Create the first chart, and format it perfectly. We’ve highlighted the arrival of the new CEO by labeling the data point “New CEO” and coloring the bar green instead of the default blue.

Scenario B - 2 - Create the first chart, and format it perfectly

(3) Make copies of the perfect chart: Here are our four charts, showing the original data and custom formatting in each.

Scenario B - 3 - Make copies of the perfect chart

(4) Change the data in the copied charts: Each chart below shows its own division’s data, but the custom formatting is lost.

Scenario B - 4 - Change the data in the copied charts

Where did our formatting go?? Now we’re going to have to apply it chart by chart.

Well, maybe not, if we first change the “Properties Follow Chart Data Point” setting as described above.

Examples of Properties Following Data Points

I’ve prepared a handful of examples to illustrate how this setting works, and why you may not want to uncheck it permanently.

Example A – Properties Following Data Points

Properties Follow Chart Data Points

This simple example has one series of data. The first view below shows the default chart, and I’ve highlighted two cells, with gold and green fill colors.

The second view shows how I’ve applied custom formatting to two bars, gold and green filled bars plus labels reading “Gold” and “Green”, corresponding to the cells with those fill colors. I’ve also highlighted the chart data range in the worksheet.

In the third view, I’ve changed the chart’s data range, shifting values and categories one row down (note the highlighting in the worksheet). Since the Properties Follow Points setting is true, the green and gold bars and labels have moved in the chart from the second and fourth bars to the first and third bars, so the same cells’ values are still highlighted.

With the Properties Follow Points setting checked (True), the custom formatting (bar fill colors and labels) have followed the points as the data range changed.

This is useful if you apply highlighting or labels to particular points in the data range (linking those points to their corresponding cells), for example, to indicate certain categories or events within the data.

Properties Do Not Follow Chart Data Points

This is the same example. The first view below shows the default chart, and I’ve highlighted two cells, with gold and green fill colors.

The second view shows identical custom formatting, gold and green filled bars plus “Gold” and “Green” data labels, corresponding to the cells with those colors. I’ve highlighted the chart data range in the worksheet.

In the third view, I’ve changed the chart’s data range, shifting values and categories one row down (note the highlighting in the worksheet). Since the Properties Follow Points setting is false, the green and gold bars and labels have not moved in the chart, but remain on the second and fourth bars.

Example A - Properties Not Following Points

With the Properties Follow Points setting unchecked (False), the custom formatting (bar fill colors and labels) have not followed the points as the data range changed.

Also we’ve learned that “properties” include point formatting and data labels.

This is useful if you apply highlighting or labels to particular points in the chart.

Example B – Properties Following Data Points and Series

Properties Follow Chart Data Points and Series

This example has multiple series of data. The first view below shows the default chart with two series, and I’ve highlighted two cells in each column, with gold and green fill colors.

The second view shows how I’ve applied custom formatting to two bars in each series, gold filled bars plus labels reading “Gold” on the first series, and green filled bars and “Green” labels on the second series. I’ve also highlighted the chart data range in the worksheet.

In the fourth view, I’ve changed the chart’s original data range, shifting values and series names one column right. Since the Properties Follow Points setting is true, the green bars and labels have moved in the chart from the second series to the first, and the gold bars and labels have fallen off the chart.

Example B - Properties Following Points and Series

Now we’re starting to see where all of our custom formatting went in our second scenario at the start of this article. Move the data far enough, so the cells corresponding to the chart’s highlighting move out of range, and all the formatting moved off the chart.

Properties Do Not Follow Chart Data Points and Series

This repeats the same example. The first view below shows the default chart with two series, and the two highlighted cells in each column, with gold and green fill colors.

The second view shows the same custom formatting, two gold filled bars plus labels reading “Gold” on the first series, and green filled bars and “Green” labels on the second series. I’ve also highlighted the chart data range in the worksheet.

In the third view, I’ve changed the chart’s data range, shifting values and categories one row down (note the highlighting in the worksheet). Since the Properties Follow Points setting is false, the green and gold bars and labels have not moved in the chart, staying with the second and fourth bars in each series.

In the fourth view, I’ve changed the chart’s original data range, shifting values and series names one column right. Since the Properties Follow Points setting is false, the custom formats have not moved in the chart, with the gold bars and labels on the first series and the green on the second.

Example B - Properties Not Following Points and Series

Now we see our answer. If we want to keep custom formatting in our chart when we assign a different data range to it, make sure the Properties Follow Points setting is unchecked.

Example C – Properties Following Custom Data Labels (or not)

This example takes a closer look at data labels, including two ways to apply labels from worksheet cells. It also shows what I think is a bug in the Excel’s implementation of the property.

The data is simple, with categories (X values), Y values, and labels in adjacent columns. I’ve made three charts, each with a green-highlighted second bar and with data labels. The labels in the first chart (“First”, “Second”, etc.) were typed manually. Data labels in the second chart were applied using formulas, for example, =Sheet1!$C$1 for “first”, etc. Labels in the third chart were applied using the Values from Cells setting, and selecting the range of labels.

Properties Follow Chart Data Points

Here I’ve moved the data range down one row. The green formatting has followed the data from the second to the first bars in each chart. The data labels have also moved down one bar. There was no fifth bar with a label to move to the fourth bar, so the fourth label reads the default Y value, or 3.

The exception to this data label movement is the labels that use values from cells. I would expect these to move with the data even more reliably, but they don’t. This is the bug I think I’ve identified in Excel.

Properties Do Not Follow Chart Data Points

Here I’ve moved the data range down one row. As expected, neither the green formatting nor the label text has followed the data; instead, they remain anchored to the bars in the chart.

Example C - Labels Don't Follow Data Points

Example D – Properties Following Chart Data Range

Have we finished flogging this dead horse? No? Okay, I have one more example. This is like our initial scenarios. I have two different ranges I want to plot in identically formatted charts. Below are the two ranges and the original default chart.

Here the chart series have had custom formatting applied, and a couple of arbitrary labels have been applied.

Now a copy of the customized chart has been made and aligned under the second data range, though the chart still points to the original range.

Properties Follow Chart Data Range

The copied chart’s data has been reassigned to the new data range. And with Properties set to Follow Data Points, we’ve lost our custom formatting.

Properties Do Not Follow Chart Data Range

The copied chart’s data has been reassigned to the new data range. And with Properties set NOT to Follow Data Points, we retain our custom formatting. These particular data labels are now incorrect, but they were not lost altogether.

Scenario B – Fixed

Since we want to retain our custom formatting as we select different data for our charts, let’s set Properties Follow Chart Data Points to False, and repeat the second scenario from what now seems so long ago.

(1) Arrange your data: Below is a simple data set, with four columns of data, one for each chart in the report. Also note that we’ve indicated that our corporation got a new CEO in 2015.

(2) Create the first chart, and format it perfectly. We’ve highlighted the arrival of the new CEO by labeling the data point “New CEO” and coloring the bar green instead of the default blue.

(3) Make copies of the perfect chart: Here are our four charts, showing the original data and custom formatting in each.

(4) Change the data in the copied charts: Each chart below shows its own division’s data, and the custom formatting remains in the charts.

Posted: Tuesday, October 9th, 2018 under Formatting.
Tags: Formatting.
Comments: 9

Interactive Formatting of a Chart Series with a Cell’s Color

Tuesday, September 5, 2017 by Jon Peltier 7 Comments

Tuesday, September 5, 2017 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A user of my Peltier Tech Charts for Excel 3.0 asked recently whether this program could perform interactive formatting of all series 2 in a set of charts to the same color.

Well, my program does many things, but it can’t do this. Such a highly specific feature is hard to support in a general purpose program. Next, someone will want to change all series 1 or series 3 or series named “Values”, or they’ll want a different color. It’s difficult to program for all of the various possibilities. (We can try, as I’ll show later.)

But if you need something particular like this, it’s not hard to spin your own VBA code.

Simple Code for Interactive Formatting

For my user’s request, it was as simple as filling a cell (say, H5) with a certain color, then changing the lines for each “Series2” in his charts to this color. The code looks like this:

Sub ColorSeries2LikeCellH5()
  Dim iColor As Long
  Dim shp As Shape
  Dim chob As ChartObject

  ' get the color
  iColor = ActiveSheet.Range("H5").Interior.Color

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    ActiveChart.SeriesCollection(2).Format.Line.ForeColor.RGB = iColor
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        shp.Chart.SeriesCollection(2).Format.Line.ForeColor.RGB = iColor
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      chob.Chart.SeriesCollection(2).Format.Line.ForeColor.RGB = iColor
    Next
  End If
End Sub

Which Chart to Format?

Let’s look a little closer at one piece of this code, which determines whether to work on one chart (the active chart), multiple selected charts (selected using Ctrl+Click or Shift+Click), or all charts on the active sheet (if no chart was selected). I’ve color-coded the explanations and the code sections.

If one chart is selected, then there is an active chart. The strange line of code “If Not ActiveChart Is Nothing Then” really means “If ActiveChart Is Something Then“, so we should process the active chart.

If multiple charts are selected, the selection consists of a collection called “DrawingObjects“, so we should process each shape in this collection (which is also a “ShapeRange“), provided that shape contains a chart.

Finally, if neither of these conditions are met (that is, no chart is selected), we’ll process each chart object on the active sheet. If there are no chart objects, the code will just finish up.

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    ' --> Process active chart
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        ' --> Process each selected shape that contains a chart
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      ' --> Process each chart object on the active sheet
    Next
  End If

How do I know all of this stuff? Well, I’ve been around for a long time, and I’ve swiped code from a lot of experts.

The code above is pretty good, but it’s not very flexible. If you need to get the color from a different cell, you need to edit the command that references cell H5. You need similar changes if you need to format a different series. This is fine if it’s your own code, but not if the user is your colleague who isn’t VBA-savvy.

Better, Flexible Interactive Formatting Code

So let’s add a couple mini-dialogs to ask the user what we should do. This makes it truly interactive formatting.

Which Cell Has the Desired Color?

First we’ll use Application.InputBox to ask the user which cell is filled with the color we want to use.

  On Error Resume Next
  Set rng = Application.InputBox("Select a cell filled with the desired line color", _
      "Select a Colored Cell", , , , , , 8)
  If rng Is Nothing Then GoTo ExitSub
  On Error GoTo 0
  iColor = rng.Interior.Color

This pops up the following dialog:

InputBox: Select Colored Cell

The 8 as the last argument for InputBox tells VBA to expect the user to supply a range.

We use “On Error Resume Next” because if the user cancels the InputBox, “Set rng =” will return an error. Bypassing the error, if the user cancels, rng will not be defined, so we will branch to ExitSub, a label at the end of the procedure.

If the user selects a cell with not fill color applied, the color white will be applied to the applicable series. This probably isn’t what you want, but at least the code doesn’t produce any ugly run-time errors.

Which Series Should We Change?

Next, we’ll use Application.InputBox to ask the user which series to change.

  vSrs = Application.InputBox("Enter the series number or name to be changed", _
      "Identify Series", , , , , , 3)
  If TypeName(vSrs) = "Boolean" Then GoTo ExitSub

This produces another dialog:

InputBox: Identify a Series

Using 1 as the last argument for InputBox tells VBA to expect a number, while using 2 tells VBA it’s text; we’ve used 3, which means number or text (3 = 1 + 2).

If the user cancels, what is returned is the Boolean value False (not the text value “False”). So we test for a Boolean, and if we get one, we branch to the ExitSub label as above.

If the number or name entered by the user does not correspond to an existing series in the chart, an ugly run-time error occurs. It’s a little more complicated to bypass the error, but your users deserve the best.

The error is handled by modifying a line of code like this:

ActiveChart.SeriesCollection([series number or name]).Format.Line.ForeColor.RGB = iColor

First, at the top of the procedure, declare a new object variable to represent the series being sought

Dim oSrs As Series

Then you need to prevent Excel from halting at the error when it finds no matching series, and turn error behavior back on, assign the object variable to the desired series, and if this assignment works (remember, “If Not oSrs Is Nothing” means “If oSrs is Something“), format the series:

  On Error Resume Next
  Set oSrs = ActiveChart.SeriesCollection(vSrs)
  On Error GoTo 0
  If Not oSrs Is Nothing Then
    oSrs.Format.Line.ForeColor.RGB = iColor
  End If

The Improved Routine

So here’s the new and improved routine:

Sub ColorSeriesLikeCell()
  Dim rng As Range
  Dim vSrs As Variant
  Dim iColor As Long
  Dim shp As Shape
  Dim chob As ChartObject
  Dim oSrs As Series
  ' get cell filled with desired color
  On Error Resume Next
  Set rng = Application.InputBox("Select a cell filled with the desired line color", _
      "Select a Colored Cell", , , , , , 8)
  If rng Is Nothing Then GoTo ExitSub
  On Error GoTo 0
  iColor = rng.Interior.Color

  ' identify the series
  vSrs = Application.InputBox("Enter the series number or name to be changed", _
      "Identify Series", , , , , , 3)
  If TypeName(vSrs) = "Boolean" Then GoTo ExitSub

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    On Error Resume Next
    Set oSrs = ActiveChart.SeriesCollection(vSrs)
    On Error GoTo 0
    If Not oSrs Is Nothing Then
      oSrs.Format.Line.ForeColor.RGB = iColor
    End If
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        Set oSrs = Nothing
        On Error Resume Next
        Set oSrs = shp.Chart.SeriesCollection(vSrs)
        On Error GoTo 0
        If Not oSrs Is Nothing Then
          oSrs.Format.Line.ForeColor.RGB = iColor
        End If
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      Set oSrs = Nothing
      On Error Resume Next
      Set oSrs = chob.Chart.SeriesCollection(vSrs)
      On Error GoTo 0
      If Not oSrs Is Nothing Then
        oSrs.Format.Line.ForeColor.RGB = iColor
      End If
    Next
  End If
  
ExitSub:
End Sub

Well, that’s pretty nifty. But what if I have something besides a line chart?

Best, Versatile Interactive Formatting Code

Now we’ll add the capability to check each series chart type, and use the appropriate formatting syntax to change the line color, the marker color, or the fill color. To keep from repeating a lot of this code for checking series chart type, I’ll introduce a function to call to change the series formatting. Using a function also means that if I need to change or enhance the feature, I only need to change it one time in the function, and not everywhere I use the feature.

What’s the Chart Type?

I’ll pass the series and the color into the function, and it will determine based on the series chart type. The function will have this form:

Function RecolorSeries(srs As Series, iClr As Long) As Boolean
  ' assume True unless otherwise
  RecolorSeries = True
  
  Select Case srs.ChartType
    Case [Line] and [Scatter with Line Only]
      ' --> change line color
      
    Case [Line with Markers] and [Scatter with Lines and Markers]
      ' --> change line color
      ' --> change marker color (foreground and maybe background)
      
    Case [Scatter with Markers Only]
      ' --> change marker color (foreground and maybe background)
      
    Case [Bar], [Column], and [Area]
      ' --> change fill color
      
    Case Else
      ' did not reformat a series
      RecolorSeries = False
  End Select
End Function

We can call it like this (which is how I’ll call it)

  RecolorSeries [series], [color]

and it will run like a sub, or like

  bDidItWork = RecolorSeries([series], [color])

and it will still run like a sub, but then return the value True to bDidItWork if it formatted a series and False if it did not. You could then notify the user if one or more series were not changed because of their chart type.

Should We Fill the Marker?

We want to change the MarkerForegroundColor of any markers, but the MarkerBackgroundColor only if it is reasonable to fill the marker. For the Plus, X, and Star markers, filling the marker results in a big ugly square marker where the Plus, X, or Star foreground is not distinguished from the background. We could probably get more creative based on whether the existing background color matches the foreground, but this is just a blog post, eh?

So anyway, after we do the MarkerForegroundColor, we test for the marker style:

      Select Case srs.MarkerStyle
        Case xlMarkerStylePlus, xlMarkerStyleStar, xlMarkerStyleX
          ' fill should not be changed
        Case Else
          ' --> change the fill
      End Select

The Reformatting Function

All fixed up the function looks like this:

Function RecolorSeries(srs As Series, iClr As Long) As Boolean
  ' assume True unless otherwise
  RecolorSeries = True
  
  Select Case srs.ChartType
    Case xlLine, xlLineStacked, xlLineStacked100, _
        xlXYScatterLinesNoMarkers, xlXYScatterSmoothNoMarkers
      ' change line color
      srs.Format.Line.ForeColor.RGB = iClr
      
    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100, _
        xlXYScatterLines, xlXYScatterSmooth
      ' change line color and marker color
      srs.Format.Line.ForeColor.RGB = iClr
      srs.MarkerForegroundColor = iClr
      Select Case srs.MarkerStyle
        Case xlMarkerStylePlus, xlMarkerStyleStar, xlMarkerStyleX
          ' fill should not be changed
        Case Else
          srs.MarkerBackgroundColor = iClr
      End Select
      
    Case xlXYScatter
      ' change marker color
      srs.MarkerForegroundColor = iClr
      Select Case srs.MarkerStyle
        Case xlMarkerStylePlus, xlMarkerStyleStar, xlMarkerStyleX
          ' fill should not be changed
        Case Else
          srs.MarkerBackgroundColor = iClr
      End Select
      
    Case xlBarClustered, xlBarStacked, xlBarStacked100, _
        xlColumnClustered, xlColumnStacked, xlColumnStacked100, _
        xlArea, xlAreaStacked, xlAreaStacked100
      ' change fill color
      srs.Format.Fill.ForeColor.RGB = iClr
      
    Case Else
      ' did not reformat a series
      RecolorSeries = False
  End Select
End Function

The Finished Main Routine

The main routine now looks like this, and it works in conjunction with the function above:

Sub RecolorSeriesLikeCell()
  Dim rng As Range
  Dim vSrs As Variant
  Dim iColor As Long
  Dim shp As Shape
  Dim chob As ChartObject
  Dim oSrs As Series

  ' get cell filled with desired color
  On Error Resume Next
  Set rng = Application.InputBox("Select a cell filled with the desired color", _
      "Select a Colored Cell", , , , , , 8)
  If rng Is Nothing Then GoTo ExitSub
  On Error GoTo 0
  iColor = rng.Interior.Color

  ' identify the series
  vSrs = Application.InputBox("Enter the series number or name to be changed", _
      "Identify Series", , , , , , 3)
  If TypeName(vSrs) = "Boolean" Then GoTo ExitSub

  If Not ActiveChart Is Nothing Then
    ' active chart (one chart selected)
    On Error Resume Next
    Set oSrs = ActiveChart.SeriesCollection(vSrs)
    On Error GoTo 0
    If Not oSrs Is Nothing Then
      RecolorSeries oSrs, iColor
    End If
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    ' multiple charts selected
    For Each shp In Selection.ShapeRange
      If shp.HasChart Then
        Set oSrs = Nothing
        On Error Resume Next
        Set oSrs = shp.Chart.SeriesCollection(vSrs)
        On Error GoTo 0
        If Not oSrs Is Nothing Then
          RecolorSeries oSrs, iColor
        End If
      End If
    Next
  Else
    ' no chart selected so do all charts
    For Each chob In ActiveSheet.ChartObjects
      Set oSrs = Nothing
      On Error Resume Next
      Set oSrs = chob.Chart.SeriesCollection(vSrs)
      On Error GoTo 0
      If Not oSrs Is Nothing Then
        RecolorSeries oSrs, iColor
      End If
    Next
  End If
  
ExitSub:
End Sub

Posted: Tuesday, September 5th, 2017 under VBA.
Tags: Colors, Formatting, Interactive Charts, VBA.
Comments: 7

Microsoft MVP Logo

Plot Values Along the Axis of an Excel Chart

Thursday, August 13, 2015 by Jon Peltier 6 Comments

A blog reader emailed me with a question:

I want to visualize a series of prices on single axis:

Data to plot along an axis

I want to portray these in correct proportion, horizontally along a single line – much like points on a thermometer.

These prices are going to be the X values. You need some kind of dummy Y values, so Excel knows where to put the points.

In the data range shown below, I’ve put a set of dummy values in the row below the prices, using 0.5 as a dummy value. I picked 0.5 to float the points in a line above the horizontal axis; if you do in fact want the points right on the axis, you could use 0.0 for the dummy Y values.

I also changed the product numbers to alphabetic labels for this illustration. Short names will work best as data labels in the chart.

I made a chart using the prices and the dummy Y values (the shaded cells in the sheet). That’s the big chart below the data.

Expanded data and preliminary chart

First I shrunk the chart and the chart title, and changed the title to something more descriptive.

I removed the gridlines, and set the Y axes to a min of 0.0 and a max of 0.7, which made the labels fit better.

The Y axis scale is something that can be easily adjusted as needed. In fact, I didn’t really set it here, I set it later, after I saw how my chart looked with the labels. If you plot the points along (not above) the X axis, you may want to pick a Y axis minimum which is the negative of the maximum.

Evolution of the chart - step 2

Next I hid the vertical axis by using no line color and no labels.

I also set the X axis scale to a min of 1.5, to spread out the points a bit.

Evolution of the chart - step 3

Next I set the plotted points so the format varied by point; this setting is found where you set the fill color of the markers.

I also added data labels; by default Excel uses the values in the labels, and placed them above the points.

Evolution of the chart - step 4

Finally I set up the custom labels I wanted. I used the X values instead of the default Y values and also used the Value from Cells option to get the product names into the labels, with a new line separating the cell values and the X values. These settings are shown in the Format Labels task pane shown below. Unfortunately if you do not have Excel 2013, you don’t have the Values from Cells option, but you have other ways to Apply Custom Data Labels in Excel Charts.

Evolution of the chart - step 2

I also changed the font color of each label to match the corresponding point, and staggered labels where they were too close by placing a couple below the points.

Finished chart

Posted: Thursday, August 13th, 2015 under Chart Types.
Tags: Axis Scales, Data Labels, Formatting.
Comments: 6

Microsoft MVP Logo

Colors Aren’t Needed to Make Data Easier to Read

Wednesday, April 30, 2014 by Jon Peltier 10 Comments

In The Right Colors Make Data Easier To Read (Harvard Business Review), Sharon Lin and Jeffrey Heer report that using “semantically resonant colors” make charts easier to read. They offer this before and after figure to support their claim, where default colors are shown at left, and semantically resonant colors shown at right.

illustration of semantically resonant colors

See what they did there? Apples are green (except of course when they’re red), bananas are yellow (except for the green and brown ones in my kitchen), blueberries are blue, etc. Joking aside, this makes intuitive sense at first.

However, I didn’t think the changed colors made the second chart appreciably easier to read. Part of the problem is that the identification of the colored bars requires repeated eye jumps from the chart to the legend, and the seven colors in this chart use up the three to five to seven chunks of short term memory at my disposal.

I usually advise people to replace the chart’s legend with labels closer to the data points. In this case, category labels right on the axis work well. I’ve also rotated the chart to make use of easy to read horizontal labels. The numerical scale also has a greater resolution, since it uses the width of the chart rather than the shorter height. This is another advantage of a rotated chart, given a wider than tall aspect ratio.

No need for colors if proper labeling is used

Note that no colors are required to give the chart meaning.

Unless you need to look up a particular value by name, it is usually beneficial to sort the data by a factor other than alphabetically by name. In this case, I’ve applied a sort by decreasing value. With only seven points, looking up a particular fruit isn’t much of a burden despite not being sorted alphabetically.

Sorting data helps visualize it

Colors or shades of color can be useful to highlight particular values. If I were writing a brochure about the growing tangerine market, I could use a darker shade of my fill color to easily show where tangerines fit among their peers.

Use colors to highlight not identify

I realize the authors were using the simple bar chart to illustrate their point about semantically resonant colors. However, more basic best practices for simple bar charts tell us to ignore these colors.

If I wanted to differentiate different data sets in a line chart, of course, this use of colors would be helpful.

Colors help distinguish data in more complicated displays

Note the use of best practices here. There is no legend, instead, data labels have been applied directly to the data. The labels have also been colored to match the data.

Posted: Wednesday, April 30th, 2014 under Charting Principles.
Tags: Colors, Formatting.
Comments: 10

Microsoft MVP Logo

Pivot Chart Formatting Changes When Filtered

Monday, April 7, 2014 by Jon Peltier 76 Comments

This is a guest post written by my colleague Jon Acampora.

The Problem

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter. This can be very frustrating and require you to apply the custom formatting to each possible view or slice of the data.

The screencast below shows this problem. I customized the colors of the lines to green and grey for the Year 2011, but the line colors change back to the default colors when I filter the data for 2012. This happens when the data is filtered by a slicer or manually filtered it in the PivotTable.

Pivot Chart Formatting Changes by Filter Slicer Excel

This post will explain why this happens and present an alternative solution that will save you time when creating PivotCharts with customized formatting. VBA or macros will NOT be needed for this to work, so it should be easy for anyone to implement.

Why Does This Happen?

It’s all about the cache…

Any changes to the formatting of a data series in a PivotChart are stored in a cache inside the Excel file. In the example below I made a few changes to the formatting of the lines in the chart from their default settings. The colors of the lines and the shapes of the markers were changed when the PivotTable was filtered for the Year 2011. As you can see in the screencast, when the PivotTable is filtered for the Year 2012 the line colors and markers change back to the default settings.

Excel Pivot Chart Source Range Unchanged on Filter

Even though the source range of the PivotChart remains the same, the formatting of the chart changes. This is an odd behavior until you understand that the formatting properties are saved and remembered by Excel.

Excel actually stores this data in a cache with all the other chart properties. This means that it remembers the exact formatting I applied for the Year 2011 only, and that is why the colors and formatting change back to my customization when the PivotTable is filtered for 2011.

To see this you have to dive under the hood of the Excel file. I included the XML code for the PivotChart in the sample file (download below). If you search through the code you can see a reference to “2011 – Coffee” and “2011 – Tea”. Below each of those lines are rows of code that contain the custom formatting settings I applied.

Excel PivotChart Cache XML

Since there is no reference to 2012 or 2013 in the chart’s cache, the default formatting for the chart is applied. I think it’s cool to be able to see the inner workings of the file and try to understand what is happening with the application. You can see the components that make up any Excel 2007+ file by changing the file extension to “.zip”, then opening the folder.

Why does this matter?

This behavior is annoying for the most part. If you click on a slicer or filter, you usually want to see the same custom formatting you applied for the other slices.

But understanding the behavior allows you to either use it to your advantage, or avoid it at design time. I will explain a solution below that allows you to avoid the behavior, but still use the PivotTable filtering and slicers.

It should also be noted that this behavior does not always happen. It depends on the setup of the PivotTable and the order of the row and column fields. If you have a very simple pivot table that only has one field in the column area and one field in the row area, then you might not experience this change in formatting at all. I am still trying to determine the exact parameters of the PivotTable that cause this to happen. Please let us know if you have any insights on this.

Pros and Cons

The advantage of this unique formatting behavior is that it allows you to apply customized formatting for each slice of your data. This could be useful if you want a series to stand out from the rest when the user is clicking through the slices. You could use it as an attention grabber to highlight some important trends.

The disadvantage is that a lot of the time you want all slices to display the same custom formatting. If you have a lot of slices then it becomes very tedious work to go through each slice and reapply the customized formatting. And it will also require maintenance in future periods. For example, when I get next year’s data for 2014 I will have to create custom formatting for that slice of the data.

Simple Workarounds

One way to prevent the formatting from changing is to create a regular chart that references the PivotTable data. With either of the following solutions we want to avoid the PivotChart all together, and instead create a regular chart. Again, the regular charts do NOT suffer from the formatting issues when filters are applied.

Solution #1

This can be accomplished by applying the technique in Jon’s article Making Regular Charts from PivotTables. With that technique you basically create a regular chart and then carefully add the data series to the chart by referencing areas within the PivotTable. You just have to be careful with the areas you select or reference because Excel has a tendency to want to convert the regular chart to a PivotChart.

Solution #2

The other option is to create a new area on the worksheet that contains a replica of the PivotTable. This area just contains formulas that reference the PivotTable. You can either use the direct cell references like (=C9) or the GETPIVOTDATA() function to point to the PivotTable. You might want to use the GETPIVOTDATA function if you only want to display a subset of your PivotTable data in the chart.

The advantage of this technique is that once you have the formula based PivotTable setup, you can reference the entire area of the source data range to quickly create a chart that includes all your series. It might save you time over having to add the series individually as you do in solution #1.

The sample file (download below) contains an example of the technique that I will explain here.

The first step is to recreate the PivotTable data by creating formulas that reference the pivot. This can be done in cell adjacent to your PivotTable or on a separate worksheet. Just remember to leave enough blank rows/columns between your PivotTable and formula based table in case your PivotTable expands when filters are applied/removed.

Excel PivotChart Formulas Linked to PivotTable

Step two is to create a regular chart using the new formula driven table as the source of the chart. When the PivotTable is filtered or sliced, the formulas will automatically be updated and display the new numbers from the PivotTable. The chart will also be updated and display the new data.

Excel Create Regular Chart to PivotTable Linked Formulas

That’s it! You should now have a chart that retains it’s formatting when a slicer or filter is applied to a PivotTable. An example of this behavior can be seen in the screencast below.

PivotChart vs Regular Chart Formatting Changes Slicer Excel

This solution works well for simple cases where the size of the data area stays the same every time the chart is updated. If the number of rows/columns in your PivotTable is increasing/decreasing every time you update the data, then this solution might take more maintenance or tweaking to work for you. But the general principle will help get you started and thinking about more advanced solutions if needed.

Jon has a great article on using VBA to create dynamic charts that explains a more advance approach to this issue.

Conclusion

Adding slicers to your PivotCharts and PivotTables is a great way to make your presentation interactive. PivotCharts allow you to link your chart to a data source so they can be refreshed dynamically with little maintenance. However, PivotCharts display some odd behavior when filtering charts with custom formatting. Understanding this behavior and planning for it at design time will save you time and frustration.

The example file can be downloaded below. Please leave a comment with any questions or suggestions. Thanks!

Download

Download PivotChart Formatting Changes On Filter Slicer.xlsx PivotChart Formatting Changes On Filter Slicer.xlsx (34.7 KiB)

About the Author

Jon Acampora writes the blog at ExcelCampus.com where he is focused on helping users improve their Excel and VBA skills. His contributions include in-depth articles, video tutorials, and free add-ins to help save time and improve productivity. Jon is a newly-minted Microsoft Excel Most Valuable Professional (MVP).

Posted: Monday, April 7th, 2014 under Pivot Tables.
Tags: Formatting, Pivot Charts, Pivot Tables, VBA.
Comments: 76

Microsoft MVP Logo