Bacon Bits:

I recieved an email from Hari, who writes:

“I saw your post about smart scrollbars…how do I make scrollbars accept percentages?”

First of all Hari, may I say you have great taste when it comes to the Blogs you read. Now to answer your question:

Unfortunately, Scroll Bars in Excel only accept integers. That is to say, it can’t pass values like .82.  The trick is to use a simple formula to convert the whole number outputs from the Scroll Bar.

.

Step 1:
Set up your Scroll Bar as normal, setting the max limit to 100.

.

.

Step 2:
Write a formula to convert the Scroll Bar output to a percentile. For example:

=C2/100

.

.

Step 3:
Hide the Scroll Bar’s output cell. In this case, we hid Column C.

.

.

And there it is.

I know…it’s silly that you have to resort to basic math to make Scroll Bars work with Percentages.

Hopefully this will change in a future version of Excel.

OK Hari – I’ve answered your question. Now send me my cookies.

RELATED STUFF

1. Using Arrows to Edit a Named Range
2. Outta My Way Formula Thingy!
3. Find and Highlight Specific Values
4. Creating a Frequency Distribution with a Pivot Table
5. Limiting Movement within Your Worksheet
6. Protect Worksheet Structure with Array Formulas
7. Padding Numbers with Zeros
8. An Easier Way to Remove Formatting
9. Getting Rid of Sticky Click
10. Flipping a Range Upside Down

• Previous Entry: Smart Scroll Bars in Excel
• Next Entry: Bacon Driven Traffic