Shifting between absolute and percentage in a chart

[Pofski]

Hi,


first time poster here. Let me say first of all that i have learned everything i know about excel from you guys. I started using excel professionally about 2 months ago, and up to that point i only used basic formulas and nothing more.

Things advanced a bit since then


Now for the question.


I have a chart that shows absolute numbers. But i want to be able to switch between absolute numbers and percentage with a checkbox.

This should be shown on a graph.

At this moment i am capable of showing something, but it give absolute numbers on the y axis at the left, and percentage on the y axis at the right of the graph.

I would like to do it so that there is only 1 y axis, and that it switches between the two.


Now, the thing is also that i can't use any macro's. Not that i don't want to, but it is preferred by the company to avoid macro's.


I hope somebody here can help me out.

Apologies for my English, it is not my primary language.


Sincerely

 

[vasim]

In the Search box (above left corner) type in


"Select & Show One Chart from Many"

or

"Use Analytical Charts to Make your Boss Love You"

 

[Pofski]

Thanks a lot.

 

[Hui]

This can be easily done with formulas and no VBA


Have two sets of Data

Have a Check Box linked to a cell

Use the linked Cell values of 0 or 1 to Extract Data set 1 or 2 from the data sets to an Extract Area

Link the chart to the extracted data

Change the Charts Vertical Axis number format to something like:

[<1]0.0%;0;0


If that doesn't make sense see the following:

https://www.dropbox.com/s/w2kle96h4laj7or/Chart%20with%202%20Data%20Types.xlsx

 

[Pofski]

Hui, thanks.


this is fantastic info.

Can i just ask what the [<1] does? This is just so i know what else i can use it for.


Thanks again

 

[Hui]

[<1] forces the Custom Number format to display numbers less than 1 with the format 0.0%

All other numbers will be displayed with the custom number format 0

and zero is displayed as 0