Pivot Chart Formatting Changes When Filtered

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 AcamporaJon 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).

Peltier Tech Chart Utility

Highlight a Specific Data Label in an Excel Chart

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

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

The VBA Approach

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

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

Highlight Max Data Label with VBA - Data and Chart 1

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

Highlight Max Data Label with VBA - Data and Chart 2

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

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

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

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

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

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

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

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

  End If

End Sub

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

Highlight Max Data Label with VBA - Data and Chart 3

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

Highlight Max Data Label with VBA - Data and Chart 4

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

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

Highlight Max Data Label with VBA - Data and Chart 5

The Non-Programmatic Approach

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

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

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

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

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

Highlight Max Data Label without VBA - Data and Chart 1

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

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

Highlight Max Data Label without VBA - Charts 2

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

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

Highlight Max Data Label without VBA - Charts 3

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

Highlight Max Data Label without VBA - Data and Chart 4

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

Highlight Max Data Label without VBA - Data and Chart 5

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

Highlight Max Data Label without VBA - Data and Chart 6

 

Peltier Tech Chart Utility

Color Plotted Points to Match Cells

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

Basic Data and Chart

Start with a simple data set:

Simple Data

Create a stacked column chart:

Simple Chart

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

Formatted Data and Chart

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

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

Data with colored cells

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

Chart with colored points

It works on bar charts too.

Chart with colored points

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

Legend colors do not change

Partially Highlighted Data and Chart

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

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

Data with highlighted cells

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

Chart with highlighted points

The VBA Procedures

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

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

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

    ColorPointsToMatchCells ChartIAmWorkingOn

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

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

  If cht Is Nothing Then GoTo OuttaHere

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

        On Error GoTo SeriesError

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

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

SeriesError:
    On Error Resume Next
  Next

OuttaHere:

End Sub

Note that I used

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

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

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

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

Peltier Tech Chart Utility

Dot Plots in Microsoft Excel

What’s a Dot Plot?

A dot plot is a simple chart that plots its data points as dots (markers), where the categories are plotted on the vertical axis and values on the horizontal axis.

Line Chart

It’s a nice plot, but it isn’t built into Excel’s default chart offerings. It’s not too hard to make your own Dot Plots, and the Peltier Tech Chart Utility features the Dot Plot as one if its custom chart types.

The Problem

A popular chart type in Excel is a line chart. Below is a line chart showing some simple data. It’s a pretty clean chart, especially without connecting lines between data points. But the labels are hard to read without hurting your neck.

Line Chart

It would be nice to be able to rotate this chart, to make the labels more readable. The only reason not to rotate the chart would be if the categories (Label A, Label B, etc.) were dates, because people are used to time being plotted horizontally. But we can’t simply make a “line chart” in Excel that had the categories on the vertical axis and values on the horizontal axis. The closest we can get is a bar chart.

Bar Chart

But the bar chart doesn’t look as clean, because of all that color. And if there are multiple sets of bars, it can be hard to distinguish the different series of data.

Bar Chart with Multiple Series

Let’s Build a Dot Plot in Excel

It’s possible and not too difficult to construct dot plots in Excel. But it takes a little work. We’ll use an XY scatter chart for the dots, and a bar chart for the vertical axis labels.

The Data

Here is some simple data for our dot plot. The “Labels” column provides the categories for the bar chart, which will adorn the vertical axis. The “Values” column provides the horizontal values for both bar and XY chart. And the “Height” column provides the vertical coordinates for the XY points. The height values are calculated using a simple algebraic formula:

=(ROWS($A$2:$A$17)-ROW()+ROW($A$2)-0.5)/ROWS($A$2:$A$17)

Dot Plot Data

Start the Chart

Select the first and second columns of data and insert a bar chart. I’ve already configured it to be slightly taller than it is wide.

Dot Plot Under Construction

The labels are going in the wrong order, so double click on the vertical axis to open the Format Axis dialog. In Excel 2007 you have so right click on the axis and choose Format Axis… from the pop-up menu (which works in any version). Or in any version you can select the axis, then press Ctrl+1 (numeral one).

Dot Plot Under Construction

Fun tip: Select any Excel object (a cell, a shape, a chart element) and press Ctrl+1 (numeral one) to open the corresponding Format dialog. I think this is my #1 time saving shortcut in Excel, and it ticks me off that other programs don’t use it too. I’m going to harp on you repeatedly until you’ve learned to do it automatically. You’re welcome.

While you’re formatting the vertical axis in the Format Axis dialog, choose Horizontal Axis Crosses At Maximum Category, so the horizontal axis labels return the the bottom of the chart.

Dot Plot Under Construction

Add the XY Data

Select and copy the second and third columns of data, select the chart, and use Paste Special to add the data as a new series, with series in columns, series name in first row, and categories in first column.

Dot Plot Under Construction

Excel adds the data as another bar chart series.

Dot Plot Under Construction

Right click on the new series, choose Change Series Chart Type from the pop-up menu, and select the XY Scatter type. Excel adds a new vertical axis on the right of the chart, since it can’t plot numeric values on the category axis on the right. Fortunately Excel can use the existing horizontal axis for both the bar and XY data.

Dot Plot Under Construction

Align the dots with the bars by formatting* the secondary vertical axis (right side of the chart) so that its minimum and maximum are 0 and 1.

*You used that Select plus Ctrl+1 trick, right? Right?

Dot Plot Under Construction

Format the vertical axis (Ctrl+1!!) so it has no line color and no axis labels, which hides the axis.

Dot Plot Under Construction

Format the bar chart series (don’t make me say it!) so that it has no border and no fill color, which hides the bars and leaves the dots standing alone. I’ve formatted the dots so they have no fill color and a thicker marker line, because I think these look better than boring old filled circles.

Dot Plot

And that’s our Dot Plot.

Adding more data to the dot plot doesn’t clutter it up the way it does with bar charts.

Dot Plot with Multiple Series

Drop Lines May Increase Readability

In this example, the labels are pretty close together, and some of the dots are pretty far away, so it might be tricky for people to tell exactly which dot goes with which label. Often dot plots have lines connecting the dots to the labels to clarify this. We’ll use horizontal error bars for these connecting lines.

Select the dots and add error bars. In Excel 2007 and 2010 find the Error Bars control on the Chart Tools > Layout tab. In Excel 2013 it’s on the menu that pops up when you click the plus sign icon. Choose any of the options, because we need to modify the settings.

Dot Plot Under Construction

For XY points, Excel adds vertical and horizontal error bars. Select the vertical error bars and press Delete.

Dot Plot Under Construction

Format the horizontal error bars, select the Minus direction and the No Cap style option, and for error amount, select the Percentage option and enter 100.

Dot Plot Under Construction

Finally format the error bars, giving them a light line color so they don’t overwhelm the dots.

Dot Plot

 

Peltier Tech Chart Utility

Another approach to plotting gaps in Excel charts

Constable Henry: Just a moment, sir. I know everyone in this neighborhood, but I’ve never seen your face before. Can you account for yourself?

Freddy: Yes, I am Dr. Frederick Fronkonsteen, newly arrived from America.

by The FrankensTeam

This guest post is from the E90E50 FrankensTeam, i.e., Roberto Mensa, Kris Szabó, and Gábor Madács. The team has done a great deal of work with Excel and its graphics, and they’ve shown how many types of “advanced” graphics can be accomplished right in Excel. Such FrankenCharts include heatmaps, treemaps, network diagrams, and a number of other creative displays. Oh, and they’re as crazy about the movie Young Frankenstein as they are about Excel.

While I was reading Jon’s work using VBA Approaches to Plotting Gaps in Excel Charts I started to think, because I felt there had to be another solution which was waiting to be discovered…

Jon provided two different VBA routines as a solution for the problem: one changes the chart series formatting, the other changes the underlying data. As he clarifies at the very beginning, the disadvantage of using VBA routine is that it must be re-run every time the data changes.

Some time ago I realized that it is possible to use union of ranges in chart series. You can do it by using the union reference operator in the SERIES formula of the chart or via named formula. (The union reference operator is the same as the formula separator character, which is a comma in US-English versions of Excel.) In the project where it came up it was not particularly interesting, so I just stored this fact in my mind.

Now, a few months later, thinking about the gap problem… Eureka!

The union of separate ranges could be a solution, especially if we can build it up using the UDF within a named formula!

I was very excited by this possibility, so I quickly put together some code and shared the idea with Jon, who suggested some modifications in the UDF. Then we did a lot of tests with Kris and Gábor and corrected the problems showed up while we tested the solution in different Excel versions.

Let us explain how it could work.

Below is a simple line chart plotting data from column B: the #N/A error value is not plotted (no marker) but the line is continuous, while the string is plotted as zero.

The series formula of the chart looks like:

=SERIES("ref to rng",,line_chart!$B$3:$B$13,1)

Where y values come from a continuous range: $B$3:$B$13

The idea

To plot gaps on the chart, you will need empty cells in the range used for y values. The union of ranges idea is very simple: we use the cells containing the number values from the original range (column B) but use empty cells from another column (column C) instead of the error or text values.

You can easily select the non-continuous range by mouse while holding the Ctrl key down.

On the below picture you can see how it looks from the Edit Series dialogue box.

And this is how the SERIES formula shows the union:

=SERIES("ref to rng",,
    (line_chart!$B$3:$B$5,line_chart!$C$6,line_chart!$B$7:$B$9,line_chart!$C$10,
    line_chart!$B$11:$B$13),1)

Note the y range is now surrounded by brackets, since it is composed of multiple areas.

You can easily define a named formula for the above range:

Name: y_rng_union
Refers to: =$B$3:$B$5,$C$6,$B$7:$B$9,$C$10,$B$11:$B$13

And use it as Series values:

Formula solution

To make a solution a bit more dynamic, instead of using static ranges, you can put together a dynamic solution with IF formulas. You have to check the cells one by one, and if the cell has a value to be plotted, then use the reference of the cell, else use an empty cell from another column. Then you separate the IF formulas by comma (or semicolon) to create the union of unique cells.

This way the chart will automatically be updated if the data changes, but if you have more data to be plotted, you will have to update the named formula manually.

For example you can put a similar formula to the name, supposing that column C is empty, so use the cells of this column:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,$C$3), IF(ISNUMBER($B$4),$B$4,$C$4), IF(ISNUMBER($B$5),$B$5,$C$5), ..., IF(ISNUMBER($B$13),$B$13,$C$13)

Or you can use only one empty cell (for example C3) instead of the cells of column C:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,$C$3), IF(ISNUMBER($B$4),$B$4,$C$3), IF(ISNUMBER($B$5),$B$5,$C$3), ..., IF(ISNUMBER($B$13),$B$13,$C$3)

More generally, you can use a name (for example x) referring to an empty cell:

Name: y_rng_union
Refers to: =IF(ISNUMBER($B$3),$B$3,x), IF(ISNUMBER($B$4),$B$4,x), IF(ISNUMBER($B$5),$B$5,x), ..., IF(ISNUMBER($B$13),$B$13,x)

If you are interested in the above formula solution, you may also find useful a help formula in an Excel range to build up the above (very long!) formula.

The logic is simple: A copy-down formula will concatenate together all the necessary IF(ISNUMBER(...) parts.

(Please note, if you use non-english versions of Excel, you will need to replace IF and ISNUMBER with your local language formula names. Also, you will have to replace the separator string (comma) if your version uses semicolons.)

For example, supposing that the header of your data is in B2, and data starts in B3, write this formula in cell M3 and copy down:

=IF(ROW(A1)=1,"=",M2&",")&"IF(ISNUMBER("&CELL("address",B3)&"),"
&CELL("address",B3)&",X)"

In our example we have to copy down this formula to cell M13 (since data ends in row 13). The string we get in M13 is exactly the formula we need to use in y_rng_union, so we copy M13 cell and paste as value. Now the formula could be copy-pasted as a reference into the name:

For better understanding, you can check these solutions in the without-udf example file.

Obviously the formula solution is not really effective and has limitations, but it could be an alternative for those who can not use VBA.

General solution with UDF-based named formula

For a more convenient resolution we will need some VBA code: a user defined function will build up the union-range and provide it to the chart, nested in a named formula. So if your data changes, the union range will change accordingly and if your data range grows, the chart will also capture new data.

Important to highlight that it will happen automatically, together with calculation, without any user interaction!

Here is the UDF code:

Function serie_with_empty(rDataIn As Range, _
        Optional cOffset As Long = 1)

    Dim rCellIn As Range
    Dim rCellOut As Range
    Dim rDataOut As Range

    Application.Volatile

    For Each rCellIn In rDataIn.Cells
        If Not IsNumeric(rCellIn.Value) Then
            Set rCellOut = rCellIn.Offset(, cOffset)
        Else
            Set rCellOut = rCellIn
        End If

        If rDataOut Is Nothing Then
            Set rDataOut = rCellOut
        Else
            Set rDataOut = Union(rDataOut, rCellOut)
        End If
    Next

    Set serie_with_empty = rDataOut

End Function

The procedure loops through the data range (first argument) and checks for non-numeric values. If the value is numeric (so to be plotted) then the cell itself is added to the union-range. If the value is not numeric, then another cell determined by offset will be added to the union. At the end, the function returns a range object.

This range object could not be used directly on the chart, it must be wrapped in a named formula.

To avoid errors when UDF could not be run (because macros are disabled) we suggest using an error check in the named formula:

Name: y_rng_union
Refers to: =IF(TYPE(serie_with_empty(rng,0))=16+NOW()*0 ,rng, serie_with_empty(rng,1) )

Where rng is the original data range, serie_with_empty(rng,1) is the UDF-calculated union-range.

The second argument of the UDF is the column offset parameter. Above we use 1 supposing that the column next to the original data is empty. You have to ensure a sufficient number of cells in this column. The offset parameter is optional with 1 as default value.

You can find more details about the logical test part of the IF formula under section Further details.

Using the above defined name on the chart, the result is correct with gaps, and refreshes as your data or underlying range changes.

The method could be used for xy scatter charts too. For this chart type only the y values need to have empty cells for gaps, so no need to change the original x values range. As an example here is a possible SERIES formula of an xy chart:

=SERIES("ref to y_rng_union" , line_chart!$D$3:$D$13 ,
   Serie_with_empty_udf_xy.xlsm!y_rng_union , 1)

with the arguments:

x values (normal range): line_chart!$D$3:$D$13

y values (UDF calculations): Serie_with_empty_udf_xy.xlsm!y_rng_union

where line_chart is the name of the worksheet containing the x values, and Serie_with_empty_udf_xy.xlsm is the name of the workbook.

You need to reference the name y_rng_union to the worksheet or workbook. I usually start my chart with a regular range in the formula, so I leave the worksheet name and exclamation point intact, and replace the address with the named formula, like line_chart!y_rng_union. If necessary, Excel will change the worksheet name to the workbook name.

Below you can see what our example xy chart looks like, illustrated with ranges as plotted.

We created two example files, one for line chart, another for xy chart. We used random data, so after pushing F9 you can see how data, ranges and chart changes.

Further details

We need to test if the UDF was run or not.

In practice, macros are usually disabled in the Trust center under Excel Options. When you open the file, Excel will try to refresh the chart, but the UDF code will be unable to run, so the range will not be correctly built. The chart will raise an “Invalid reference” error message. Unfortunately, after enabling the macros, the chart will not be refreshed, because it remembers that the reference is invalid.

If the UDF can not run it results a #NAME error, which is number 16 in the TYPE formula. In this case the IF formula will give the original range, so the chart will get a valid range – although the line will not have gaps. To see the correct gaps, you need to enable macros and trigger calculation (so push F9). We use a volatile formula in the name ( NOW()*0 ) this is necessary for the chart to be updated after calculation. (The chart ignores the Application.Volatile in the UDF code!)

Problems with Cell.Text property

The base concept of gaps on the chart is to plot only numbers. In the UDF,

If Not IsNumeric(rCellIn.Value)

tests the cell’s .Value property. We were intending to provide another possibility for the users to mark unplottable data using formatting. For example they could set a custom formatting to hide 0 values if these should not be plotted. In VBA Cell.Text property could be used to get cell’s text as displayed on the screen, but using .Text property caused Excel crashes in case of xy charts. In case of line charts we have not observed crashes, but the SERIES formula was not visible on the formula bar, so we stopped using the .Text property.

About the authors:

Roberto Mensa, Kris Szabó, and Gábor Madács are three Excel-enthusiastics from Europe, thrilled to push the boundaries of Excel and do what seems to be impossible. You can find more about them on their team-site.

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