Friday Challenge Submissions: Change Chart from Amount to Percent From a Picklist

I received 2 submissions from the Friday challenge.  Both were awesome in that they are really creative ways to handle the question from the user.

The user 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?

Well Peter and Don say YES, YES, YES!

Here is how they did it:

1) Peter created a chart with 2 data series.  One was with percent chart labels and one with amount labels.  This is a really unique and out of the box way to think of the problem.  Good job.  You can check out his file below.  Only issue that I can see is that this solution may not be scalable.  For instance, what if the user had >25 data points in the chart?  This could give you a headache trying to read all the data labels.  But it is a great solution for most applications.  Awesome job Pete!

2) Don created 2 charts.  One based on percentages and one based on amounts.  Then Don used a linked picture to display the chosen chart version that the user wanted to display.  Yet another great way to solve this issue.  This solution has a different scalability problem.  I have found that when you have many linked pictures in an Excel spreadsheet, your performance will suffer and the sheet will calculate often and get very slow.  So if the user chose this as their solution and implemented it for many charts in the same spreadsheet, then the Excel user may run into issues.  But once again, a great way to solve the question at hand.  Amazing work Don!

3) My solution.  I will share this in the next post as I was able to solve this problem with one chart and one series.  So you have to come back and see Smile next post.


Video Tutorial:

Check out the video on the techniques that Pete and Don used here:


Free Sample Excel Download Files

Download Pete’s and Don’s Excel chart files here:

Pete’s Submission: Revenue-or-Percentage-Friday-Challenge-Solution-Peter.xlsx

Don’s Submission: 12_21_13_Challenge_DropDown_Charts_Don.xlsx


If you have figured out another way to solve this issue, leave me a comment below.  In my next post, I will show you how I solved this issue.