Ignore blank cells in chart

[bez999]

Hello
I have a set of data on an Excel spreadsheet that contains numbers in cells and blank cells. I am trying to create a bar chart that only plots the cells that are not blank but whatever I try I get gaps in the chart.
Is there a way to ignore blank cells for a chart?

Any help would be most appreciated.
Thanks

Regards
Chris

 

[SkipVought]

Hi,

Yes. Hide the row or column and be sure that your chart options Tools/Options - Chart Tab have Plot visible cells only.

Skip,

_[red][/red]

 

[bez999]

Thanks, Skip, but that is not a practical solution for me.
The data grows daily and would entail hiding a lot of intermediate rows. I would then have to unhide them later.

Thanks for the speedy response.

Regards
Chris

 

[xlbo]

charts do not plot the #N/A error

you could either do a find/replace blanks with #N/A or incorporate a formula that returns a #N/A rather than an empty string.....depends on your layout and how the data gets in there

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Check out Tushar Mehta's site on discontinuity of charts with the N/A..

http://www.tushar-mehta.com/excel/software/na_discontinuity/index.html

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[bez999]

Hello Geoff

I tried that earlier after reading about #N/A in another thread but I cannot get it to work.

As a test, in cells E6 to H6 I put the values 4, =na(), 6, 8. F6 shows #N/A as expected and I made a bar chart covering these cells. The resulting chart shows 4 columns where column 1 is 4, column 2 is empty, column 3 is 6 and column 4 is 8.

Am I doing something wrong?

My data is created by VBA as a result of data cells being updated but if I can get this n/a thingy to work it will solve my problem.

Thanks for helping.

Regards
Chris

 

[firefytr]

No, read my post.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[combo]

I'd get back to Skip's hint and use autofilter to hide/reveal unnecessary data.

combo

 

[bez999]

I have solved it. I remembered using a technique for filling a combobox with values from a list but excluding what has already been used. I adapted this code to produce a column containing the data I need but leaving out the empty cells.

Here is how I did it:
My data is in column E and has empty cells in between the cells that contain my data. I created a named range called Results for this whole column.

In every row of column F next to the list of data, I entered the following formula:
=IF(COUNTIF(Results,E1)>=1,ROW(),"")

In every row of column G next to the list of data, I entered the following formula:
=IF(ROW(E1)-ROW(E$1)+1>COUNT(F$1:F$26),"",INDEX(E:E,SMALL(F$1:F$26,1+ROW(E1)-ROW(E$1))))

I can then use column G as the source for my chart.

Thanks for your time, everyone.