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!

Yes, the solution I'm looking for would ideally have text in the column that is graphed. (Or maybe some documentation proving it can't be done with text in the column). — KyleUp, May 03 '16 at 19:06
[This](http://www.pcreview.co.uk/threads/graph-while-ignoring-text-but-not-changing-text-to-na.3494476/) may not be straight from the Microsoft documentation, but if Jon Peltier says it can't be done, then I'd wager that it can't be done... — sous2817, May 03 '16 at 19:19
The word 'protected' is just text. It's populated when generating the excel document. — KyleUp, May 03 '16 at 19:49
Unless you can change that text to a =NA(), I have a feeling you're going to be out of luck. [Here](http://superuser.com/questions/480347/how-to-tell-excel-to-ignore-num-errors-in-plotting-a-line-graph) is something that's close (again, Jon Peltier giving the solution in a comment) — sous2817, May 03 '16 at 20:53

OldUgly · Answer 1 · 2016-05-09T19:25:32.757

When manually building the chart:

Select the data point
On the Format ribbon, pick Format Selection
On "Format Data Point", choose Line, and select No Line.
Pick the next data point (corresponding to 2013).
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

score 0 · Answer 2 · answered May 06 '16 at 18:43

0

While I agree with @OldUgly's answer, you could also just add another column next to the data, if you think it would be understandable.

=IF(ISNUMBER($B2),$B2,"")

That should do the trick.

answered May 06 '16 at 18:43

Weasemunk

455
4
16

This has the same issue using "Protected" has. Using a string (even an empty string) excel plots it at zero. I did play with this some and tried using NA() instead of "". This resulted in excel just interpolating the line between the previous and next point. Using the Hidden and Empty Cell settings with NA() had no effect. It acts as if it was set to "Connect data points with line" – KyleUp May 09 '16 at 13:20
If I'm hearing you right, the chart populates a 0 for an empty string, but not for a truly empty cell. If that is the case, you can do what I mentioned, then select your data. Find all "blank" cells with Home->Editing->Find & Select->Go To Special->Blanks. Then clear all cell data/formatting with Home->Editing->Clear->Clear All. – Weasemunk May 09 '16 at 14:26

score 0 · Answer 3 · answered May 10 '16 at 09:29

I'm kind of gutted that I couldn't figure the majority of this out, it seems like such a simple premise. I've made somewhat of a workaround but it could definitely do with some refinement;

I'm using a VBA function called Eval:

Public Function Eval(varRange As Range)
Dim varArray() As Variant
varArray = varRange
Dim R As Long
Dim C As Long
For R = 1 To UBound(varArray, 1)
    For C = 1 To UBound(varArray, 2)
        If varArray(R, C) = "Protected" Then
            varArray(R, C) = CVErr(xlErrNA)
        End If
    Next C
Next R
Eval = varArray
End Function

And a defined range called 'Chart' which has the following formula

=EVAL(INDIRECT("Sheet1!$B$2:$B$"&1+COUNTA(Sheet1!$B$2:$B$31)))

This makes the range dynamic, but you could just use =EVAL(B2:B6) if need be.

On your chart, edit the series values range to Sheet1.xlsm!Chart (or whatever your sheet is called), and you get the data range with any "Protected" values converted to blanks. The only thing is, the chart will plot the line between the two points regardless, I cannot make the chart plot the gap. Maybe someone with better google-fu than myself could come up with a solution?

Chart Numbers in Excel with Strings mixed in

3 Answers3