Excel Dashboards can be great for conveying critical summary data regarding the state of investments, an important project, or even an entire business enterprise. Dashboards also pose many challenging problems for developers; one of the more important ones being, how to convey information using a limited amount of monitor real estate. One solution is to provide rollover functionality, that is, changing a single chart to display different data as the view uses his or her mouse to roll over hot text or symbolic icons.
A completed version of the workbook is available here.
Rollover interaction can be implemented by using the HYPERLINK( ) function for a use a bit outside of the conventional. Hyperlinks in an Excel worksheet are generally created using graphical tools, and the HYPERLINK( ) function is used more for specialized functionality. In this example, we will use the HYPERLINK( ) function to invoke a VBA function in response to a mouse rollover.
The technique works by providing a VBA function with a different range depending on the particular cell the user is rolling over with the mouse. The range can be specified either with the A1 style or as a named range. Named ranges are probably clearer and easier to use. In this example, we will start with a table of sales data for Northwind products sorted by category. Each different category of product will be assigned a named range.
We then create a VBA function that accepts as an argument a range object and assigns that range as the data range for a chart.
Function ProductCategory(R As Range) As String
Dim SalesChart As ChartObject
Set SalesChart = ActiveSheet.ChartObjects(“SalesChart”)
For each category, we create a cell with a formula specific for that category. For example, the following formula will create a cell for the Grains and Cereals category of Northwind products.
=IFERROR(HYPERLINK(ProductCategory(Grains),”Grains and Cereals”),”Grains“)
The first Grains in the formula (shown in green) is a named range and is the argument for the function called “ProductCategory”. The function is the first argument for the HYERLINK( ) function and will be invoked when the user rolls the mouse over the cell. “Grains and Cereals” is the optional second argument for HYPERLINK( ), the so-called “friendly name” for the link. In this case, the argument it required if we would like to have text in the cell. (If we are using an icon, this is not necessary. The last “Grains”, shown in red, is only used if an error occurs.
The list of categories is blue only because it was formatted as such using the standard format dialog box. If, during development, a cell becomes formatted in the hyperlink style, such formatting can be cleared and replaced with whatever formatting is desired.
Dashboards attempt to convey critical information at a glance. This goal can be defeated if they are too large or contain too many charts at once. Implementing rollover functionality can reduce the number of charts taking up space at one time and permit users to focus only on the chart of interest by “rolling over” a description or an icon with their mouse.