Hide Columns in Bar Chart Based on Rule

[mcuozzo]

Hi,
In the attached simple example, I'd like to have Excel not show Test 2 in the Bar Chart because it's value equal 0. How can I do this?

Thanks,
Michael

[teylyn]

Hi,

the quickest fix would be to hide column B. Hidden columns will not be graphed by default.

If you need a more dynamic solution, you will need to set up a helper table which populates only with non-zero values. Check back if that's what you're after.

[mcuozzo]

Hi,
Manually hiding columns will not be a good option for me because the data is going to be constantly updated through the use of a data connection. I need Excel to be able to dynamically calculate if it should display a column or not.

[teylyn]

The whole procedure is explained in this thread http://www.excelforum.com/excel-char...ic-x-axis.html where the data is arranged in rows instead of columns. Are you able to work with that?

[mcuozzo]

Thanks...I might be able to work with the sample provided in the thread. However, my data is coming through a data connection on sharepoint list and therefore, it is arranged by columns, as in my original example. Can you modify your example to handle the column format?

Thanks,
Michael

[teylyn]

here is the arrangement for your horizontal layout.

[mcuozzo]

Thank you, this is very helpful. However, I still have a problem because this solution forces me to reselect the data I want included in the chart every time the data itself changes. The source data in the spreadsheet will be constantly changing though and can be different every time a user opens up the workbook because the data is being pulled from a connection. Is there a way around this problem. Let me know if you need further explanation.

[teylyn]

I don't understand. Why would you need to reselect the cells if the data changes? In the file I attached, you can change the data and the chart adjusts. That's the whole point.

What is different in your situation?

[mcuozzo]

Hi, please see the attached example to understand my problem...In the Failed Test Data worksheet, you will notice that the only two columns which display in the chart are those that have the word "Fail" in their columns in the Raw Data worksheet. This is great, and as expected. However, suppose I was to change the value in cell C2 in the Raw Data worksheet (Windows Login NPC) from Pass to Fail. This would subsequently change the helper table in the Failed Test Data worksheet and the chart by showing only the Windows Login User and Windows Login NPC data in the chart. The Login to the ADventure Home Page NPC item would cease to show because the selected chart area does not include that space. I am looking to be able to have the chart automatically extend the data range so it would show that column in this example. Does this help clarify?

Thanks,
Michael

[teylyn]

Hi,

there are several problems:

The dynamic range names are broken and there are two versions of each range name, one with sheet scope and one with workbook scope.

Delete all range names and create

ChartLabels ='Failed Test Data'!$A$5:INDEX('Failed Test Data'!$A$5:$H$5,,MATCH("zzz",'Failed Test Data'!$A$5:$H$5,1))

ChartData =OFFSET(Hide_Bar_Chart_Columns_Example.xlsx!ChartLabels,1,0)

The second problem is that the chart no longer references the range names, but cell addresses. To fix this, click the chart, then on the Chart Tools - Design - Select Data

Edit Series1 and change the series values to

='Failed Test Data'!ChartData

Edit the Horizontal Axis labels and change the value to

='Failed Test Data'!ChartLabels

Now your chart is dynamic again and will show as many columns as there are values in row 2

cheers

[mcuozzo]

This worked! Thank you very much for your help. I greatly appreciate it.

[teylyn]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save