When I got bored with the old scroll-bar and their tricks, I thought of inventing a new one on my own. In this post I am going to share everything (including pros and cons), about making a customized scroll-bar in Excel.

Caution : This is a meaty post.

Recommendation : Sit with a latte and enjoy! 😀

So we have here some random yearly data (nothing too significant) on Sheet 1. Our Objective is to make a timeline with customized scroll-bars

### Breaking down the logic of Scroll-bar

In the above illustration note that

1. We have a highlighted year in the center
2. 2 years ahead on the right and 2 years back on the left
3. And the scroll-bars to move the years (ahead or backwards)

On Sheet 2, here is what I do

2. 20 +1 in the next cell and 21 + 1 in the next cell
3. 20-1 in previous cell and 19 – 1 in previous cell

A few things to note

1. In our data we have years from 1975 till 2016 i.e. = 42 years of data. 20 is any random number from 1 to 42.
2. On sheet 2, we are writing numbers (and NOT years) because the data could be anything (text, product code, names etc..) for which you want to make a scroll bar, so we are indexing each data point to a number (starting with 1)

We have also named the middle cell as Highlighted year

### Let’s Create the Scroll-Bars Now

Nothing fancy, but I am going to pick up a simple shape and make it look sexy

1. Pick up the ‘Half Frame’ Shape
2. Draw it while pressing the shift key. Shift key keeps symmetry
3. Reduce the thickness with yellow nods
4. Replicate the shape
5. Rotate them
6. And now you have something like scrollbars ready. We can definitely format them more, but for now they are good to go!

### Linking the Scroll-Bars to our Data

Now that we have our scrollbars ready, our next job is to link them to the numbers in Sheet 2. Before we even do that, let me give you a bigger picture first. Try and understand the snapshot below

1. There are 42 data points (from 1975 to 2016), correct?
2. The highlighted year (as of now 20), can go up to 42 and can go down till 1
• If the highlighted year is 42 then the next two cells should be blank
• If the highlighted year is 1 then the previous two cells should be blank

### Here comes a short Macro

To apply this logic to the Scroll-bars (that we have made), we need 2 tiny bits of code

Macro for Left Scroll-Bar

```Sub custom_scroller_left()
If [highlighted.year] >= 2 Then [highlighted.year] = [highlighted.year] - 1
End Sub```

Marco for Right Scroll-Bar

```Sub custom_scroller_right()
If [highlighted.year] <= 41 Then [highlighted.year] = [highlighted.year] + 1
End Sub```

Copy and paste both these macros in the VBA Window. Follow the steps

1. Go to the Developer Tab (Press Alt + F11 if you cannot find it)
2. Click on Visual Basic
3. From the menu bar click on Insert
4. And then click on Module (Module is the place where we write macros)
5. Module 1 will be inserted. Double click on that
6. And paste both the codes there
7. Close the VBA Window

Now Simply Link the Scroll-bars to the Macros

1. Right Click on the Shapes and go to Assign Macro
2. Link the Shapes to the correct macro

### The Last Part : Getting the years

1. As of now we have got the numbers
2. We have got the scroll bars working

But we don’t have the years displayed yet. Let’s get them too

1. I will apply a simple INDEX formula to pull up years from sheet 1
2. And wrap it around IFERROR function for turning off the errors to 0

And it is done! 😀

1. Although our formatting is lose, which can certainly improve
2. You can also hide the numbers and retain only the years
3. You can now link anything to the years (using Vlookup or Index) and display it on your dashboard. In our case we could have linked Sales numbers

1. The foremost advantage is that it significantly improves the look of your Visualization/Dashboards
2. You can even customize the aesthetics (color, shading etc) of the arrows shapes, which is not possible in the regular scroll bar

1. The biggest disadvantage is that each time you click, it only moves a single year forward/backward. I could not find a way to increment the years automatically in case the button is pressed for a longer time
2. Since this working with Macros, it may seem a little daunting for people

### Take a Look at the use of the Custom Scrollbar

I used this type of a scrollbar in making an Indian Republic Day Visualization. Check it out, it is pretty interesting and you can download it too!