Friday Challenge: Can the data in chart change itself to be percentages & number?

Hi Excel Fans!

Today we are going to see if we can solve this question from Ajay.

Ajay wrote:

Can the data in chart change itself to be percentages & number

Can the data in chart change itself to be percentages & number ie, axis changing the format itself. i am trying to present a dynamic chart and trying to show both sales and % sales in same column chart (based on a drop down option)… is this possible?

This is how I read the question:

“I want to have a drop down pick list that will allow a chart to be changed from an amount to a percentage”

Here is my mock up.  I want to change the value in cell F1 from the word “Amount” to the word “Percent” and my chart will go from this:image

to this:image

Is this possible?  Well see if you can do it.  If you can, then let me know how you did it in the comments below.  If you couldn’t do it, let me know what part gave you trouble.  I will post my answer on Monday.

Here your data set.  Copy and paste it into your own spreadsheet and give it a try:

A B C D E F
1 Choose: Percent
2 Revenue %
3 Qtr 1 $ 17,055 11.8%
4 Qtr 2 $ 30,926 21.3%
5 Qtr 3 $ 59,987 41.4%
6 Qtr 4 $ 36,972 25.5%
7 Total $ 144,940 100.0%

Sheet1

Talk to you all on Monday.  Thanks for being a fan!

Steve=True