Learn how to create a dynamic chart using the hyperlink rollover effect with the mouse hover method in Excel.
Using the hyperlink rollover method, we will get an interactive chart that visualizes a four-year sales period with a space-saving solution. But first, we will build menus for your report. So if we talk about the available space, it is time to show you a few advanced tricks in Excel.
If you check our chart templates, you’ll see that the biggest challenge is to show a relatively large amount of information in a given (sometimes small) space. Moreover, we must show all key information on a single page and keep the result simple and easy to understand. The rollover hyperlink method enables us to build Excel’s lightweight navigation menu structure.
So, keep the main rule of data visualization in mind: less is more! After this small intro, it’s time to start creating the visualization.
How to create a hyperlink rollover chart using the hover effect?
#1 – Set up the table that contains the data
The first step is to prepare the data set. In the example, the table contains monthly sales over four years.
After that, create five columns for the monthly and yearly data. Finally, we need to insert an additional column and store the chart data here. Using this range, Excel will change the main chart source to refresh the chart based on the user’s choice.
Formula to get the matching value based on cell M5:
=INDEX(data,,$M$4)#2 – Create a Named Range for the actual selection
Named ranges play an essential role in Excel. With its help, you can simplify formulas and add descriptive names for ranges. In the example, the cell that contains variables is M5. To create a named range, highlight the cell to which you want to add a name. Next, choose the Formulas tab, then select the Name Manager option.
Note: If you want to speed up this procedure, use Ctrl + F3 keyboard shortcut.
Click OK! Now we have a unique name for cell M5. In the example, we add a name for cell M5, “Selection”. It is easy to check how the named range works. Highlight cell M5, and look at the Name Box. Everything seems right; from now on, we can refer to the M5 cell as “Selection.”
For the sake of simplicity, create another named range, “data”, that refers to I6:L17. Now you can close the Name Manager window.
#3 – Build a line chart using the dynamic range
To create a line chart, we need to select the source data. The source is the dynamic range. Therefore, the “Selection” column will be a dynamic range! With the help of the rollover hyperlink method, Excel will calculate the chart’s data.
Select the Insert tab, and insert a simple line chart.
Note: You can use other chart types. You have to decide which format is most fit for the given report! If we think it’s best, change the default chart’s color and the gap between the columns.
#4 – Design the navigation area for the hyperlink rollover chart
To understand how the rollover hyperlink effect works, make a wireframe! We have often mentioned that if we leave the planning phase, we’ll do double work later. Therefore, creating a sketch can take only a few minutes; worth to spend time on it!
The chart area consists of three parts that are well separable from each other. First, in the top row, there are the sales results that contain the summary section. Again, there’s no magic here; with the help of the hyperlink, we can connect the values with the selected period.
Here is the wireframe of the interactive chart:
The second area is the mouse rollover area, which is much more interesting. If we move the mouse over the given cell (in the B4 and E4 range), the chart will dynamically change to correspond to the selected period. Again, we can implement the rollover effect using a relatively small space. So it is easy to display various charts on a single page.
#5 – Implement the Hyperlink Rollover effect
We will use the HYPERLINK function. Let’s get to know it shortly. This function will define what will happen If the user clicks on this link. Here’s an example:
=HYPERLINK(FunctionName(),”Click here”)Note: Good to know that the mouse hovering is essential in the example and NOT the click. Excel will activate and execute the “HighlightMyData” function we write when we move the mouse over the given range.
The rollover hyperlink effect is already in action! First, we’ll write a short UDF (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 this shortcode will do! The function only shows the chart based on the value of the “Selection” range. The “Selection” variable can take on the following values: 2019, 2020, 2021, and 2022.
Insert this code with the help of the 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.
#6 – Combine the HYPERLINK and IFERROR functions
There’s nothing left to do but write the four HYPERLINK formulas that support dynamic charts. The formula below may seem a little complicated, but we will explain how it works!
=IFERROR(HYPERLINK(HighLightMyData(C3)),CHAR(113))Highlight range C4:F4 and change the default font type to Windings 3. The CHAR(113) formula will display the unique character we need. In our case, an arrow points downward. The role of the IFERROR() formula is error handling.
Apply the formula as shown in the picture below:
The Power of Interactive Charts
You can create dynamic charts in many various ways. However, we often forget that conditional formatting is a Swiss knife if you analyze data. We have millions of other tools and technics to make the presentation interactive.
Using the HYPERLINK() formula combined with the rollover technique enables us to achieve stunning results. In addition, implementing the rollover hyperlink method can decrease the number of charts and provides a clean look. As a result, we can use the dashboard area once and allow users to focus only on the selected period.
Download the practice file and stay tuned.
Additional resources:
