Excel Chart Question - Temporarily Remove Data

[Sparkyweb]

Hi all,

I have often lurked here to find answers and have found it a great resource, but i have a tricky to search for question now, so its my first time posting here!

I have a set of data which results in a simple bar graph. This is based on the values such as the example below:

Company 1 = $4,000,000
Company 2 = $600,000
Company 3 = $500,000
Company 4 = $10,000
Company 5 = $230,000

and so on. For various reasons company 1 will always have the highest figures and of course this is making the bar chart pretty meaningless for all of the other data. Ideally i would like to add a check box onto the graph that i can tick and when it is enabled it will "turn off" company 1 so i can just see the data from the other company's.

Is this possible? Do i make sense?

Many thanks

Mark

 

[Kamal GKJ]

Hi,

We can do this in pivot option is it fine with you.

 

[RoryA]

Welcome to the forum.

You could simply hide that row? By default a chart only plots visible data.

 

[Sparkyweb]

Hi thanks for the prompt responses.
So with either of these options - can i enable them (or turn them "on" or "off") by using a checkbox?

If so how can i do this?

 

[RoryA]

You could use an autofilter, or you could use code to hide, unhide the row. Alternatively, you could link a checkbox to a cell, then have a formula that returns the actual value if the checkbox is checked, or a 0 if it isn't. Plotting that value rather than the original data would then allow you to toggle the large value on/off.

 

[Sparkyweb]

Perfect - Thanks for this.
I did a quick bit of research on hiding a row and now have the ability to toggle the data on and off.

The only way i could seem to do it was to remove the data when the box wasn't checked which is not my preferred route. But i can live with this.