Excel 08 Help! how to display null values in chart

[oakbough]

I am working on my thesis data where I have to create a lot of different graphs, all of which have cells where there was no sampling done and thus, no data. I would like to display a symbol or indicator in the graph where there is no data rather than it appearing as a zero value. I do not want to go through after creating the graph and importing it into my word doc and place text boxes with symbols where there is no data.

Excel help is garbage and has been worthless. I have searched the internet with no luck and now I am pleading for any help here. I am loosing my mind trying to solve what should be a very simple task. Please, if anyone can help!

Thanks.

 

[akadmon]

What kind of chart, scatter, bar? Post a picture of what you would like to see. Chances are there is a workaround. Being an Excel nerd that I am, I love being challenged, so bring it on!

 

[Gregg2]

Have you tried http://www.mrexcel.com/forum/forumdisplay.php?f=10&order=desc ?

 

[oakbough]

Thats awesome to hear akadmon! Thanks.

OK so I am constructing column graphs with three columns for each of month (X axis). I am comparing monthly bat activity sampled at three locations and there are some months where 1 or 2 or all 3 columns do not have data since I was unable to sample at that time. I would like to add a symbol or other mark at the place where a column would be on the x axis corresponding with months where there is no data.

Here is a pic of what I was thinking of. This is a crude example done in word on a copied picture of the graph from Excel. I used different symbols for months with 1 or 2 values missing between those months where all 3 are no data but that is not necessary, it juste seemed to work better in word. If there is a better way of indicating null values on a graph in excel other than this I would be interested to see it. This is just what I was thinking of as a solution to having a dataset with null values where it is important to distinguish between 0 and no data. I do not want to omit data and I would like to include months where there is no data for continuity.

I hope this helps, thanks.

 

[akadmon]

OK, I admit I'm stumped. I have come up with a workaround that will allow you to stay within Excel, bit I'm not sure if this will save you any time.

What I have in mind is creating links to your data in another range using formulas that contain booleans that would replace the zero value with a negative value (i.e. if a cell is blank then value is -X, otherwise value is value). The next step is to create a column chart linked to this new range. Now select each negative value in the chart by clicking on it twice (important: you need to wait about 1 second between clicks, otherwise the "Format Data Series" dialog will come up, which you don't want). Next right click and select "Format Data Point". In the dialog box that comes up, select the "Picture" tab and choose an image file that contains the symbol you would like to show (you may have to create one in Photoshop or some other graphics editor if you don't like any of the choices that come preinstalled with Excel). You will have to repeat this for every negative datapoint (this can get tedious!). The last step is to replace the negative values in your formulas with some positive values that result in your graphic keeping the desired proportions (i.e., something that does not result in your graphic being unnaturally stretched). You can use the Replace function on the Edit menu to accomplish this.

 

[oakbough]

OMG!!! You are so cool!

Since my dat set is relatively small, though there are many sets, your advice works. All I had to do was put in a value of 1 in the cell without data, do a slow double click as you suggested, then format data point as picture.

thank you thank you thank you!!!!

It will take a little time but it will provide the consistency I need. Thanks again!!!!!!!!

 

[akadmon]

Glad I could help! I've been using Excel since version 1 (way back when it was a Mac only program), so I've had plenty of practice in problem solving like this. Mine aren't always the most elegant solutions, but they get the job done