Bar Graph not to show zero columns

**peakymatt** · 06-06-2008, 07:08 PM

Hi all

I have a spreadsheet containing exam results. It contains 12 students over 7 exams. There is data in all exams except for Exam 5 & 6.

Is there anyway when the bar graph is created to get it not to show Exam 5 & 6 except for when there is data.

I have supplied a file to help explain this better

thanks

Matt

**Andy Pope** · 06-07-2008, 07:49 AM

Manually:
- you can simply hide the 2 columns and the chart will adjust.
- if you turned the formulated chart data so the assess where in rows you could use Autofilter to hide rows. But again not automatically.

Automatically
- with more formula to order the assess's and named ranges to determine what data is charted. See attached

**peakymatt** · 06-07-2008, 08:32 AM

Thanks Andy

Does the job quite nicely.

I will have to go away and sort out the "large" bit now

Thanks again

**peakymatt** · 06-10-2008, 07:21 AM

Hi Andy

Thanks for all your help. I need to take this chart one step further. I now have the colours working, and the bars not appearing when there is no data, however, I am setting this up for future courses, so therefore when I set up my tables with all their formulas and all, I get the good old "#DIV/0!" in all my cells, due to averages and the like. When this happens the chart go spastic, and comes up with errors.

Is there anyway to tell the chart to ignore the "#DIV/0!" until data is populated into the tables, cause once I put data into the tables the problem goes away

Thanks

Matt

**Andy Pope** · 06-10-2008, 07:31 AM

You adjust your formula that generate the #DIV/0 by wrapping an IF test around them. Output #N/A if the divisor cell is empty and or zero.

**peakymatt** · 06-10-2008, 07:33 AM

Thanks

I'll try that

**Thorian** · 02-13-2009, 11:50 AM

Hello Andy,

In follow up of the question or bar charts and 0 values. I am struggling for a longtime to find a proper automatic solution to excel not showing 0 amounts as value in charts.

I am building automated solutions where graphs source data is based on outcome of formulas. In case of line graphs you can use #N/A as result as data point and value will not be shown but this does not work for bar graphs.

On the graph I want excel to show the value in the bar when value is not 0 and in case the value is 0 not to show the value.

Many thanks in advance for your help.
Thorian

**Andy Pope** · 02-14-2009, 10:14 AM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.