Rollover Effect in Excel

Rollover Effect in Excel! Learn how to create a clean navigation structure using hyperlinks. Build interactive menus for your report! In this tutorial, we’ll display a four-year sales period with a space saving solution. So if we talk about the available space, then this is the time to show you a few tricks!

Think back to the one-page Excel dashboard that we’ve made before! One of the hardest tasks is when we have to display a relatively large amount of information on a given space. Moreover, we have to solve this issue so that the report or chart remains simple and easily understandable. The rollover effect is an impressive data visualization tool in Excel.

rollover effect overview excel 01

Keep the rules of data visualization in mind! Sometimes less is more! After this introduction let’s get to work!

How to create navigation using the rollover effect?

Set up the user-specified values

The starting steps are the usual ones. First, we have to create the data set. The source of the data is the range containing the monthly sales data.

First, create five columns for the months and the years. After this, insert the sixth one too, this will have great importance later.

The last one will be a dynamic range! It will always contain data chosen by the user in a given moment. In other words: The column can be filled out in four ways depending on what data we need between 2015 and 2018.

dynamic range excel rollover 02

Create a Named Range for actual selection

Because we’ll work with the above mentioned fifth column, minds well to give it a name. Highlight cell M5. From the Formulas tab chose the ‘Name manager’ option.

Hint: If we’d like to speed up this procedure than use Ctrl+F3 keyboard shortcut.

name manager setup 03

In the ‘Name Manager’ window click ‘New,’ than browse in the cell, we’d like to name.

named range selection dynamic rollover 04

Click OK; we created a unique name for the cell M5. In our example, we named the cell ‘Selection.’

Check if we executed the operation right. Highlight the given cell and take a look at the “Name Box.” Everything seems to be right; from now on we can refer the M5 cell as “Selection.”

Build a column chart using the dynamic range

Highlight the data range which is the base of our graph. The column named “Selection” will be a dynamic range! Why? Because the user with the help of the rollover hyperlink will fill up the column with always changing values.

After this insert a simple column chart. Use a format that is most fit for the given report! If we think it’s best than change the color of the default chart and the gap between the columns.

format chart 05

Design the navigation area

To understand the operation of the rollover effect, make a report layout! We have mentioned many of times that if we leave out the planning phase, we’ll do double-work later. It takes only a few minutes, worth to spend time on it!

The report area consists of three parts that are well separable from each other. In the top row, there are the sales results. It is not surprising that this is a summary section. There’s not any magic here, with the help of a link we connect the values with the cells of the top row.

For us the second, the mouse rollover area is much more interesting. If we move the mouse over the given cell (in the B4 and E4 range) than the chart will appear corresponds the year chosen by the user. We can achieve the rollover effect that in a relatively small space we can display various values of 4 years.

Beneath the summery and the navigation structure, there is the chart positioned.

rollover effect layout design 06

Implement the Rollover effect in Excel

We will use the HYPERLINK function let’s get to know it shortly. Here’s an example:

=HYPERLINK(FunctionName(),”Click here”).

If the user clicks on this link, a function will execute we define that.

Hint: For us now the mouse hovering is essential and NOT the click. It is a specialty of Excel to execute the little function written by us when we move the mouse over the given range. The rollover effect already in action!

We’ll write a short UFD (user defined function). Please don’t be shy it is only three lines.

Public Function HighlightMyData(seriesName As Range)
Range(“selection”) = seriesName.Value
End Function

Explanation: Let’s see what will this shortcode do! The function only shows the chart based on the actual value of the “Selection” range. The “Selection” here is a variable that can take on the following values: 2015, 2016, 2017 and 2018.

Insert this code with the help of VBA editor. If you can’t see the Developer Tab on the ribbon, use the ALT+F11 shortcut so the proper window will open immediately.

vba editor insert code 07

Combine the HYPERLINK and IFERROR functions

There’s nothing left to do than to write the four HYPERLINK formulas that will create the dynamic charts. At first sight, the formula below may seem a little complicated, but we will explain exactly how it works!

=IFERROR(HYPERLINK(HighLightMyData(C3)),CHAR(113))]

Highlight range C4:F4. Change the default font to Windings 3.

The CHAR(113) formula will display the unique character we need. In our case an arrow pointing downward. The role of the IFERROR() formula is error handling.

Apply the formula as shown in the picture below:

add formula highlight rollover effect 08

Final Thoughts – The Power of Rollover Effect

We can use dynamic charts in many ways. Much time we forget that besides the conditional formatting we have millions of other tools to make a chart interactive. With the help of the HYPERLINK() formula and the rollover technique, we can solve more complicated tasks that we have introduced in our examples.

Implementing the rollover hyperlink effect can decrease the number of charts. So we can take up space at one time and allow users to focus only on the selected period.