Formatting

Invert if Negative Formatting in Excel Charts

Thursday, December 5, 2019 by Jon Peltier 9 Comments

Thursday, December 5, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Often the positive and negative values in a chart are formatted differently to make it visually easier to distinguish these values. In Excel column and bar charts, this can be done using a feature called Invert if Negative. This is commonly done in Waterfall Charts (using a different technique).

Invert if Negative easily applies different colors for positive and negative data in bar or column charts.

Invert if Negative is often applied incorrectly, but this article will show you the right way to use it.

Here is a simple data set with positive and negative values. All of the bars have the same color.

Side Tip #1: Move Axis Labels

The first thing you might notice about the previous chart is that some of the category (X axis) labels are hard to read because they overlap with the bars.

A simple axis setting moves the labels to the bottom of the chart, to eliminate this overlap. Select the axis and press Ctrl+1 to open the Format Axis task pane, click Labels, and in the Label Position dropdown, change from the default Next to Axis to Low.

Label position low to avoid overlapping with data

Side Tip #2: Ctrl+1

People love learning new Excel shortcuts. When they ask me for my favorite shortcut, I tell them Ctrl+1. Whatever you select any object in Excel (a cell or range, a shape, or a chart element), pressing Ctrl+1 opens the Format dialog or task pane for that object. I use Ctrl+1 a lot, and when I use it in PowerPoint or Word I get mad that it doesn’t help me format what I’ve selected there.

Actually, I’m lying about my favorite Excel shortcut. My real favorite Excel shortcut, the shortcut I use more than any other, is Ctrl+Z, which undoes my last action (i.e., my last mistake). Ctrl+Z is great, because it also works in PowerPoint, Word, and many other programs.

Side Tip #3: Don’t Move the Axis

In some kinds of charts, you can actually move the horizontal axis away from its default position at Y=0 to the bottom of the chart. This is done by assigning a Y axis value to where the horizontal axis crosses.

Axis crosses setting to move the horizontal axis

This might be fine in a line chart, to move the axis labels out of the way of the data. In a bar or column chart, this isn’t a good idea. Since Excel draws bars and columns starting from the axis, you get excessively long positive bars for positive values, and short positive bars for negative values.

Alternative Method: Invert Formats Using Different Series

You can use the approach in Conditional Formatting of Excel Charts to format the bars differently. This requires extra columns in the data range for each different format and formulas to place applicable values in each column.

Invert series colors using conditional chart formatting

This is a fine technique, and it’s suitable for more than just positive vs. negative formatting, any condition that you’re smart enough to write a formula for. But it requires extra columns in the data range.

Invert if Negative: Not the Right Way

The Invert if Negative setting is easy to find. Select the series and press Ctrl+1 to open the Format Data Series task pane. The Invert if Negative checkbox is near the bottom of the list of fill options.

If you just check the box while the Automatic fill option is selected, you can only format the color of the positive bars with the paint can icon, while the negative bars are white with a black border.

People think Invert if Negative doesn’t work right when they use it this way, but really, the right technique is easy.

Invert if Negative: The Right Way

Select the Solid Fill option when you check the Invert if Negative box, the task pane has two paint can icons, one for positive bars, and one for negative bars.

You can go back anytime and select Solid Fill later to get the double paint cans.

Invert if Negative for Old Excel Versions

Old versions of Excel had Invert if Negative, with a default negative fill color of white. In Excel 2003 and earlier, you had to apply a pattern temporarily to select a specific color for negative bars, then unapply the pattern and the color would stick. In Excel 2007, you had to apply a gradient fill with an insane gradient setting. But if you’re using Excel 2010 or later (and you should be), the Solid Fill option provides two paint cans for easy selection of positive and negative colors.

Invert if Negative for Pivot Tables

In a Pivot Chart, Invert if Negative may lose the custom negative formatting you’ve created, after you save and reopen the workbook.

The old Excel 2007 workaround that hacks the gradient fill would be good here, since it gives the appearance of Invert if Negative, though it’s a bit tedious to apply. However, that hack was based on Excel 2007’s faulty color management, and when that was fixed in 2010 or 2013, the gradient hack no longer worked. So you’ll have to rely on the even older Excel 2003 workaround that uses pattern fills.

Select the series in your chart and press Ctrl+1 to open the Format Series task pane. Select Pattern Fill and Invert if Negative. Select the positive color (blue below) using the Foreground paint can, and the negative color (orange) using the Background paint can. And choose the Dotted 90% pattern

It’s not a smooth looking as the regular chart without patterns, but at least it captures about 90% of the experience, and the Pivot Chart’s pattern fill seems to keep its inverted formatting robustly.

Pattern-Filled Inverted if Negative Pivot Table

Invert if Negative for Tables

If you create a chart from a Table, and then filter the Table (and therefore to filter bars in the chart), then Invert if Negative may also lose its negative formatting.

According to limited testing I’ve carried out, if any (but apparently not all) negative bars were filtered out of a chart, they may lose their negative formatting, and revert to the default white bar with a black outline.

Ah-HA! you say, we’ll just apply the patterned fill approach that works so well for Pivot Charts. And that will seem okay, until you filter the table, save the workbook, reopen the workbook, and refilter the table. And the formerly hidden negative bars have white fill again. Sometimes playing with the slicer may restore the inverted fill color, but not reliably.

The remedy is to unfilter the table, so all bars are visible (even if their reversed formatting isn’t totally correct), and then save the workbook. When you reopen the workbook, the negative bars are inverted correctly, and the formatting survives subsequent filtering, until the next save.

Peltier Tech Articles About Conditional Formatting of Excel Charts

Invert if Negative is one specific form of conditional formatting in Excel charts, unique in that it is built into Excel. Here are other approaches which are not built into Excel but which can be implemented with formulas and/or VBA.

Posted: Thursday, December 5th, 2019 under Formatting.
Tags: invert if negative.
Comments: 9

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

Chart with Actual Solid Lines and Projected Dashed Lines

Wednesday, July 11, 2018 by Jon Peltier 9 Comments

Wednesday, July 11, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Show Projected Data with Dashed Lines

This tutorial shows how to create a chart with historical (actual) data and future (projected) data, with the historical data formatted with a solid line and the future data with dashed lines.

It’s not hard to do, you just need to know some data layout and chart formatting tricks.

Background

I came across this tweet, where Max wanted help with an Excel chart:

I have a population data for 2001 to 2018 and projected population data for 2025, 2030 and 2040 only. What I would like to do is a full line from 2001 to 2018 and a dotted line from 2018 to 2025 to 2030 to 2040. Is that possible ?

— Max_Habs (@Max_Habs) July 10, 2018

Basically he wants to plot all of his data, with dashed lines for the projected data.

First Attempt: Tedious Reformatting Bit by Bit

I don’t have Max’s data, so I made up my own. In my Excel worksheet, I’ve formatted the predicted values with gray italic text. I made a simple chart of this data, using a chart type of Scatter with Straight Lines.

Then I selected the first line segment for the projected data (select one line segment by selecting the entire line with one click, and selecting the specific line segment with a second click), and I formatted it to have dashed lines. So far, so good, except that legend went a bit funny. When individual points re formatted differently, and there is only one series in the chart, Excel helpfully changes the legend to show each individual point, taking up half the chart.

No big deal, the legend is redundant anyway, so delete it. Then reformat the rest of the projected line segments.

This chart wasn’t too hard to do, but imagine if you had dozens of line segments to reformat? Hard enough to select each itty bitty one, then your brain begins to turn into mush from the repetition.

The Right Way: Separate Series for Actual and Projected

The better way to make the chart is to start with better data. Put the Actual data in one column and the Projected data into the next. Repeat the last Actual value in the Projected column.

Now plot the data. Above I simply jumped ahead to a scatter chart without explanation, but anyone who knows me knows I love to explain stuff.

Here’s what happens when you try to make a line chart, which is the first thought most users have, until they’ve been burned. Excel’s line charts don’t treat the X values as numerical data, but as text labels. So the first part of the chart has data every year, and it looks okay, but then the projected data comes at irregular intervals (7, 5, and 10 years). Treating these years as labels, the widely spaced projections are still plotted one slot apart, so the orange projected line does not extend smoothly from the actual data.

So use Scatter with Straight Lines as your chart type. I know you’d like to use Scatter with Smooth Lines, and for this example it wouldn’t make much difference. But there are reasons to avoid the smoothed lines.

At first, the fact that the projected line is formatted differently (orange) may seem like a drawback to this approach, but it’s actually a benefit. Here all you have to do is select the entire Projected series, and format it once, changing the color to blue and the line style to dashed.

A little formatting and it’s finished. I removed the legend, adjusted the X axis scale, and added data labels on the last visible point of each series.

The Tricks

I used a few simple concepts so my chart is easy to make and easy to maintain. In case you missed them, here they are:

Separate columns for actual and projected data
Repeat last actual value in projected column
Separate chart series for actual and projected lines
Scatter chart type, not line chart

Posted: Wednesday, July 11th, 2018 under Formatting.
Tags: predictions, projections.
Comments: 9

Slim Down Those Fat Legend Keys

Monday, April 30, 2018 by Jon Peltier 9 Comments

Monday, April 30, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

My Big Fat Legend Keys

A client had a combination chart in his report, with some columns and some lines, and was complaining about the legend. It seems that when he added the lines, the column entries in the legend had a wide rectangle showing the series color, rather than a square, so the legend was taking up way too much space. The left hand chart below shows this fat-legend problem, while the right hand chart has no problem because I’ve fixed it.

Fat and Thin Legend Keys

Normally, the fill-type of series (column charts, area charts, bar charts) use a simple square for the legend key, the colored part of the legend entry that helps the user determine which series is which.

Fill-Type Charts with Thin Legend Keys

In contrast, line charts have a wider legend key, which allow for the display of a marker and a line.

Line Charts with Wide Legend Keys

The problematic charts are mixed types. Wider legend keys are needed to show the lines for the line chart types, and to accommodate the added width, the squares for the fill-type charts are widened.

Mixed Charts with Wide Legend Keys

And I thought, wouldn’t we all like to be a little thinner? So I played around a bit, and I’ve found a way to fix this.

First, I noticed that if the line charts had markers but no lines, the legend keys were only as wide as the markers.

Marker Charts with Thin Legend Keys

So I played a bit more, and discovered that I could format the whole line series to have no lines, but then format the individual points to have lines, and the legend would only show the whole series format, not the individual point format.

Fix the Fat Legend Keys Manually

I’ll show you the manual protocol first, then I’ll share some VBA that will remove the drudgery of it all.

We’ll start with a combination chart with columns and one line series (top left).

The first step is to format the line chart series with markers but no lines (top right).

We select the first point, and format it to have a line, which does not appear, because it connects the first point to the point before that, which doesn’t exist (middle left).

We then select the second point, and format it also to have a line, which is now visible (middle right).

We select the third point and repeat (bottom left).

Finally, we repeat this for the rest of the points (bottom right).

Protocol for Line and Marker

That wasn’t so hard, was it?

What if the line chart has no markers? Well, it’s almost the same. Again we start with a combination chart with columns and one line series (top left).

The first step is to format the line chart series, adding markers but removing the lines (top right).

We select the first point, and format it to have a line but no marker. Neither the line nor the marker appear (middle left).

We then select the second point, and format it also to have a line but no marker (middle right).

We select the third point and repeat (bottom left). See where this is going?

Finally, we repeat this for the rest of the points (bottom right).

Protocol for Line Only

Fix the Fat Legend Keys Programmatically

So the protocol isn’t too hard, but you can just tell it’s going to get tedious when there are a lot of charts to process. So I’m here to streamline the process with a little VBA.

The core procedure inputs one line chart series and handles it as above. It first formats the entire series with a marker and no line. If the series originally had no marker, it temporarily uses a square; otherwise it uses whatever marker was present. Then it formats each point in turn to have a line, and if it originally had no marker, it removes the marker from that point.

Sub RemoveLegendLinesFromSeries(srs As Series)
  Dim iPt As Long, nPts As Long
  Dim bNoMarkers As Boolean
  
  nPts = srs.Points.Count
  Select Case srs.ChartType
    Case xlLine, xlLineStacked, xlLineStacked100
      bNoMarkers = True
    Case xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100
      bNoMarkers = False
    Case Else
      GoTo ExitSub
  End Select
  
  If bNoMarkers Then
    srs.MarkerStyle = xlMarkerStyleSquare
  End If
  
  srs.Format.Line.Visible = msoFalse
  
  For iPt = 1 To nPts
    With srs.Points(iPt)
      .Format.Line.Visible = True
      If bNoMarkers Then
        .MarkerStyle = xlMarkerStyleNone
      End If
    End With
  Next
  
ExitSub:

End Sub

The next procedure inputs an entire chart, doing each line chart series and ignoring the others.

Sub RemoveLegendLinesFromChart(cht As Chart)
  Dim srs As Series
  
  For Each srs In cht.SeriesCollection
    Select Case srs.ChartType
      Case xlLine, xlLineStacked, xlLineStacked100, xlLineMarkers, xlLineMarkersStacked, xlLineMarkersStacked100
        RemoveLegendLinesFromSeries srs
      Case Else
        ' nope
    End Select
  Next
End Sub

We have two entry-point procedures: the first handles the active chart.

Sub RemoveLegendLinesFromActiveChart()
  RemoveLegendLinesFromChart ActiveChart
End Sub

The second entry-point procedure figures out what the user has selected, and acts accordingly. If the user has selected a series, a point, a set of data labels, or a single data label, the procedure processes just the applicable series. If the user has selected a chart (i.e., there is an active chart, but none of the previously listed elements are selected), the procedure takes care of the whole chart. If the user has selected multiple charts, then all charts are handled. In fact, since this procedure handles an active chart, the previous procedure isn’t needed after all.

Sub RemoveLegendLinesFromSelection()
  Dim sh As Shape
  
  If Not ActiveChart Is Nothing Then
    Select Case TypeName(Selection)
      Case "Series"
        RemoveLegendLinesFromSeries Selection
      Case "DataLabels", "Point"
        RemoveLegendLinesFromSeries Selection.Parent
      Case "DataLabel"
        RemoveLegendLinesFromSeries Selection.Parent.Parent
      Case Else
        RemoveLegendLinesFromActiveChart
    End Select
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    For Each sh In Selection.ShapeRange
      If sh.HasChart Then
        RemoveLegendLinesFromChart sh.Chart
      End If
    Next
  End If
End Sub

I selected the mixed charts from the screenshot in the first section above, and ran this last procedure, and in an instant, the legend keys were brought down to size.

Mixed Charts with Thin Legend Keys

Posted: Monday, April 30th, 2018 under Formatting.
Tags: Legend, Legend Entry, Legend Key, VBA.
Comments: 9

Microsoft MVP Logo

Connect Two XY Series with Arrows

Tuesday, January 24, 2017 by Jon Peltier 14 Comments

The Issue

If you have XY data from two different times, or under two different sets of conditions, you can connect points of one series (one time or condition) to another (another time or condition), to help show how the data evolves. For example, here are two series of data that I might want to connect together.

Series A and Series B to be connected by line segments

Several years ago I wrote Connect Two XY Series, which showed how to connect the points of one series to the corresponding points of another series. This approach used a third series with data from Series A and Series B, interwoven with blank cells.

Series A and Series B connected by line segments

I followed this up with Connect Two XY Series with Arrows (VBA), which showed how to connect the two series with arrows. In Excel 2003 and earlier, you had to use VBA to draw the arrows, and this article provided the VBA (and it’s reproduced below).

Series A and Series B connected by arrows, using VBA

This works nicely enough, but the arrows are not tied to the points, but only to the original positions of the points within the chart. So if you change the axes in the chart …

Series A and Series B no longer connected by arrows

… or change the size or shape of the chart …

… the arrows no longer match up to the points.

You could always delete the misaligned arrows and rerun the VBA procedure, but what a pain.

I hinted in this second article that you could connect the points with arrows in Excel 2007, without using VBA but instead by formatting the third series that connected the first two series. But I never explained how. This article fills the gap.

Connect Two Series with Arrows, Without VBA

You need to keep the original Series A and B data in place, so you can still easily plot the original points. You need to get data into a new range, interweaving Series A with Series B.

Set Up The Data – Approach 1

To get the data into this alternating arrangement, start by pasting the data from Series A into a new range, then paste the data from Series B below it. Enter a set of index numbers into a column adjacent to this combined data, from 1 to the number of points in Series A, and from 1 to the number of points in Series B (which should be the same number of points). This is the first data block below.

Select the data, and sort by the column of digits you just entered. The data now has alternating Series A and B data (below right).

Select the data and create a chart, or add it as a new series to the chart of Series A and Series B.

Series A and Series B connected with lines, but needing gaps at alternating segments

Hmmm, that’s almost what I want. But there are too many line segments. We need to eliminate half of them, shown as dashed lines below.

Series A and Series B connected with lines, showing where gaps are needed

We could format alternating line segments to be drawn with no line, but that’s mighty tedious after the first or second segment. Speaking from experience, because I had to format the dashed lines in the chart above, segment by segment.

As I’ve written about in Mind the Gap – Charting Empty Cells, the way we get a gap between points in a chart is to have a gap in the data, that is, blank cells between the points.

So let’s alter our protocol above, so that we start with the Series A data, then the Series B data below that, then an equal number of blank rows at the bottom, and include our index column (below left).

Sort by the index column, to get Series A and B data pairs, separated by blanks (below right).

Sorted Data for Series A and B and blanks

Now when we plot this data, we connect appropriate pairs of points, with gaps between points we do not want to connect.

Series A and Series B connected with lines, with gaps

Set Up The Data – Approach 2

Above we produced our data range by copying the data into a new range, and sorting the data appropriately. We could instead have stretched out the data first, then copied and pasted using the Skip Blanks option.

Here’s a copy of the Series A and B data, with two rows inserted between each point.

Series A and B data, expanded with blank rows

Copy the expanded Series A data, select the first cell of a new range, …

… then paste.

Now copy the Series B data, select the cell below the first Series A point, …

… and use Paste Special, and check the Skip Blanks option near the bottom of the dialog. Skip Blanks prevents blanks in the copied range from being pasted over data in the target range, so the Series A data is preserved.

Series B data pasted, skipping blanks to preserve Series A data

Now the data is ready to plot.

Make the Chart

Select the Series A data (shaded blue below), and create an XY Scatter chart. Then copy the Series B data (shaded orange), select the chart, and use Paste Special to add the data as a new series, with series in columns, Y values in the first column, series names in the first row. The resulting chart is shown below right.

Series A and Series B, to be connected with arrows

Now copy the A-B Interwoven data (below left), select the chart, and again use Paste Special to add this data as a new series, with series in columns, Y values in the first column, series names in the first row, to produce the chart shown below right. (You may have to format the new series to show lines and no markers)

Series A and Series B connected with line segments

Now you need to format the line segments of the new series. Select the series and press Ctrl+1 (numeral one) to open the Format Data Series task pane or dialog. Click the End Arrow Type dropdown, and select the desired arrow type. The options include three arrowheads, a circle, a diamond, and a plain line.

Format Data Series Lines - End Arrow Type

Now click the End Arrow Size dropdown, and choose an appropriate size for the arrowhead. There are combinations of three arrowhead lengths and widths.

Format Data Series Lines - End Arrow Size

Now the line segments have been transformed into arrows.

Series A and Series B connected with arrows

Unlike the VBA approach of the old tutorial, the arrows are tied to the points in Series A and Series B. So if you change the axes in the chart …

Series A and Series B still connected by arrows

… or change the size or shape of the chart …

… the arrows still match up to the points.

Connect XY Series.xlsx

The VBA

You don’t need to use VBA to connect points with arrows, but it is a good sample application of VBA to your charts. Just so you don’t have to go back to the earlier post, here is the VBA Procedure that connects the points with line segments with arrowheads.

Sub ConnectTwoXYSeriesWithArrows()
  Dim myCht As Chart
  Dim mySrs1 As Series
  Dim mySrs2 As Series
  Dim nPts As Long, iPts As Long
  Dim myBuilder As FreeformBuilder
  Dim myShape As Shape
  Dim iShp As Long
  Dim Xnode1 As Double, Ynode1 As Double
  Dim Xnode2 As Double, Ynode2 As Double
  Dim Xmin As Double, Xmax As Double
  Dim Ymin As Double, Ymax As Double
  Dim Xleft As Double, Ytop As Double
  Dim Xwidth As Double, Yheight As Double

  ' a chart with at least two series must be selected
  If ActiveChart Is Nothing Then
    GoTo ExitSub
  End If
  If ActiveChart.SeriesCollection.Count < 2 Then
    GoTo ExitSub
  End If

  Set myCht = ActiveChart
  Set mySrs1 = myCht.SeriesCollection(1)
  Set mySrs2 = myCht.SeriesCollection(2)
  nPts = mySrs1.Points.Count

  ' two series must have matching numbers of points
  If mySrs2.Points.Count <> nPts Then
    GoTo ExitSub
  End If

  ' remove any old connecting arrows
  For iShp = myCht.Shapes.Count To 1 Step -1
    If Left(myCht.Shapes(iShp).Name, 12) = "ArrowSegment" Then
      myCht.Shapes(iShp).Delete
    End If
  Next

  Xleft = myCht.PlotArea.InsideLeft
  Xwidth = myCht.PlotArea.InsideWidth
  Ytop = myCht.PlotArea.InsideTop
  Yheight = myCht.PlotArea.InsideHeight
  Xmin = myCht.Axes(xlCategory).MinimumScale
  Xmax = myCht.Axes(xlCategory).MaximumScale
  Ymin = myCht.Axes(xlValue).MinimumScale
  Ymax = myCht.Axes(xlValue).MaximumScale

  For iPts = 1 To nPts
    ' first point
    Xnode1 = Xleft + (mySrs1.XValues(iPts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode1 = Ytop + (Ymax - mySrs1.Values(iPts)) * Yheight / (Ymax - Ymin)

    ' second point
    Xnode2 = Xleft + (mySrs2.XValues(iPts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode2 = Ytop + (Ymax - mySrs2.Values(iPts)) * Yheight / (Ymax - Ymin)

    ' draw connecting line
    Set myShape = myCht.Shapes.AddLine(Xnode1, Ynode1, Xnode2, Ynode2)

    ' name and format shape as arrowhead
    With myShape
      .Name = "ArrowSegment" & CStr(iPts)
      With .Line
        ' USE YOUR FAVORITE FORMATS HERE
        .ForeColor.ObjectThemeColor = msoThemeAccent3
        .ForeColor.Brightness = -0.25
        .Weight = 1.5
        .EndArrowheadLength = msoArrowheadLong
        .EndArrowheadWidth = msoArrowheadWidthMedium
        .EndArrowheadStyle = msoArrowheadTriangle
      End With
    End With

  Next

ExitSub:
 
End Sub

Posted: Tuesday, January 24th, 2017 under Formatting.
Tags: Arrows, Data Techniques.
Comments: 14

Microsoft MVP Logo