Clearly and Simply

Category: Charts

  • 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…)

  • Weighted Route Maps in Excel

    How to visualize round trips on a Route Map in Excel, including an indication of the quantities transported between the cities

    Weighted Route MapThe recent post series “Geographical Flow Maps in Excel” (part 1, part 2, part 3) described how to visualize flows from one selected city to several target locations, showing the connections between origin and targets and indicating the flow quantity by the thickness of the lines.

    My highly esteemed colleague Joachim Schirra, outstanding SAP and Business Intelligence expert, read the articles and came up with the following question in a comment on LinkedIn:

    “Would it be possible to show a flow with branches, junctions and return flows, too?”

    Although this is a great idea, it sounds easier than it is. Truth be told, I do not have a solution. At least not yet.

    However, Joachim’s comment made me think. What can easily be achieved is the visualization of a weighted Route Map (i.e. a round trip, see image above).

    Today’s post describes how to change and enhance the Geographical Flow Map approach to get to such a Route Map. As always, the post includes the Excel workbook for free download.

    (more…)

  • Geographical Flow Maps in Excel (Part 3 of 3)

    Final part of a 3 parts series on how to create a Geographical Flow Map in Microsoft Excel

    Flow and Choropleth Map in ExcelThis is the third and final part of our series on how to create a Geographical Flow Map in Microsoft Excel and it will be a relatively short article (at least by my standards).

    Why? Because the technique of how to visualize flows on a map in Excel was already covered in part 1  and part 2. Today’s post will simply enhance that visualization with a technique which has been covered in many blog posts here throughout the last 10 years: adding a so called Choropleth or Filled Map. Check out the category Choropleth Maps to see what has already been published about this topic on this blog.

    To cut a long story short, this article will only briefly explain the idea and how to configure the colors used on the Flow and the Choropleth Map. As usual, the example Excel workbook is provided for free download.

    (more…)

  • Geographical Flow Maps in Excel (Part 2 of 3)

    Part 2 of a 3 parts series on how to create a Geographical Flow Map in Microsoft Excel

    Flow Map IntroWhilst the workbook and explanations provided in the first post laid the foundation for a Geographical Flow Map in Excel, the visualization was very limited in its usability. It only visualized the flow or movement from one city to several endpoints, but not any measure or value which is moved like shipped quantities, USD, persons, or something similar.

    Today’s post removes this limitation by visualizing a measure by the thickness of the lines between the cities: the thicker the line, the larger the measure and vice versa.

    As always, the post comes with the example Excel workbook for free download.

    (more…)

  • Geographical Flow Maps in Excel (Part 1 of 3)

    Part 1 of a 3 parts series on how to create a Geographical Flow Map in Microsoft Excel

    Geographical Flow Maps in Excel - IntroA Geographical Flow Map visualizes the physical route or flow of material, people, money, information, etc. from a starting point to several endpoints.

    Flow Maps do certainly not belong to the visualization types in daily use. But it is definitely helpful to have such a template in your toolbox in case you may need it one day.

    Today’s article is the first of a 3 post series about how to create Geographical Flow Maps in Excel.

    This first part lays the foundation with a workbook setting up the data, the calculations and the chart by using standard Excel features only, i.e. no VBA. As always, all three posts will come with the according Excel workbook for free download.

    (more…)