Clearly and Simply

  • Interactive Tooltips on Excel Worksheets

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

    Marchant mechanical calculating machine - Photographer: Ian's Shutter Habit (flickr.com)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…)

  • 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

    Oktoberfest Impressionen - Photographer: sanfamedia (flickr.com)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…)

  • 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

    Filter - Photographer jercraigs (flickr.com)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.

  • Interactive Drop Lines on Microsoft Excel Charts

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

    Ruler - Photographer Auntie P (flickr.com)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…)

  • Tableau Quick Tip #4 – Drop Lines

    Call out positions of selected data points in your Tableau view using Drop Lines

    Ruler Macro - Photographer 2nd_Order_Effect (flickr.com)

    I am barely
    using gridlines on my charts. In fact I didn’t even know that Tableau Software has an option to show and format gridlines. Hence I
    started the original introduction to this post as follows:

    Unlike Microsoft
    Excel,     Tableau Software does not provide an option to display gridlines on
    charts. Tableau allows you to define so called row and column dividers, but
    only for categorical data, i.e. dimensions.

    This statement
    is totally wrong: Tableau offers gridlines (Format | Lines | Grid Lines) and
    Rich was kind enough to correct this in the first comment to this post. Thanks
    Rich. My fault. I apologize for the confusion.

    But still:
    gridlines are very often nothing else than chart junk as Stephen Few points out
    in this excellent article: Grid Lines in Graphs are Rarely
    Useful    . Tableau has something way
    more useful than gridlines: the interactive Drop Line.

    Today’s short Tableau Quick Tip #4 introduces this extremely helpful interactive feature of Tableau.

    (more…)