I run a pool league and I have a chart of player handicaps over time. Handicaps change over time based on performance. Since not all matches are played on the same days, I chart against the ordinal value (1st match, 2nd match, etc.) rather than against time.

My problem is with the missing values at the end of each series. Excel plots my lines crashing to zero, making for an ugly (and wrong) chart. This is true whether I have a zero or a blank in the 1st unplayed match of each series.

Zeros within a range can be shown as a gap, but the zeros at the end of a series will not show as gaps.

I know that I can show #NA in the empty cells and they then will not display, which is what I want in the chart, but then my table of values is ugly, filled with #NAs for all unplayed matches.

How can I get both a chart with series which just stop at the last nonzero value and cells which show as blanks in the table for zero values? I'm already displaying zeros as blanks but that doesn't fix it.

Sample data and chart here

All data cells contain the same formula.

  • It would better if you can attach some sample data in a worksheet and describe what you have done and what you wanted. – steviG Aug 13 at 14:35
  • @JohnBiddle - the problem is your formulas probably are like =IF(something,value,"") (I didn't look at your file). The "" looks blank, but Excel will plot it as a zero value. Replace "" with NA(): =IF(something,value,NA()), which places #N/A in the cell but which isn't plotted in the chart. – Jon Peltier Sep 8 at 3:32
  • @jon Peltier See question paragraph 4. – John Biddle Sep 10 at 1:50
  • Sorry, I skimmed right over that. What you can do is use conditional formatting, based on a formula, and use a formula like =ISNA(cell reference). If true, change the font color of the cell to white to match the background of the cell. This is the first example in Excel Conditional Formatting Examples. – Jon Peltier Sep 10 at 12:40

What kind of Line chart did you create?

If you're using Line with Markers chart you can follow these steps:

  1. Select the Chart and go to Chart Design- Select Data: enter image description here

  2. Select Hidden and Empty Cells- check Connect data points with line- OK- OK: enter image description here

  • This did not work for me. Don't know if it matters but I'm using Excel 365. Also, how'd you do the video screencap? Nice. Just to be sure, the cells after the last data cell in each series, which show as blanks, have the same formulas that the cells showing numbers have. I've just had the formula show a zero if they haven't played that match yet and set Excel to show blanks instead of zeros in the worksheet. – John Biddle Aug 14 at 22:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.