Chart Numbers in Excel with Strings mixed in

Question

I have some data that I'm putting into a chart and formatting. There are some business rules where some of the data is "Protected" like in the example. The issue when graphing with the word "Protected" (or any other word) excel is graphs that point at the bottom of the X-Axis. I'd like the graph to look like the 2nd chart, but I'd like to avoid graphing off a hidden column because I think it would confuse my power users.

Any thoughts or insights are appreciated!

Answer 1

When manually building the chart:

1. Select the data point
2. On the Format ribbon, pick Format Selection
3. On "Format Data Point", choose Line, and select No Line.
4. Pick the next data point (corresponding to 2013).
5. On "Format Data Point", choose Line, and select No Line.

Here are my results ...

A small bit of VBA that will generate the chart ...

Sub MakeChart()
Dim cell As Range, mySerRng As Range, mySrcRng As Range
Dim mySht As Worksheet, myChrt As Chart
Dim lastRow As Long

Set mySht = Worksheets("Sheet1")
lastRow = mySht.Range("A" & mySht.Rows.Count).End(xlUp).Row

Set mySerRng = mySht.Range(mySht.Cells(1, 2), mySht.Cells(lastRow, 2))
Set mySrcRng = mySht.Range(mySht.Cells(1, 1), mySht.Cells(lastRow, 2))

Set myChrt = mySht.Shapes.AddChart2(-1, xlLine, mySht.Range("C1").Left, mySht.Range("C1").Top).Chart
With myChrt
    .SeriesCollection.Add Source:=mySrcRng, RowCol:=xlColumns, serieslabels:=True, categorylabels:=True, Replace:=True
    For Each cell In mySerRng
        If cell.Value = "Protected" Then
            .SeriesCollection(1).Points(cell.Row - 1).Format.Line.Visible = False
            .SeriesCollection(1).Points(cell.Row).Format.Line.Visible = False
        End If
    Next cell

End With
End Sub

---

Alternate approach

Build a scatter chart with multiple series, separated by the "offending" rows, and formatted so they appear to be one series ...

This has disadvantages:

• Likely more confusing to an end user
• Requires a lot of series if you have a lot of data with "Protected" scattered throughout