Clearly and Simply – Page 18 – Intelligent Data Analysis, Modeling, Simulation and Visualization

Clearly and Simply

Interactive Tooltip Examples
3 Examples of how to use an ActiveX Spreadsheet Control as a Tooltip on a Microsoft Excel Worksheet

The recent post described how to use an ActiveX Spreadsheet Control as a tooltip on Excel worksheets. At first sight this seemed to be a nifty little idea to spice up tooltips on Microsoft Excel worksheets. In the meantime, however, I had to learn that there is a major pitfall:

The heart of the solution, the ActiveX Spreadsheet Control is part of the so called Office Web Components. So far, so good. The problem is that Microsoft does not ship the Office Web Components with Office 2007 and later anymore. In other words, in a clean, new Office 2007/2010 installation, the ActiveX Spreadsheet Control is not available anymore.

Microsoft still provides the Office Web Components for free download (Office 2003 Add-in: Office Web Components), but there is still a major drawback: if you want to use the described technique, you have to make sure that every user of your workbook either uses Excel 2003 or has the Office Web Components installed.

At the end of the previous post I announced a follow up article with more practical examples using the technique. Knowing now what I didn’t know before, I pondered if this article would still make sense. To cut a long story short, I finally decided to publish it despite the limitation described above. Maybe some of you will still find it interesting and useful.

Today’s post provides 3 different use cases of how to take advantage of the ActiveX Spreadsheet Control in a tooltip:
- a scrollable tooltip
- side calculations within a tooltip
- an improved version of the Summary Card tooltip
As always the articles provides all Excel workbooks for free download.

(more…)
November 11, 2012
Interactive Tooltips on Excel Worksheets

The Next Level of Tooltips on Excel Worksheets: Taking Advantage of the ActiveX Spreadsheet Control

Tooltips are one of the most helpful features when prospecting large data sets.

We already had a couple of articles talking about tooltips, on charts and on worksheets:

Tooltips on Microsoft Excel Tables

Charts in Excel Table Tooltips

Display all Fields of a Row in large Excel Tables

Better Chart Tooltips with Microsoft Excel 2010

Today’s post is trying to take this to the next level: an interactive tooltip on a Microsoft Excel worksheet. You may ask:

“What the heck is an interactive tooltip?”

What I am thinking of is a tooltip enabling you not only to read additional information but also interact with it. E.g. copy data from the tooltip and paste it somewhere else, do side calculations based on the currently selected data, browse through a longer list of data in the tooltip, etc.

Impossible? No it isn’t. Microsoft provides a very useful ActiveX control which enables us to do exactly this: the Microsoft Office Spreadsheet Control. Today’s post shows how to take advantage of this ActiveX control in order to provide an interactive tooltip on Excel worksheets – as always including the Excel workbook for free download.

(more…)

November 10, 2012
Roll Over Tooltips and Web Actions on a Microsoft Excel Dashboard

Bluffing the “Beer Prices at Oktoberfest” Tableau Dashboard with Microsoft Excel using Roll Over Tooltips and Web Actions

Recently we had an article visualizing the beer prices and beer price development at Oktoberfest with a Tableau dashboard: O'zapft Is!

Oktoberfest is long over already and there is peace in the valley of Munich again. Having said that, I would like to come back to this visualization once more. We had a couple of posts here on Clearly and Simply showing how to bluff Tableau’s great interactive features with Microsoft Excel:

Bluffing Tableau Actions with Microsoft Excel

Bluffing a Visual Cross-tab with Excel

The Next Level of Interactive Microsoft Excel Dashboards

Microsoft Excel Site Catchment Analysis

Drawing on this tradition, today’s article presents an Excel workbook emulating the Tableau Oktoberfest Dashboard. The technique behind this bluff is a very clever approach my blogging colleague Jordan Goldmeier published in several articles over at his Option Explicit VBA Blog.

Stealing Jordan’s idea, today’s post shows how to recreate the Oktoberfest Beer Price Tableau dashboard with Microsoft Excel, as always including the Excel workbook for free download.

(more…)

November 4, 2012
Filter Excel Tables by Double Clicking
How to set the active cell’s value as the filter criteria in Microsoft Excel Tables by double clicking

In a post on the Excel Hero LinkedIn Group Charles asked today:

“[…] I wish to click on a cell with a purchase order number and have that action filter the list based on that purchase order number.
What could be a strategy please? […]”

In one of the answers to Charles’ question, Craig pointed to the most obvious solution: right click and select Filter | Filter By Selected Cell’s Value. Well, I know this is available in Excel 2010, but I can’t tell you no lies: I don’t even remember if this feature was available already in 2003 and earlier.

Anyway. Although at least Excel 2010 provides a built-in feature for filtering by the value of the active cell, Charles' idea is very interesting and reminded me of a post we had here last year: Sort Excel Tables by Double Clicking.

The article provided a small VBA code snippet to sort data tables by double clicking into any given column. The technique used back then for sorting can easily be adjusted to implement Charles’ original idea: filter data tables by the value of the active cell after double clicking and remove the filter criteria with another double click.

The implementation is simple: all you need is to define a name for the range where your data is (e.g. “myData”) and a very small piece of VBA code (24 lines of code) in the event driven Worksheet_BeforeDoubleClick event procedure.

The code
- checks if the the user double clicked inside of the data
- detects the active column
- checks if the auto filter is set and sets it, if not
- checks if a criteria is defined already and if so, removes the criteria or otherwise sets the criteria to the value of active cell
If you are interested in the details, have a look at the workbook provided for download below. If you have any questions, please leave me a comment.

A simple and effective way of setting / removing filter criteria by double clicking. The technique is not limited to one column. You can also select several columns to filter your data.

Here is the workbook for free download:

Download Filter Excel Tables by Double Clicking (Microsoft Excel 2003, 93.5K)

You can even combine the filtering by double clicking with the sorting by double clicking technique, if you change the VBA to sort only if the user double clicks on the header of a column. A simple IF clause and a combination of the two code snippets will do the job.

Stay tuned.
November 2, 2012
Interactive Drop Lines on Microsoft Excel Charts

Display drop lines to both axis of an Excel chart after clicking on a data point

The previous post (Tableau Quick Tip #4 – Drop Lines) discussed a great interactive feature for exploring large data sets on charts: the Drop Line.

In fact I like this feature so much that I am not only using in Tableau. If the data is appropriate, I am also providing a similar feature on my Excel dashboards.

You may ask:

“Interactive Drop Lines in Excel Charts? Does Excel provide such a feature?”

Not built-in, but with a few tweaks and a small piece of VBA code, you can easily get to interactive drop or reference lines in Microsoft Excel, too.

Today’s post shows how to create interactive drop lines on Microsoft Excel charts for different chart types. As always, the article provides the Excel workbooks for free download.

(more…)

October 28, 2012