Clearly and Simply

Category: Visualization

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

  • US Choropleth Map by County per State – a 4th Option

    Another option to display a Choropleth Map of the United States by County for one user-selected state in Microsoft Excel

    The previous post presented three different options to implement a US Choropleth Map by County per State in Microsoft Excel. One approach showed the entire map of the United States and highlighted the selected state (option 2), whereas option 3 filtered the data and only plotted the counties of the selected state.

    It was again Leonid Koyfman (who else?), who provided me with an interesting idea for another alternative: Leonid suggested in this comment to combine options 2 and 3 and show both maps in one view. This requires more real estate on the dashboard, but it also combines the advantages of both approaches: it provides a zoomed map of the selected state and keeps the context of this state on the map of the entire USA.

    Since the technique will certainly be most useful on a dashboard showing more than just the maps, I created an example visualization with two additional views:

    US Choropleth Map by County per State Dashboard - click to enlarge

    • On the left side of the dashboard the two maps (zoomed selected state and USA) are displayed
    • At top right of the visualization a table lists 10 selected counties of the current state: the 10 counties with the highest or lowest unemployment rates. A small new VBA routine assigns the according bin color to each of the 10 counties in the narrow column right to the table
    • The band chart at bottom right visualizes minimum, maximum and (unweighted) average of unemployment rates of all counties in the selected state over time

    This is just one example how the technique of visualizing maps for a user selected state could be integrated in a more complex dashboard.

    If you want to use this on your own dashboard, you may want to play around with the size of the maps and where they are located. Size and position of the zoomed state map are defined by the named range “myMapView”. You can adjust the zoomed map by simply changing the cell reference of this name. As for the map of the USA, you have to group all shapes of the US map first, then resize and reposition this group and finally ungroup it again.

    The major disadvantage remains, though: the zoomed map of the selected state makes the distortions visible. These distortions are inevitable in the process of converting SVG-files to freeform shapes in Excel. Well, at least I couldn’t find a way to avoid them. The problem depends on the size of the selected state: Texas and California, for instance, still look good as a zoomed map. Rhode Island and Connecticut definitely don’t. A crucial downside, no doubt about it. Unfortunately I do not have a solution for this. A map would be needed which will not be distorted in the ungrouping process, but I couldn’t find a better map than the one used here.

    If you are interested, here is the link to download the zipped Excel workbook:

    Download US Choropleth County Map per State (zipped Excel workbook, 2.4 MB)

    Many thanks go to Leonid for the idea.

    Stay tuned.

  • US Choropleth Map by County per State

    How to display a Choropleth Map of the United States by County for one user-selected state

    Choropleth US Map by County per State (Intro)If you are a regular reader of this blog, you certainly know that I am into geographical data visualization. Just click on “Choropleth Maps” in the Categories roll on the right side of the blog and you will see that we already had 18 (!) posts in this category. Some of them discussing maps in Tableau, but most of them dealing with the question how to implement Choropleth Maps or Cartograms in Microsoft Excel.

    I thought I would be through with this topic. I really did. However, I recently noticed that there is one common use case for Choropleth Maps in Excel I never wrote about: visualizing one user-selected region of a bigger map. Imagine you have geographical data with a hierarchy, e.g. USA by state and county (to state the obvious for the majority of my readers). It may well be that one of your dashboards is focused on displaying views for one selected state only.

    Now, what if one of your views shall be a Choropleth Map? How can you show only the selected part of a map on your dashboard?

    Today’s post describes three different options how to do this. As always, the example Excel workbooks are provided for free download without any password protection.

    (more…)

  • Zooming in and out of Excel Charts

    Interactive zooming in and out on Charts in Microsoft Excel

    Zoom in and out on Excel ChartsIf you have an XY scatter chart with a lot of data points in Microsoft Excel or even using an XY scatter chart mimicking a map in Excel, an interactive zooming function (allowing to magnify user-defined parts of the chart) can be very helpful.

    Microsoft Excel does not provide such a feature natively. There have been a few workarounds published. Most of them use interactive controls like spinners or sliders to change the axes scales with OFFSET formulas and dynamic cell ranges or by VBA.

    A more intuitive way of zooming into a chart would be to select an area on the chart with the mouse and zoom exactly into this user-defined part of the chart (or map).

    We recently had a couple of posts taking advantage of Andy Pope’s stellar idea to use a label control on top of a chart for interactive chart features:

    Another Technique for Interactive Excel Charts

    Selecting and Highlighting Areas on Excel Charts

    Select Areas on a USA Map in Microsoft Excel

    Andy’s technique is very versatile and can be a viable solution for many different use cases, including the requirement of an interactive zooming feature.

    Today’s post describes an implementation of an interactive zooming feature for a map (XY scatter chart) in Microsoft Excel. As always, the workbook is provided for free download.

    (more…)

  • Select Areas on a USA Map in Microsoft Excel

    Interactive radial or rectangular area selection on a map of the United States in Microsoft Excel

    One of the recent posts demonstrated how to mimic Tableau’s rectangular and radial map selection features in Microsoft Excel:

    Selecting and Highlighting Areas on Excel Charts

    The example workbook used a map of Europe. Since more than 40% of my readership is located in the United States, I thought it might be a good idea to also share a template with a map of the USA. Here it is for free download:

    Download Select and Highlight Areas on a US Map (zipped Microsoft Excel 2010 – 2013 workbook, 1.3MB)

    This is an example screenshot of the dashboard:

    Interactive Selection on a USA Map in Excel - click to enlargeThe map (the contiguous 48 states, i.e. excluding Alaska and Hawaii) includes all cities in the United States with more than 5,000 inhabitants, divided into 2 categories (greater than or less than 500k inhabitants), visualized with different sizes of the dots.

    To define a radial or rectangular selection (choose with the radio buttons), keep the SHIFT key pressed, click somewhere on the map with the mouse key pressed, move the mouse to span the selection area and – as soon as you are done with your selection – release the mouse key. The metrics above the map and the bar charts (selection statistics by states) right to the map are updated on the fly.

    For more details on how to use the template and how this was implemented in Excel, please have a look at the original post.

    I hope this will be helpful, especially for my friends and readers in the United States.

    Stay tuned.