Change Chart from Amount to Percent using a Drop Down List

In case you were wondering, here is my solution to last Friday’s Excel Challenge.

Change Chart from Amount to Percent from Drop Down List GIF

Our challenge was to see if we could solve this question that an Excel user posted:

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?





Well, we had some great submissions for this challenge.  It was so cool, because none of the submissions were the same and they were completely different than my solution.  So check out their solutions here:

My solution is below.  Maybe you can use these techniques in your next executive Excel Dashboard.  Also, below you will find a free sample download file, video tutorial are step-by-step instructions.

The Breakdown

1) Create Pick List

2) Create Chart Range



3) Create Custom Number Format

4) Create Excel Chart

 

Step-by-Step

1) Create Pick List





First, as the request stated, we need to create a pick list or a drop down list that will change our Excel chart from a percent chart versus an amount chart.  If you don’t know how to do this, you should check out this post that has a step-by-step and video tutorial on this technique:

Creating Pick Lists in Excel for your Dynamic Dashboard

Now, let us assume that our data is in cells A3:C7 like this:

Excel 2012

ABC
1
2Revenue%
3Qtr 1$ 17,05511.8%
4Qtr 2$ 30,92621.3%
5Qtr 3$ 59,98741.4%
6Qtr 4$ 36,97225.5%
7Total$ 144,940100.0%

I want my dropdown list in cell F2.  So select F2 and then go to your Data Ribbon and choose the Data Validation menu and then choose the “Data Validation…” menu option.  Then from the Settings tab on the Data Validation dialog box, choose “List” from the Allow picklist and type in “Amount, Percent” in the Source text box.SNAGHTML2f5782

Your spreadsheet will now look like this:image



 

2) Create Chart Range

Now that we have made our picklist, we need to create a range of data for the chart that we want to create.  It is pretty simple because it is just an IF statement for the chart data that we want to show based on the pick list data.

First, lets create our chart categories in cell E3 by entering in this formula:   =A3

And then copy it down to cells A4:A6.  Your chart data will look categories will now like this:image





Now that you have created your categories, we need to create the chart data.  So select cell F3 and put in this formula:

F3  =IF($F$2=”Amount”,B3,C3)

And then copy it down to the cell range of F4:F6.  Your chart data range will now look like this:image

Now you are almost set to make your Excel chart, but there is one more step before we make our chart.

 



3) Create Custom Number Format

Now before we make our chart, if we perform this step, it will save us tons of time.  This is the key to this demonstration as several other techniques that I and others tried didn’t work (even though I thought they would work).  It is a simple, but very cool step.

The KEY: So as I was thinking about the problem, I thought that this user was plotting a large amount versus a percentage (which is a very small amount).  Most percentages are not that much above 100% which is only a 1 whole numbers.  In fact, most percentages are not above 1000% which is only a whole number of 10.  On the flip side, most of the sales or revenue amount numbers are typically greater than 10 and typically range in the 100’s, thousands, millions and even billions.  So they are very different numbers in terms of scale.  So I decided that our break point was going to be greater or equal to 10 or less than 10 for my solution.  What you may ask is my solution?  Glad you asked.  I had to resort to to an old trick of creating a custom number format that has a conditional element.  You can learn more about this technique on these posts:

How-to Format Chart Axis for Thousands or Millions

Remove The Zero Point or Make a Hole in an Excel Radar Chart

(Don’t forget to watch the video as to why I had to resort to this solution and why others also found it hard to solve this seemingly easy task.)

Okay, to implement this step, highlight your chart data range from F3:F6
image





Now that you have the chart data range selected, go to your Home Ribbon and then select the Number Format picklist in the Number Group.  Then from that menu, select the “More Number Formats…” option. image

Then you will be taken to the “Format Cells” dialog box.  You will want to choose Custom from the Number tab.  Then in the Type field SNAGHTML382e22

you should enter this formula:

[>=10] $#,##0;[>0] 0.0%;0

and then press the OK button.  This will change the number format of your selection to a Percent when the values are less than 10 and change them to an dollar format when the are greater than or equal to 10. Now you are all set and there is just one step to complete the project.



 

4) Create Excel Chart

Now that we have created our custom number format for our chart range, we can create our chart.  And the chart will take the number format that is in the range.  So when you change the picklist to Amount or Percent, the chart will change accordingly.

So highlight E2:F6 image

and then go to the Insert Ribbon and choose Insert 2D Clustered Column Chart from the Chart Group.SNAGHTML664922





And you final chart will look like this:image

and when you change the pick list value from Amount to Percent, your chart will change accordingly:image

Wow, that was easy, but it was my 3rd try at making this chart.  And other rock stars like Don and Pete struggled with it as well.  So it wasn’t as easy as it seemed, but it means that where there is a will, there is most likely a way in Excel.

 

Video Tutorial



Check out the video to see my first solutions that didn’t work as well as the one that did:

 

File Download

Download the free sample file to see how this chart was made.





Amount-and-Percent-in-Same-Chart-from-Drop-Down-List.xlsx

 

Thanks for an awesome year on Excel Dashboard Templates and thanks for being a fan!! If you want to make sure you get the next post delivered directly to your inbox, please subscribe today.

Steve=True





6 COMMENTS

  1. Steve,

    I thought about formatting the numbers, but I decided the limitations were too great. You are right that for this particular set of data it works, because revenues would always be above $10. But what if you were calculating the APR (annual percentage rate) for borrowing a short term loan, given an upfront fee? For example, take a 30 day loan:

    Upfront Fee APY
    $5.00 64.04%
    $6.00 77.66%
    $7.00 91.58%
    $7.50 98.65%

    All of the $ amounts are below $10, so the formatting thinks they are %.

      • My example was just one case. Another would be, if dollar amounts were “Net Income” and not revenue. If the firm losses money in any quarter, the formatting goes away for losses over $100. I’m not trying to “beat a dead horse”, just showing why I decided against formatting. Maybe there is a formatting that gets around this, but I could not figure it out.

LEAVE A REPLY

Please enter your comment!
Please enter your name here