Have you ever come across the problem that there are too many sheets you would like to squeeze into a dashboard which makes it almost impossible to see clearly or organize them in a logical way. Here are a simple tip to solve the problem which can keep the dashboard focused on the high level details while still keep the possibilities to breakdown into more details: embedding worksheets into tooltips.

So let’s try to make it with the sample superstore data. Our goal is to make a map with the total sales of each state, and insert the sheet with the total sales and profit breakdown by sub-category for the specific state as tooltip to show when hovering on the state on the map. Here are the simple steps to follow to achieve this:

1. Make the main worksheet (map view) and the tooltip sheet with the sub-category breakdowns to get it ready to insert. For the tooltip sheet, change the view to “Entire View” that is more friendly to format later when being inserted.
2. Go back to the main worksheet (map view), and press “Tooltips” on the Mark Pane. Form the text and variables in the tooltip that you would like to show first, and then click “Insert”, select “sheets” and select the detail sheet that you would like it to show. Press “OK”
3. Now when you hover on the map, the sub-category breakdown details will show up as the tooltip. But you might come across this message indicating that the tooltip sheet is too large to view and the tool tip sheet is truncated. In that case, go back to the tooltip and change the Max Width and Max Height value to make the tool tip big enough to be fit in.                              

Now when you hover on the States on the map, the breakdown of the Sales and Profit sheets will show as a tool tip.

After struggling with too many worksheets in one dashboard which makes it too busy and hard to follow due to audience easily distracted by other details, I find this way of organising worksheets really efficient as it keeps all the details available yet only show up when necessary. To achieve the purpose, we also need to think carefully about which worksheets to put in the tooltip as what’s in the tooltip would lose the compacity to clearly be compared across two different groups.

The Data School
Author: The Data School