Adding Forms Scrollbars to your Excel Charts and Spreadsheets

One exercise that is done with almost religious fervor in all companies at the beginning of each financial year is the preparation of the budgets and targets for the next year. Every year a fresh faced newly recruited manager is identified, handled this dreaded responsibility and asked to present the first draft to the management team. And so the rookie spends a couple of nights pouring over multiple spreadsheets and a plethora of for complicated formulae and comes out with, or atleast what he hopes, a really nice dashboard for presenting the next year’s likely projections. Exhausted after his efforts, yet alert lest he make some silly mistake, he stands before a large group of senior managers and begins his presentation. Halfway through, after having repealed a battery of pointed questions, in a fleeting moment he allows himself to feel that he has done a good job so far. And then the Vice President says, “Ok, That’s good. Now let’s look at what happens if we raise our sales by 25% and decrease overheads by 15%. How’s that going to impact the bottomline……….”

Scenario A: The rookie runs a cold sweat and blubbers something about having to go back and redo the numbers and send the revised draft to the entire team later on.

Scenario B: The rookie turns to the chart, smartly adjusts the figures on the sliders, hiking sales by 25% and at the same time decreasing overheads by 15% and says, “Glad that you asked that question. I think what my analysis tells us is that out profit after tax is going to look significantly better at ……”

The moral of the story, you can add a very strong what-if scenario builder in your excel projections by using a scroll bar. With a little bit of a tinkering around, you’ll end up with a set of figures and charts that can tell you precisely what the end result will be after modifying all the input variables.

Adding scrollbar from the Forms Toolbar in Excel

1. Prepare a chart

2. Select a chart and click on the View menu. Click Toolbars and select Forms.

3. In the floating forms toolbar, select the scroll bar icon.

4. Place a scrollbar next to the first series in the chart.

5. Adjust the scrollbar height

6. Double click the scrollbar and link it a particular cell. Normally you’d want the linked cell to be right next to the actual cell whose value is plotted in the chart.

7. Edit the cell whose is represented on the chart so that it changes dynamically as the scrollbar is moved up and down. As in the attached example, you can try out other approaches too.

8. Set the max and min values for the scrollbar

9. Repeat steps 4 to 8 for all the series in the chart.

10. Feel happy.

Here are two alternative approaches:

Placing the Scrollbars right inside the chart

1. Makes all the bars in the chart move real time
2. Move along well when you scale the chart up or down

1. Difficult to maintain scrollbar’s relative position to axis when you zoom in and out.
2. May hide values if you happen to have turned on the display of values in the chart

Placing the Scrollbars right next to the data

1. Easy to maintain scrollbar’s relative position
2. Can be placed irrespective to the charts location so anybody can just look at data and get an idea of the impact. Does not interfere with any of the chart display items.

1. The chart does not respond real time but only after mouse release.

And here’s the attachment with two alternative approaches to using the Forms Toolbar to add Scrollbars to your dashboards and excel spreadsheets for presenting a what-if analysis.

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. How to Create a Dashboard in Excel | da TaB is On wrote:

    […] can add VBA controls to your excel sheets. Turn on the Forms toolbar by clicking the ‘View’ -> ‘Toolbar’ -> ‘Forms’ in the menu. Once you have it turned on, […]


Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel