Excel Chart: Ignore Null Values

Hello,
With Excel (2003), when a chart is active, you can access the treatment of empty cells through the path Tools --> Options --> Chart tab.
It is possible to create a sliding chart if you name the data ranges using the Offset function.

Hello everyone

I tried your various solutions (thank you for taking the time to respond!) but it doesn't correspond to the problem.

It's true that I didn't specify whether I was on 2003 or 2007, the answer is 2003 :)
Then, I also didn't mention that I want to create a histogram, not a line chart, and here is a more detailed example.
Let's say I have:

Year CA Workforce
2006 3 5
2007 4 6
2008 0 6
2009 0 7
2010 3 6

From there, I want to create two graphs: one for CA and one for Workforce (which is not a problem in itself).
However, what I currently get for CA is a bar at 3 for 2006, then 4 for 2007, then 0 for 2008 and 2009, and then 3 for 2010.

What I want is for the graph to go directly from the year 2007 to 2010, treating 2008 and 2009 as nonexistent since they are zero.

The solution of filtering CA by "Show differences from 0" doesn't quite work because it will hide the two CA of 2008 and 2009 that are equal to zero (fine for the CA graph) but also the Workforces 6 and 7 for those years, which will no longer appear in the Workforce graph.

Is this possible in 2003? It doesn't seem like a very complex procedure; I would be surprised if they hadn't thought of it...

Thanks again for your responses and the time spent.

Best regards,

Charles

Hello,
One solution is to replace the custom automatic filter suggested by Éric with a more elaborate filter that will eliminate the 0s and copy the year and revenue columns to another location.

Hello,
I might be too late.
You can go through inserting a pivot chart and then apply the filter on the values [CA] and that's all good.
See example: http://www.cijoint.fr/cjlink.php?file=cj201008/cijHDk3SF5.xls

--
Best regards.
The Penguin

Hello again,

in principle, it's a solution, yes. "In principle" because I presented this to my tutor who doesn't want us to rewrite the data but wants to use the existing ones... -_-'

Is there another solution besides a good dose of hard drugs in his coffee? :)

Once again, thank you for your suggestions (which I find suitable, I want to emphasize :) )

Charles

No, Le Pingou, it's not too late, especially since you offer a solution where we were convinced there was none! Thank you.

If Charles had told us that he had the opportunity to work on pivot tables and pivot charts with his tutor, it would have pointed us in the right direction ...
--
Retirement is nice! Especially in the Caribbean ... :-)
☻ Raymond ♂

Hello everyone,

Thank you, this solution of a pivot chart does seem to be suitable. I will try to implement it. Thanks!

Indeed, I didn't think to mention that pivot tables could meet my needs since I am quite new to Excel and I don't master (or even know) all its tools. And my tutor doesn't either, so he can't guide me towards one solution or another.
Moreover, it’s a deliverable that we would like to have without really knowing if it was achievable. My position was not just a training role to get there, but also one of gathering information, to see if it was possible.

And I completely understand what you mean, Raymond, when talking about the issues posed by teachers, instructors, or tutors. However, I still want to clarify that if I'm coming to you, it’s because I've been entrusted with a task on which I'm stuck. This is by no means a request to complete work that my tutor is doing on the side, nor am I trying to pose a trick question by expecting something unfeasible.

Thanks again for all your solutions and the time you’ve spent on this!

Best regards,

Charles

Very well, Charles, and thank you for clarifying the context in which you are making your request.
I also must thank you because, thanks to your question (and the insightful intervention of my old friend Le Pingou), I learned a new procedure that will likely serve me well in the future.
It's true that I don't use pivot tables and data charts often enough, which would have certainly saved me time and improved my efficiency in a number of situations!
So, to you both, my cordial greetings, while awaiting to meet again in this forum.
--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂

Hello,

It seems normal to clarify things since it's a point that I indeed overlooked, albeit important.

As they say, "you learn something new every day." For my part, since August 24th at 2:55 PM, I have indeed learned many little tips regarding Excel and its graphical representations.

See you very soon for the next issue, and thank you again for the time you dedicated to this one. It's nice to see people who are there to help, and that's far from the case on all forums.

Thank you.

Best regards,

Charles