Us vs. Them – Compare Sales Performance using Charts & Form Controls

Posted on March 12th, 2009 in Charts and Graphs , Featured , Learn Excel - 23 comments

One of the common uses of charts is to compare one value with another. For eg. our sales vs. competitor sales.

Today we will learn a little trick to compare 1 value with another, especially when you have a large set of values to compare. We will learn how to create a chart like this:

Comparing Sales Performance using Charts & Form Controls
Comparing Sales Performance using Charts & Data

Prepare your data

By now you must have realized what the first step for most of our tutorials. It is always prepare your data (or occasionally it is Have you signed up for PHD, but not today). Assuming you have the data in the table format shown to the right, we will also create a 4×2 table to hold the 4 items to be displayed in the chart. First item in the chart is “our sales”, remaining three are competitor figures which will change based on scroll bar position.

We will comeback to how the other three items are computed. So dont worry about them at this point.

Add a Scroll Bar Form Control

Now comes the fun part. Insert a scrollbar control in your workbook. If you are using excel 2003 and earlier, you can find it under view > toolbars > forms and select the scroll bar option and draw it on your worksheet. For excel 2007 you can find it in developer ribbon tab (you need to turn this on first from Office button > Excel Options)

Once you have added a scrollbar control, right click on it and select Format Control option. Here in the “control” tab, adjust the settings of the scrollbar to something like this.
Adding Scrollbar Control to Excel

Make sure you have adjusted the minimum and maximum values based on the amount of data you have. In our case we have 10 values to display and at any point we are displaying 3+1 values, so the maximum is 10+3-1 = 8 (why so? think…)

Also, mention a cell link where the scroll bar selection is updated. We will use this cell (F11) to calculate the 3 values to be displayed.

Now, Write the Formulas to Display 3 Values

This is very simple step, especially if you know excel INDEX formula.

What is Index formula and how it works?

Index() is your way of telling excel to fetch a particular item in a list by its position (unlike vlookup or match which lookup a matching item). For eg. INDEX(list, 10) returns the 10th value in that list. INDEX works with both lists and tables, when used on tables, you should also specify which column you want the value from. For eg. INDEX(table, 5,3) returns the 3rd element in 5th row.

Ok, so how do we use it to fetch values for our “data shown” table?

Simple, we use a formula like =INDEX(names_list,F11) to fetch the first competitor name, =INDEX(sales_list,F11) to fetch first competitor sales figures. For the other two, we can use formula like.. =INDEX(names_list,F11+1), =INDEX(names_list,F11+2)

Finally We Make the Comparison Chart

We will insert a simple column chart based on “data shown” table. Adjust the formatting for first column. And position the scroll bar beneath last 3 columns. And you are good to go.

Download Comparison Chart Template & Play With It

I think this is a good way to bring focus to particular data point, especially if it needs to be compared to 100 other values. If you want to know more about the technique, I encourage you to download the workbook and play with it: comparison chart template

Learn Few More Techniques Before Calling it a Day

who says you have to learn only one thing a day? So, learn how to display one chart from many, prepare a matrix chart instead of data tables or make an incell bullet chart

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

23 Responses to “Us vs. Them – Compare Sales Performance using Charts & Form Controls”

  1. Another simple yet very effective use of Excel. Always impressed by the practicality and elegant simplicity of your suggestions.

  2. Jon Peltier says:

    “Prepare your data”
     
    or “PREPARE YOUR DATA”

    It’s a “Pay me now or pay me later” kind of thing. I tell people they can spend five minutes on their data, and save five hours of frustration later on.

  3. Chandoo says:

    @Bernard: You are welcome…

    @Jon: exactly… preparing data is an important step for any type of analysis or visualization.

  4. […] was reading Chandoo’s post Us vs. Them – Compare Sales Performance using Charts & Form Controls, and the first step in his protocol was “Prepare your data”. I’m always telling […]

  5. Jialin says:

    Great tips. Thanks.

    How can we put this dynamic chart in powerpoint?

  6. Werner says:

    first of all – great site!! Has helped me so much in many ways!

    I have a question if I may:

    I have made made charts containing form controls (scrollbars) and would like to show these charts by selecting them from a listbox (like in a previous post http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/)

    would this be possible perhaps?

  7. Chandoo says:

    @Werner: hmm.. very good question.

    First, the technique mentioned in http://chandoo.org/wp/2008/11/05/select-show-one-chart-from-many/ is more complicated and wouldn’t work with form control / active-x control based charts.

    You should try this one instead: http://chandoo.org/wp/2009/05/19/dynamic-charts-in-excel/

    Now, Form controls cannot be moved and sized with cells. but active-x controls can be moved and sized with cells. So, instead of form control scroll bar, try the active-x scroll bar and connect it to a cell. Then, use the above data filter based dynamic chart technique to show / hide the charts.

    Good luck.

    PS: you might as well write a macro based thing for all this. It is more clean and easy for the users.

  8. Jason says:

    Very nice chart!!!

  9. Sherrie says:

    I am so happy to find this website, your templates and ideas are wonderful!!! I have used your idea on using checkboxes to display my charts at work and my boss loved them! Thank you so much for sharing this!

  10. Donna says:

    I have put this into my Dashboards, but all of a sudden, my graph does not update when you move the scroll along, but the data does.

    Anyone know how i can get the graph to update again??

    cheers

    Donna

  11. Donna says:

    Hi Hui, thanks for getting back to me,

    I have followed the above instructions, in which it all worked fine. But even when i download the example given on this website the scroll button doesn’t work either,

    I am wondering if there is something that has been disabled on my PC, which is restricting this function from working?

    any ideas, what this could be?

    i could post my example on here but i can’t find an upload button!?!?

    Cheers

    Donna

  12. Jenica says:

    Really helpful… thanks

  13. Sid says:

    U rock man ! coll stuff..

  14. Carlos G says:

    Excellent graphic, as you are always great

Leave a Reply