Line chart, plot only data, not blank, and label x axis only with plotted data - Help, please

[aiki100]

Greetings all -

I have looked high and low and can see that a lot of people have asked about how to keep a line chart from plotting blank cells, but I seem not to be able to nail it down in any effective manner.

I have a column that is 25k rows. Each cell has a formula that produces a calculated amount, a $ amount, or returns blank ("").

The formula now is: =IFERROR(HM2/$B$2*$C$2,"")

As it is now, the chart's x axis labels the line chart 1-25k, with 1122 as the increment between the labels. However, In all, out of the 25k rows, there are only about 300 cells of actual data in the column in question.

What I would like is to have the line chart display only the cells that have data.

That data would be labelled with the row number it occurred on. Or if possible, I might also (instead of) wish to label the data in order of occurrence, simply 1-300.

I have seen tricks with tables, and seen approaches using "go to," and "constants," (which tells me there is no data available in the cells when I try it…) Nothing has worked so far.

Any thoughts, please, formula style and no VBA?

Line chart, only plot rows with data, connect those data points in the chat with a line, and label the x axis with row number, (or the number in the series).


Thank you all very much- any help much appreciated!

 

[DaBoggs]

Use Filter and hide the blank values. This will remove them from the line chart. When you need to go back and look at all the raw data you only have to clear the filter.

Other option would be a Pivot Chart which allows you to filter the data in the chart.

 

[aiki100]

Hello DaBoggs!

Thank you so much for being willing to share your thoughts on this! Although I was hoping to find a dynamic way to do this (non-manual intervention) your approach does indeed work, and I thank you for putting me on it.

In order to make it work for me, I took my (otherwise header cell) and entered this formula:

="<>" (i.e., all not-blank)

and filtered on that. (It does take several minutes to filter 25K rows however, not surprisingly...)


You could also enter:

="=" (i.e., all blank)

to filter on blanks.


Thanks again for your help! If you do indeed have some slick way to do this dynamically via formula, etc. I would love to know it. But if not, your solution worked just fine, and I hope you have a great day!

 

[aiki100]

Hi DaBoggs -

I think I hit reply to thread rather than reply to you, so please do see my thanks to you on the full thread - you were very helpful!