×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Ignore blank cells in chart

Ignore blank cells in chart

Ignore blank cells in chart

(OP)
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

RE: Ignore blank cells in chart



Hi,

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

Skip,

glasses
tongue

RE: Ignore blank cells in chart

(OP)
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

RE: Ignore blank cells in chart

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

RE: Ignore blank cells in chart

(OP)
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

RE: Ignore blank cells in chart

No, read my post.

Regards,
Zack Barresse

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

RE: Ignore blank cells in chart

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

combo

RE: Ignore blank cells in chart

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close