Clearly and Simply

Category: Microsoft Excel

  • Power BI lookalike Tooltips in Microsoft Excel

    How to create Power BI lookalike Tooltips in Microsoft Excel Charts

    1,361 words, ~7 minutes read

    Tooltips are an extremelyPower BI lookalike Tooltips in Microsoft Excel - Intro helpful feature to explore and understand data.

    When hovering over a data point of a chart, a textbox appears and displays the values and – if applicable – even additional information.

    I love working with Microsoft Excel. I truly love the application. But I will make no bones about it: Microsoft Excel’s native chart tooltips are mostly pointless. There is no option to adjust or format them, to link the content to a cell range or the like. They only display default information and this is pretty useless in most cases. All you can do is to turn them off in Excel’s options to get them out of your hair.

    I already posted a few articles about this subject, the first one back in 2010, including the same rant as above: Better Chart Tooltips with Microsoft Excel 2010.

    Microsoft didn’t do anything about it. Excel’s chart tooltips are still as lame as they have been in the past 20 years. This is remarkable, because Microsoft provides much better tooltips in Power BI. So, they are aware that tooltips are helpful, but apparently they don’t see the necessity to let Excel users take advantage of it, too. So we are still on our own here.

    Although I already provided a few options to display better tooltips in Excel (e.g. Customizable Tooltips on Excel Charts), I would like to come back to this topic again.

    Power BI does not only automatically insert tooltips on charts, it also shows a vertical line across the entire plot area and displays the tooltip even if the mouse cursor is not above the plotted data series.

    That intrigued me. Today’s post will provide 2 options how to mimic Power BI lookalike tooltips on Microsoft Excel charts. As always, the post comes with the Excel workbooks for free download.

    (more…)

  • Animated Maps in Microsoft Excel

    How to create an animated Choropleth Map (aka Heat Map, Filled Map, Thematic Map or Statistical Map) in Microsoft Excel

    1.006 words, ~5 minutes read

    In one of the recent articles (All Peaks of the Alps visualized in Excel), I tried to show how to combine different advanced techniques in Microsoft Excel (posted here or elsewhere) to create highly interactive Excel dashboards.

    I will tell you no lies: that one was a washout. The interest in that post and workbook was next to nothing. Now, you certainly expect I would have learned my lesson and try something else. I did learn something, but the stubborn old man I am, I can’t let it go yet. Thus, I decided to give the idea another shot.

    Animated Choropleth or Heat Maps have become quite popular, especially in the past few weeks. So, there is nothing more obvious than providing a template with an animated Choropleth Map in Excel, is it?

    Animated Maps in Microsoft Excel Intro

    Today’s post will briefly explain how to combine two advanced Excel techniques which have already been published here: a Choropleth Map and the animation of visualizations in Excel.

    As always, the article comes with the workbooks for free download: an animated map of Germany by districts (Kreise und kreisfreie Städte) and one of the United States by counties.

    (more…)

  • All Peaks of the Alps visualized in Excel

    Bluffing some of Tableau’s amazing interactive features in Microsoft Excel: an Excel Replica of All Peaks of the Alps visualized with Tableau

    The previous post provided a Tableau visualization of all peaks of the Alps. Peaks of the Alps - IntroI already bluffed some of Tableau’s amazing interactive features in Microsoft Excel and published the solutions here.

    The idea wasn’t mine, though, but Matt Grams’.

    Matt started the journey in 2009 (!) with his beautiful guest article Bluffing a Visual Cross-tab with Excel. I followed a few months later with Bluffing Tableau Actions with Microsoft Excel. Ever since, I posted a few techniques to replicate Tableau’s interactive options in Microsoft Excel.

    Back in 2013, I created an entire Excel replica of one of my Tableau workbooks, which was once selected as the Viz of the Day by Tableau: Bruce Springsteen Discography – A Tableau Infographic. This is the Excel replica: Bruce Springsteen Discography in Excel.

    To carry on this tradition, today’s article provides an Excel replica of All Peaks of the Alps visualized with Tableau. Truth be told, of course not replicating all of Tableau’s features, but still some interesting interactive functionality in Excel beyond the defaults.

    (more…)

  • Optimized Choropleth Maps in Microsoft Excel

    How to create optimized Choropleth Maps in Excel with a higher resolution and without distortions using Excel 365

    Inspired by an idea of my internet friend and highly esteemed colleague Leonid Koyfman, the post US Choropleth Map by County per State – a 4th Option described and provided an Excel workbook with a Choropleth Map of the United States including a second map showing a magnified view of one selected state.

    Optimized Chroropleth Maps in Excel (Intro)The code and technique itself worked fine.

    However, there was one major disadvantage: during the process of creating the map in Excel, the shapes were slightly distorted. The zoomed map of one state still looked good for e.g. Texas or California, but definitely not for smaller states like Rhode Island or Connecticut.

    Today’s post provides a solution to overcome this issue: a way to create a Choropleth Map in Excel without distortions, displaying all counties accurately, no matter at which zoom level. As always, an example workbook is provided for free download.

    (more…)

  • Filter Excel Dashboards with Slicers and Timelines

    How to use Table Slicers and Timelines as interactive Filter Controls on a Microsoft Excel Dashboard

    Filter Excel Dashboards with Slicers and Timelines (Intro)The recent posts

    Filter Excel Dashboards with Table Slicers

    and

    Showcase for Table Slicers on Excel Dashboards

    described a technique how to use Excel’s popular Slicers on tables as easy-to-use, interactive filter controls on a dashboard.

    Although the approach can quickly be implemented and is working fine, it has one major shortcoming: for whatever reason, timelines are only available for Pivot Tables, not for tables. If you have a date dimension in your data (and according to my experiences many data sets do), you can’t let the user filter by dates with a timeline on a table.

    Today’s article will describe a work-around to eliminate this shortcoming. As always, the post provides the example workbook for free download.

    (more…)