Excel Chart: Ignore Null Values Solved

Question

Hello everyone,

after receiving some valuable assistance from you, I won't be able to do without you anymore :)

So here is my new issue, which is certainly not one for most of you.

I am using Excel and would like to create a chart. The problem is that within my data range, there are some null values, and I don't want them to appear. I remember seeing an option like "Ignore if blank" or something similar, but I can't seem to find it, and having switched a few times between Open Office and Excel, maybe I'm confusing things?

Additionally, another question: do you know if it's possible to create a "sliding" chart, meaning to only take the last X cells of a column, even after adding new data? So I don't have to manually modify the displayed data range.

Thank you for your time and help.

Charles

Configuration: Windows XP / Safari 533.4

eriiic · Accepted Answer

Good evening everyone,

In the options (on 2003) you can choose the treatment for empty cells, not for the value 0.

To not display these points with a formula, replace 0 with =NA() (create an extra column if needed),
or set an automatic filter and filter on <>0

To display, for example, the last 10 values, you need to name dynamic ranges in the form =OFFSET($A$2,COUNTA(A:A)-11,0,10,1)
Then you select your curve and replace the ranges with their names.

See attached file with filtered 0s, NA(), and display of the last 10 values

eric

PS: an excellent site for dynamic charts (and many other things), that of J. Boigontier http://boisgontierjacques.free.fr/pages_site/graphiques.htm#GraphiqueDynamique

tontong · Answer

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.

Charles · Answer

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

tontong · Answer

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.

Charles · Answer

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

Le Pingou · Answer

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

Raymond PENTIER · Answer

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 ... :-) &#9787; Raymond &#9794;

Charles · Answer

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

Raymond PENTIER · Answer

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... :-) &#9787; Raymond &#9794;

Charles · Answer

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

Excel Chart: Ignore Null Values

10 réponses

Texture issues in enshrouded

Driver his

Peritel to hdmi conversion

How to access the bios on a compaq notebook

Dsi won't turn on anymore

Search for a song with the lyrics

Left mouse click, touchpad click inactive

Codes stronghold crusader

Stuck computer system repair

Disable write protection mp3 player