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.
Keep the rules of data visualization in mind! Sometimes less is more! After this introduction let’s get to work!
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.
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.
In the ‘Name Manager’ window click ‘New,’ than browse in the cell, we’d like to name.
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.
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.
Implement the Rollover effect in Excel
We will use the HYPERLINK function let’s get to know it shortly. Here’s an example:
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
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.
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!
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:
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.