VBA Approaches to Plotting Gaps in Excel Charts

In Mind the Gap – Charting Empty Cells, I described in gory detail how Excel’s various chart types treat empty cells, that is, cells which are totally empty. I also described why none of the approaches we mortals have ever tried to produce a gap across a simulated blank cell has ever worked.

Cells which have formulas that return a null string (i.e., “”) are plotted like any text, with a marker at a value of zero. In line and XY Scatter charts, an error value of #N/A almost works, because it suppresses plotting of a point, but does not produce a gap in the line segments connecting points.

The only way to get a gap in the lines of a chart is to have empty cells is to rely on a VBA routine to correct the appearance of the chart, wither by changing the formatting of the points and lines you want to hide, or by clearing the contents of the associated cells. These work well, but must be repeated every time the data changes, and if you’ve cleared any cells, their contents must be recreated in case those points should no longer show gaps.

This post contains two routines to fix the chart’s appearance, one by changing the chart series formatting, the other by changing the data.

The Gap Problem

Below is a simple data range I created to illustrate this problem. The “Broken” column shows a set of random values centered around 3, with either #N/A or “x” inserted into random cells in place of the random values. The “x” is text, so it will be treated the same as “”, but it is used instead so it shows up in the cells.

In the “ChartFix” and “DataFix” I’ve included this same data, increased by 1 so I could plot it with the “Broken” data without obscuring it. I’ll use two XY Scatter charts, one to show how fixing the chart formatting works, the other to show how fixing the data works.

Here are the two charts I promised. The lower (blue) series is the “Broken” data, which will not be changed by these routines. The upper (orange) series is the “ChartFix” series (left) or the “DataFix” series (right).

Both series in both charts show a missing marker at X=5, with a line connecting the points at X=4 and X=6. These missing points correspond to #N/A errors in the corresponding cells. All four series also show a point plotted at X=15, Y=0, corresponding to the “x” value in the corresponding cells.

We want our code to leave no markers and no connecting lines at these points in the orange series, while the blue series remain intact to remind us where changes were made. In general you will probably want to process all series in your charts.

The Code

Both procedures parse the series formula (see detailed documentaiton at the end of this article) to find the ranges containing the X and Y values. If you are not using XY Scatter charts, remove the X value components of the code, because nonnumeric X values are allowed for other chart types.

The first procedure loops through the parsed X and Y values, and where it finds a nonnumeric value, it formats the point to have no marker and for the connecting line segment on either side to show no line. Where it finds a numeric value, it restores marker and line segment formatting, in case the chart already was “fixed” but now the data has changed.

Sub FixLineFormatInChart()
  Dim iPt As Long
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sXVals As String
  Dim sYVals As String
  Dim rXVals As Range
  Dim rYVals As Range
  Dim vXVals As Variant
  Dim vYVals As Variant

  With ActiveChart
    ' just process the orange series
    With .SeriesCollection(2)
      sFmla = .Formula
      vFmla = Split(sFmla, ",")

      sXVals = vFmla(1)
      sYVals = vFmla(2)

      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      vXVals = rXVals.Value
      vYVals = rYVals.Value

      For iPt = 1 To .Points.Count
        If IsNumeric(vXVals(iPt, 1)) And IsNumeric(vYVals(iPt, 1)) Then
          .Points(iPt).MarkerStyle = .MarkerStyle
        Else
          .Points(iPt).MarkerStyle = xlMarkerStyleNone
        End If
      Next

      For iPt = 2 To .Points.Count
        If IsNumeric(vXVals(iPt - 1, 1)) And IsNumeric(vXVals(iPt, 1)) And _
            IsNumeric(vYVals(iPt - 1, 1)) And IsNumeric(vYVals(iPt, 1)) Then
          .Points(iPt).Format.Line.Visible = True
        Else
          .Points(iPt).Format.Line.Visible = False
        End If
      Next
    End With
  End With
End Sub

The second procedure loops through the parsed X and Y values, and where it finds a nonnumeric value, it clears the cell containing the nonnumeric value. When rerun on a chart with changed data, it cannot restore the appropriate cell contents where it finds a numeric value, because the relevant formula or value was previously deleted.

If you plan to use this approach, it is best to leave the original data or calculations intact, and use another worksheet range that simply links to the original data, so the links are easy to recreate.

Sub FixSourceDataInSheet()
  Dim iPt As Long
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sXVals As String
  Dim sYVals As String
  Dim rXVals As Range
  Dim rYVals As Range
  Dim vXVals As Variant
  Dim vYVals As Variant

  With ActiveChart
    ' just process the orange series
    With .SeriesCollection(2)
      sFmla = .Formula
      vFmla = Split(sFmla, ",")

      sXVals = vFmla(1)
      sYVals = vFmla(2)

      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      vXVals = rXVals.Value
      vYVals = rYVals.Value

      For iPt = 1 To .Points.Count
        If Not IsNumeric(vXVals(iPt, 1)) Then
          rXVals.Cells(iPt).ClearContents
        End If
        If Not IsNumeric(vYVals(iPt, 1)) Then
          rYVals.Cells(iPt).ClearContents
        End If
      Next
    End With
  End With
End Sub

The Results

I selected the first chart (title = “Change Chart”) and ran the first procedure (FixLineFormatInChart). Then I selected the second chart (title = “Change Data”) and ran the second procedure (FixSourceDataInSheet). Here is the resulting data. Note that the ChartFix data is unchanged because the FixLineFormatInChart procedure only changes the chart, while the DataFix data now has a couple blank cells because the FixSourceDataInSheet works by changing the data and leaving the chart alone.

Here are the resulting charts. The two procedures produce identical results, changing the interpolated line across point 5 (the #N/A value) to a gap, and changing the plotted zero at point 15 (the text label “x”) to a gap. The interpolated line across point 5 and the zero value plotted at point 15 remain in the blue series.

What if the Data Changes?

Worksheets are not always static pictures of data. The original data may change, or you may perform the same analysis with new data. The following table represents new data overlaid on the previous data range.

The original nonnumeric cells now contain numeric values, but we’ve gained text values in rows 4 and 5. Because the previously nonnumeric cells in column D were cleared, whatever formulas we had there could not recompute new values for cells D6 and D16. In the charts from before, we still see gaps, wither because we formatted them not to appear (left) or because we deleted the values (right).

We’re fine if we used the Change Chart approach, or if we simply pasted values on top of the data, which filled in any blank cells from before. But if we deleted formulas, as in column D above, we need to restore them, as shown in the worksheet range below.

The chart with reformatted points still shows gaps (left) but the formatting will be restored to numeric points by the procedure. The chart with unchanged formatting and restored data (right) shows all markers and line segments. The text values are plotted as the two zeros near the left edge of these charts.

We run the corresponding procedures on the two charts. The data is changed in column D, as before.

The orange series in both charts now show gaps where the blue series remind us of the text values plotted as zero.

Parsing the Series Formula

A series formula has the following form:

=SERIES(Sheet1!$C$2,Sheet1!$A$3:$A$19,Sheet1!$C$3:$C$19,2)

The four arguments of the series formula are:

  1. Series Name – can be a worksheet address or defined name, a text label, or empty. In this example it’s a cell reference, Sheet1!$C$2.
  2. X Values (Category Labels) – can be a worksheet address or defined name, a literal array such as {1,2,3} or {“A”,”B”,”C”}, or empty. In this example it’s a worksheet address, Sheet1!$A$3:$A$19.
  3. Y Values – can be a worksheet address or defined name, or a literal array such as {1,2,3}. In this example it’s a worksheet address, Sheet1!$C$3:$C$19.
  4. Plot Order – an integer, in this case 2.

Generally to parse the series formula, you first need to strip off everything except the parameters, which means the open parenthesis and the preceding text, and the closing parenthesis. Then you split the resulting string into a comma separated array. This works as long as none of the ranges contain multiple areas, in which case there are commas separating the addresses of the individual areas, and simple separation by commas will produce surprises. The result is a zero-based array.

Since we only want the X and Y values, we don’t care that the first parameter will contain the opening parenthesis and the preceding text, or that the last parameter will contain the closing parenthesis.

So our code looks like this, with loads of nice documentation:

    With .SeriesCollection(2)
      ' get series formula
      ' =SERIES(Sheet1!$C$2,Sheet1!$A$3:$A$19,Sheet1!$C$3:$C$19,2)
      sFmla = .Formula

      ' split formula into its arguments
      vFmla = Split(sFmla, ",")
      ' vFmla(0) = "=SERIES(Sheet1!$C$2"
      ' vFmla(1) = "Sheet1!$A$3:$A$19"
      ' vFmla(2) = "Sheet1!$C$3:$C$19"
      ' vFmla(3) = "2)"

      ' get the individual addresses for X and Y
      sXVals = vFmla(1)
      sYVals = vFmla(2)

      ' find the ranges containing the X and Y values
      Set rXVals = Range(sXVals)
      Set rYVals = Range(sYVals)

      ' put the values from the X and Y ranges into arrays
      ' (faster processing in an array than cell-by-cell in a range)
      vXVals = rXVals.Value
      vYVals = rYVals.Value

Now we can test for nonnumeric values in the arrays, and either clear the corresponding cells or change the formatting of the plotted points.

You can put the X and Y values of a series directly into an array as follows:

      vXVals = ActiveChart.SeriesCollection(2).XValues
      vYVals = ActiveChart.SeriesCollection(2).Values

The problem here is that the values saved internally in the series have already been changed. The #N/A remains #N/A in the array, but any blanks and any text values are converted to zeros, so they are undetectable as nonnumeric.

Peltier Tech Chart Utility

Create and Update a Chart Using Only Part of a Pivot Table’s Data

In Referencing Pivot Table Ranges in VBA I showed how to identify certain sections of a pivot table using VBA. In Dynamic Chart using Pivot Table and VBA I showed how to update a non-pivot chart when a pivot table updates.

How about some code that makes a chart based on only part of a pivot table, and updates it on demand?

The Pivot Table

The data we’re using shows causes of failure in a system, broken down by categories and types of failure. It’s the same data used in the dynamic chart article cited above.

Pivot Table Source Data

Here’s the pivot table from the dynamic chart article. The duplicate columns Main Category and Category were needed in order to have the categories (electrical, mechanical, etc.) in both the row area and column area of the pivot table. Doing this plots each category’s defect data in a different series, so each category is shown in a different format.

Pivot Table

If we just make a chart from the pivot table, here is the result. Not bad, but our objective is to show only one category, say, Environmental.

Pivot Chart

One of the limitations of a pivot chart is that we can’t plot a subset of the data. Well, we could, but we’d have to filter the pivot table so only the information we wanted to chart was visible.

Even if we selected only the data we wanted to plot, Excel would automatically expand the data range to the extent of the pivot table, and hand us a pivot chart.

We can still plot only part of the pivot table in a regular chart, but we need to take some special measures, as described in Making Regular Charts from Pivot Tables. For example, the selected range has to be nowhere near the pivot table when we insert the chart. Then we need to add the chart data one series at a time. Any attempt to enter a portion of the pivot table in the top portion of the Select Data Source dialog will convert the chart to a pivot chart and convert the source data range to the entire pivot table.

Chart Source Data in the Pivot Table

Basically, we need the subcategories of the environmental category for our X axis labels, we want the corresponding defect totals for our Y values, and we need the environmental category label for the series name.

I’ll use the approach I described in Referencing Pivot Table Ranges in VBA to identify these ranges.

The code to select the environmental category label (series name) is

ActiveSheet.PivotTables(1).PivotFields("Category").PivotItems("Environmental")
    .LabelRange.Select

Series Name: Environmental Pivot Item Label

Very good, the label is selected. One part done, the easy part.

If we use

ActiveSheet.PivotTables(1).PivotFields("Category").PivotItems("Environmental")
    .DataRange.Select

we would select the entire column of environmental defect data, including the blank cells for the other categories.

Category Pivot Field - Environmental Data Range

Likewise, if we try

ActiveSheet.PivotTables(1).PivotFields("Main Category").PivotItems("Environmental")
    .DataRange.Select

we end up with the entire row of data, including those blank cells.

Main Category Pivot Field - Environmental Data Range

What we need is the intersection of those two ranges, or

Intersect(ActiveSheet.PivotTables(1).PivotFields("Category")
    .PivotItems("Environmental").DataRange,
    ActiveSheet.PivotTables(1).PivotFields("Main Category")
    .PivotItems("Environmental").DataRange).Select

Y Values: Intersection of Environmental Data Ranges

Perfect. Now for the category labels. Trying

ActiveSheet.PivotTables(1).PivotFields("Category").PivotItems("Environmental")
    .DataRange.Select

gives us all of the subcategories.

Subcategory Data Range

We only want some of those, so let’s poke around a little deeper.

ActiveSheet.PivotTables(1).PivotFields("Main Category").PivotItems("Environmental")
    .LabelRange.Select

gives us the four cells we want, but not in the right column.

Main Category Pivot Field - Environmental Label Range

So let’s use the interesection of the column of subcategory labels and the entire row of environmental data:

Intersect(ActiveSheet.PivotTables(1).PivotFields("Subcategory").DataRange,
    ActiveSheet.PivotTables(1).PivotFields("Main Category")
    .PivotItems("Environmental").DataRange.EntireRow).Select

Category Labels (X Values): Intersection of Main Category Environmental Ranges (entire row) with Subcategory Data Range

Good, so we’ve figured out the ranges we’ll need.

Let’s Write Some Code

Now that we know what we’re starting with (a pivot table), how much of it we’ll need (the chart data ranges we divined above), and the output (a chart), it’s time to write some code.

I’ll do this in two parts. I’ll use a routine that updates a specified chart based on the ranges within the pivot table. I’ll write a routine that makes sure that the selected range doesn’t overlap with a pivot table and insert a new chart, then call the first routine to populate the chart. Finally I’ll write a short routine that calls the update chart routine, specifying the active chart.

The following routine accepts a chart from whatever code calls it, defines the various ranges it needs, uses the addresses of these ranges to construct the series formula, then clears the data out of the specified chart and replaces it with a series that uses this formula.

Sub UpdateChart(cht As Chart)
  Dim rXVals As Range
  Dim rYVals As Range
  Dim rName As Range
  Dim sSrsFmla As String
  Dim PT1 As PivotTable

  On Error GoTo LeaveThisMess

  ' define chart data ranges

  Set PT1 = ActiveSheet.PivotTables(1)

  Set rName = PT1.PivotFields("Category").PivotItems("Environmental").LabelRange

  Set rXVals = Intersect(PT1.PivotFields("Subcategory").DataRange, _
      PT1.PivotFields("Main Category").PivotItems("Environmental") _
      .DataRange.EntireRow)

  Set rYVals = Intersect(PT1.PivotFields("Category").PivotItems("Environmental") _
      .DataRange, PT1.PivotFields("Main Category").PivotItems("Environmental") _
      .DataRange)

  ' write series formula

  sSrsFmla = "=series(" & rName.Address(, , , True) & "," & _
      rXVals.Address(, , , True) & "," & rYVals.Address(, , , True) & ",1)"

  With cht
    ' clear out chart data
    Do
      If .SeriesCollection.Count = 0 Then Exit Do
      .SeriesCollection(1).Delete
    Loop

    ' add brand new series with data we've defined
    With .SeriesCollection.NewSeries
      .Formula = sSrsFmla
    End With
  End With

LeaveThisMess:

End Sub

The following routine moves the active cell to the bottom right cell of the sheet, where hopefully there won’t be any pivot tables. I could do more checking, but I won’t. Then the sheet is scrolled up to the top left cell.

It disables screen updating while it does this, so the user doesn’t get a case of vertigo.

The routine inserts a chart in the default position (centered in the window), makes it a clustered column chart, then sends the chart to the routine above to be populated for the first time.

Sub CreateChartFromPartialPivotTable()
  Dim cht As Chart

  Application.ScreenUpdating = False

  On Error GoTo BailOut

  ' make sure active cell isn't in pivot table
  ' if it were, the chart would be a pivot chart
  With ActiveSheet
    .Cells(.Rows.Count, .Columns.Count).Select
  End With
  With ActiveWindow
    .ScrollColumn = 1
    .ScrollRow = 1
  End With

  ActiveSheet.Shapes.AddChart
  Set cht = ActiveSheet.ChartObjects(1).Chart
  cht.ChartType = xlColumnClustered

  UpdateChart cht

  ActiveSheet.Cells(1, 1).Select

BailOut:
  Application.ScreenUpdating = True

End Sub

This last routine makes sure a chart is selected, and if so, calls the first routine to repopulate it.

Sub UpdateActiveChart()
  If Not ActiveChart Is Nothing Then
    UpdateChart ActiveChart
  End If
End Sub

Fairly elegant for something so quick and dirty.

Finally, The Chart

And oh yeah, here’s the chart. I’ve shown it next to the pivot table with the plotted series selected, so you can see the highlighted ranges used to plot the data. Environmental defect totals as Y values (blue highlights), environmental subcategories as X values (purple highlights), and Environmental label as series name (red highlight).

Chart and Pivot Table, with Source Data Highlighted

Peltier Tech Chart Utility

Multiple Line Charts by Category

The Problem

I encountered a new blog recently called HelpMeViz.com, which was recently established to give the visualization community a forum to discuss their projects, and give and receive advice and feedback about their work.

In Excel: Multiple Lines Across X-Axis Categories, Lars Verspohl described a column chart with columns forming 12-month time series of some variable for nine countries. Lars wanted to plot another variable on this chart as a set of line chart time series. This is his sketch of the chart he envisioned (click on the image to see it full size). Multiple Lines Across Column Chart Maarten Lambrechts suggested using lines for both variables, dispensing with the original column chart. I agreed, and since the description of how I would do it was much too long to fit into a comment, I have written this tutorial.

Data

The data I used in my example is shown below. Since I made up the values, I didn’t bother showing all 118 rows of the worksheet, just enough to see the pattern. The blue shaded range has the values we want to plot. There are 12 monthly values for each country, plus a blank in between countries. The blank leaves a gap between the connected points of one country and the next. The pink shaded range contains data for a dummy series which will anchor the country names along the bottom axis. The X values (6.5, 19.5, etc.) are centered within each country’s monthly points (6.5 is between June and July for Spain, etc.). The Y values are zero so the points are located along the bottom of the chart. The green shaded range contains the country names which will be used as data labels for this dummy series. Multiple Line Charts by Category - Data

The Chart

Select the blue shaded range, and insert an XY Scatter chart, the style with lines and no markers. I’ve already fixed the X axis so it has a tick label (and vertical gridline) every 13 units, from 0 to 9×13=117. Multiple Line Charts by Category - Chart 1

The Labels

Select and copy the pink shaded range, then select the chart, and use Paste Special to add the data to the chart as a new series, by column, with X values (categories) in the first column and series name in the first row. I’ve formatted this new data as round markers without a line, so you can see how it all works. Multiple Line Charts by Category - Chart 2 If you have Excel 2013, select the added points, and add Data Labels, using the Below position, and the Labels from Cells option, selecting the green shaded range with country names for the labels. If you don’t have Excel 2013 (or even if you do) you can use Rob Bovey’s Chart Labeler add-in to add these labels. It’s free, but you’ll need to download and install the Labeler before you can continue. Again, position the labels below the Label series data points, using the labels in the green shaded range. Multiple Line Charts by Category - Chart 3

Clean Up

Hide the Label series by formatting it with no line and no markers. Choose No Labels for the X axis. Delete the Label legend entry by clicking on it once to select the legend and again to select the legend entry, then pressing Delete. Multiple Line Charts by Category - Chart 4 That’s pretty good, and it didn’t take all that long.

Secondary Panel

Most of the time, the two different variables will have much different magnitudes. It is tempting to stick one of the variables on the secondary axis, and let the two series just lie across each other. But there are many more good reasons not to overlap the data than good or bad reasons to overlap them. So we’ll put the secondary variable into a secondary panel of the chart. We’ll start by formatting the Red series so it is on the secondary axis (ignore the fact that Red and Blue values are not so different). I’ve gotten a head start here by giving the plot area a slightly darker gray border, and giving the horizontal gridlines a lighter gray. Multiple Line Charts by Category - Chart 5 Now we need to fix the scaling. The Blue data spans 0 to 0.6, and to fit it into the bottom panel, we will scale the left hand axis from 0 to 1.2. The Red data really spans 0 to 0.4, but I’ll use 0 to 0.6 as its range to make things work out nicely. To make Red fit the top panel, we’ll scale the right hand axis from -0.6 to 0.6. Multiple Line Charts by Category - Chart 6 The scales work out nicely, and the primary horizontal gridlines fit both axes. Sometimes this requires more trial and error. Now we need to neaten up the axes. We don’t want the primary axis ticks to extend across the secondary panel, and vice versa, so we’ll use custom number formats to hide the labels we don’t want. The right hand axis is easy. Format the axis so it has a custom number format of 0.0;;0.0; which has four elements separated by semicolons. The first element tells Excel to use one decimal digit for positive labels. The second element is actually absent, telling Excel not to show negative labels. The third element tells Excel to display zero labels with one decimal digit. The fourth element is also absent, indicating that text labels should not be shown. The left hand axis is a bit trickier. Format the axis so it has a custom number format of [<=0.6]0.0; which has two elements separated by semicolons. The first element has a conditional expression in square brackets which tells Excel to use one decimal digit for labels less than or equal to 0.6. The second element is absent, telling Excel to omit labels that do not meet the first condition. Finally, we need a line to separate the top and bottom panels. Let’s use the horizontal axis, which so far has remained at the bottom of the chart. First, I formatted this axis to match the plot area border. Then I formatted the left hand axis so that the horizontal axis crosses at 0.6. Multiple Line Charts by Category - Chart 7 Separating the series into distinct panels clarifies the data even when they don’t need different axis scales.

Keeping the Column/Line Style

In a follow-up comment to the original article cited at the top of this tutorial, Lars Verspohl indicates that he wants to keep the columns, and not convert everything to lines. This is also possible.

The Data

The column chart data is a simple grid of months by countries, shown in the blue shaded range below. The data for the lines is given in the red shaded range below. I’ll explain the calculation of X values in column N in a moment. Multiple Lines and Columns by Category - Data

The Column Chart

The column chart is easy. Select the blue shaded data, and insert a Clustered Column chart. Switch rows and columns, because Excel by default wants to plot fewer series of more points each. Multiple Lines and Columns by Category - Chart 1 The most tedious part of making this chart is formatting all the graduated shades of blue.

X Values for the Added Lines

I’ll use a subset of the data below to show how to compute the X values. The column chart by default has a gap width of 150%. That means the gap between the clusters of columns is as wide as 1.5 individual columns. This means there is a gap of 0.75 columns to the left and to the right of each cluster, and the entire width of a category is 0.75 columns for the gap on the left + 12 columns for the months + 0.75 columns for the gap on the right, or 13.5 columns. The categories are internally numbered starting at 1 for the first category up to N, the number of categories. These whole numbers are the X values at the center of each cluster (at the vertical yellow lines in the chart below). By extension, the X values between clusters are the halves. Rather than 0 to N, then, the X axis effectively runs from 0.5 to N+0.5, as shown by the numerical labels at the top of the chart below. The first data point for the red line is in the middle of the first column, so it is 0.75 columns for the gap and 0.5 columns for the half of a column to the right of the vertical axis, which itself has an X value of 0.5. We want X to go from 0.5 at the left of the first gap to 1.5 at the right of the first gap. So the first X value is 0.5+(0.75+0.5)/13.5, or 0.59259. The second data point is in the middle of the second column, one column to the right of the first point, so its X value is 0.5+(0.75+0.5+1)/13.5, or 0.66667. The third X value is 0.5+(0.75+0.5+2)/13.5, or 0.74074. Continue to the last X value for the first continuous line, at 0.5+(0.75+0.5+11)/13.5, or 1.40741. There is a blank row between countries, so there will be a gap in the plotted line. The next set of X values is calculated by adding 1 to the first set of values. Continue for the rest of the countries.

Multiple Lines and Columns by Category - Chart 2

When this data is plotted by itself in an XY Scatter plot, the result is shown below. I’ve set the axis scale to 0.5 to 9.5 to match the internal X values in the column chart. A quick comparison of this chart with the column chart indicates that our calculations are correct. Multiple Lines and Columns by Category - Chart 3

Adding the Lines

Adding the lines will take a few steps. First select and copy the red shaded range which includes our meticulous calculations. Then select the chart, and use Paste Special to add the data as a new series, by columns, with X values (category labels) in the first column, and series names in the first row. Multiple Lines and Columns by Category - Chart 4 This temporarily scrunches the existing columns way to the left of the chart, and makes all of the columns very thin. Right click on the skinny red columns, choose Change Series Chart Type, and select the XY Scatter style with lines and no markers. Excel automatically places the XY series on the secondary axis. The secondary X axis (top) runs from 0 to 10, so the red lines are not in alignment with the blue columns. The secondary Y axis (right) is also not in alignment with the primary Y axis (left). Multiple Lines and Columns by Category - Chart 5 The last step is to format the XY Scatter series so it is plotted on the primary axes with the Clustered Column series. This brings all the data into alignment. Multiple Lines and Columns by Category - Chart 6

Peltier Tech Chart Utility

Simple Interactive Charts in Excel

If you’re making a dashboard-style report in Excel, you may prefer that a chart not be cluttered with all of the available data, but instead allow the report consumer select which set of data to analyze.

Sample Data

This example will let the viewer select from among the three options in this table.

Sample Interactive Chart DataConstruction

Start by moving the original data range (shaded orange) down several rows, out of the way of the chart. This data can actually be located far away, even on another sheet.

Insert a “staging area” (shaded green below) and insert some temporary dummy data. This area can also be located anywhere else.

Create a chart using this staging area as its data source, and position it where desired.

Interactive Chart Setup

You need to make the Developer tab visible in the ribbon, if it isn’t already. In Excel 2010 and 2013, click the File tab, click Options, click on Customize Ribbon, and in the right-hand list, check the box in front of Developer, and click OK.

On the Developer tab, click Insert, then in the Form Control section of the dropdown, click on the Combo Box button (second from left in first row).

Insert Form Controls

The cursor turns into drawing crosshairs. Draw the Combo Box, then position it where you need it.

Interactive Chart Setup

Right click on the Combo Box control, and choose Format Control. Click in the Input Range box, then select the range of cells that contains the items to show in the list (A18:A20). Click in the Cell Link box, and select the cell where the index of the selected list item will be displayed (A17). Click OK.

Format Dropdown Control

Write formulas in the green-shaded staging area. Select A15:E15, with A15 being the active cell, enter the following formula, then hold Ctrl while pressing Enter, to enter this formula into all of the selected cells:

=INDEX(A18:A20,$A17)

Note that the Combo Box control’s link cell is shaded blue in the screenshot below.

Interactive Column Chart A

The interactive chart is now live. When you select another item in the Combo Box, the data in the staging area updates, and the chart displays the new data.

Interactive Column Chart B

Options

You could just as easily use this interactive technique with a line chart, or pretty much any chart type.

Interactive Line Chart A

You also can locate the Combo Box control wherever you want. In the example below, it has been placed over the chart, taking the place of the chart title.

Interactive Line Chart C

You are not constrained to using Combo Box controls. You could insert a List Box control (fifth button in the first row of the Insert Form Control dropdown on the Developer ribbon tab).

Interactive Column Chart C

The format dialog for the List Box control is essentially the same as for the combo box, with an Input Range for the cells containing the items in the list, and a Cell Link for the cell displaying the selected option.

Format Dropdown Control

You could also use a series of Option Buttons (last button in the top row of the Insert Form Control dropdown on the Developer ribbon tab).

Interactive Line Chart B

The format dialog for the Option Buttons only has a Cell Link box for the cell that indicates which option button is selected. You only need to format one Option Button in the set, though there is no Input Range, so the caption of each Option Button must be typed individually.

Format Dropdown Control

There are pros and cons of each of the control types used in this example.

The Combo Box is most compact, but only shows the selected item

The List Box shows all items in the list, but requires a space tall enough to display them all.

The Option Buttons show all items, and requires enough space for all Option Buttons needed. But these controls may be arranged in a column, or in a row, or in whatever arrangement you want.

Interesting Control Behavior

We already know that changing the control changes the Cell Link value, and therefore the staging area data and chart update. But typing a number manually into the Cell Link changes the staging area and chart, and also changes the control.

And if for some reason you have multiple controls that use the same Cell Link, changing any one control changes all of the controls as well as the staging data and chart.

This can be useful if you have several sheets of interactive charts, and you want all of the charts to show the same option. Set up only one Cell Link, on only one sheet, and use this Cell Link for all controls, and use its address in the formulas of all staging areas.

Peltier Tech Chart Utility

Multiple Width Overlapping Column Chart

I read a post entitled Calling All Graph Wizards – Overlapping/Stacking Graphs w/o secondary axis on the Mr Excel forum today, and decided the question was broad enough and the answer quick and elegant enough that it was worth sharing.

The problem was that the user wanted to show projected and actual values of one variable as columns on the primary axis and of another variable as lines on the secondary axis.

If you only have the one variable, you can plot projected on the primary axis and actual on the secondary axis, then use a smaller gap width (wider bars) on the primary axis and a larger gap width (thinner bars) on the secondary.

Dual Column Width Chart Using Primary and Secondary Axis

But you can’t use two gap width settings if the columns must be plotted on the same axis. When you plot them, the taller bars in the front obscure the shorter bars in the back, so you can’t compare the values.

Mono Column Width Chart Using Only Primary Axis

But you can still make Excel do what you want. (In fact, you can almost always make Excel do what you want, if you know how.) I’ll describe two ways to accomplish this.

Option 1: Fill Bars with Rectangular Shapes

This approach was the original topic of this tutorial.

Draw two rectangles, pretty tall. The taller the better for visual quality of the chart. Make the thinner rectangle the color you want, and make the thicker one transparent. Make the widths of the two rectangles in the same proportion as the widths you want for the bars in the chart. Center the two rectangles horizontally.

Rectangles for Dual Column Width Chart

Select both rectangles, and copy (Ctrl+C). Select the series in the chart, and paste (Ctrl+V). The chart now uses the copied shapes as the fill for the selected series.

Dual Column Width Chart Using Only Primary Axis

Pretty easy, once you know how.

This comes in handy too if you need more than two widths. Without this trick, you couldn’t make the following chart even if you could use the primary and secondary axes:

Triple Column Width Chart Using Only Primary Axis

This requires two pairs of rectangles, a clear one and a relatively wide filled one for the second series, and a clear one and a relatively narrow filled one for the third. These are shown below:
Pairs of rectangles for bar chart fills

The advantage of this approach over the next is that the narrower bars keep their relative width, which is fixed by the ratio of filled rectangle to transparent rectangle used to fill the bars. The disadvantage is that if you want to adjust the width of the narrower bars, you need to adjust the width of the rectangles, then copy and paste onto the chart series.

Option 2: Error Bars with Multiple Widths

My colleague Andy Pope has pointed out in the comments that another approach for this effect is to use error bars for the narrower bars. In Excel 2003 and earlier, you had few options for line width, but since Excel 2007, you can make lines of seemingly any arbitrary thickness.

I’ll show Andy’s technique for three sets of bars. It’s even easier for two sets of bars.

I’ll start with the original column chart, setting overlap temporarily to zero so the different sets of error bars don’t obscure each other. The first thing to do is hide the bars you want to display narrower, that is, use no fill color for them. I’ve kept a colored outline to show what’s going on.

Using Error Bars for Multiple Width Chart Series Bars

Add error bars to the series you want to show as narrower bars.

Customize the error bars using the Minus Only, No Caps, and 100% Percentage value options.

Using Error Bars for Multiple Width Chart Series Bars

Now comes the magic. Apply the desired line colors to the error bars, and make the error bar lines thicker. Here I’ve used 11.25 pt for the orange bars and 5.75 pt for the blue bars.

Then I’ve hidden the outlines of the original bars.

Using Error Bars for Multiple Width Chart Series Bars

Change the overlap back to 100 so the bars are all centered on the categories (the month labels).

This has made the unchanged bars for Plan much wider, so we need to adjust the line widths of our error bars. I’ve settled on 30 pt for the orange and 12 pt for the blue.

Using Error Bars for Multiple Width Chart Series Bars

I used a little trick to reapply the bar colors to the legend. When I made the bars transparent, I started with the entire series formatted with the desired color. Then I selected one bar at a time instead of the entire series, and used no fill for the bar. Working point-by-point in this way leads Excel to believe that the series as a whole has not been changed, so it leaves the legend entries alone.

The disadvantage of the error bar approach is that  any reformatting that changes the widths of the original bars (changing overlap, adding points to each series, stretching the chart) will distort the relative widths of the error bars and the original bars.

The advantage is that these widths can be adjusted very easily by formatting the error bar lines, without having to fiddle with the widths of the rectangles which must then be copied and pasted onto the series bars.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

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

 

Site by: Dawud Miracle, Business Coach & WordPress Websites