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. I 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.   

The Dashboards and their Features

Peaks of the Alps - Map

This dashboard show the peaks of the Alps on a map:

• Filter the peaks by altitude bins using the option buttons at the top of the dashboard
• The peaks are color coded on the map as shown in the legend beneath the option buttons
• When hovering over the map with the mouse, a tooltip is shown for the peak currently beneath the mouse cursor, including the name and the altitude of the mountain
• Clicking on the map will select a peak
• Alternatively, you can select a peak by entering the name of the mountain in the search box beneath the map view (bluffing a “search as you type” functionality).
• The selected peak is highlighted on the map with a red, larger marker

Peaks of the Alps - Sorted List

The list of the filtered peaks is visualized on a descending sorted, color coded bar chart:

• Just like on the map dashboard, you can filter the peaks by altitude bin with the option buttons
• The bars are color coded by altitude bins
• Scroll through the table using the scroll bar right to bar chart

Peaks of the Alps – Names

The Names dashboard visualizes the occurrences of peak names in the alps:

• Filter the peaks by name count using the "At Most" slider at top left of the dashboard
• The bars are color coded by average altitude
• Scroll through the table using the scroll bar right to the bar chart
• A Word Cloud is the “infographic-type” visualization of the same data: the higher the count, the larger the font size of the name in the cloud
• Hover over the Word Cloud to see tooltips with the peak name and count
• The look and feel of the Word Cloud can be changed on worksheet control, e.g. font type and font color
• Please note that the Word Cloud will only be updated when the slider loses focus, i.e. after leaving the slider by clicking on any cell of the dashboard

The Implementation

I am sorry to say, but I don’t have a snowball’s hope in hell to provide you with a step-by-step tutorial how this was built. This would go far beyond the scope of a blog post.

Thus, I will only briefly describe the main techniques and provide a couple of links to get you started, if you want to dissect the entire workbook or selected techniques.

Peaks of the Alps – Map

• The map view actually consists of three objects:
• An imported static image of the region in the background
• An XY Scatter Chart sitting on top of that image. The axes scales and the size of the plot area are aligned to the map to make sure the peaks (latitude and longitude values) are plotted in the correct position
• Finally an ActiveX Label sitting exactly on top of the plot area of the chart
• 6 different data series are plotted on the chart: one for each altitude bin and one for the selected peak. The altitude bin data series are not static cell ranges, but named formulas returning the according range with the data for each bin. Have a look at the Name Manager (names myChtBin1X, myChtBin1Y, etc.) to see the formulas
• The feature to select by clicking on the map uses the ActiveX Label. VBA code converts the current coordinates into the equivalent data of the chart. In fact, the user never clicks on the chart or map, but on an invisible ActiveX Label. Have a look at the following posts for more details: Another Technique for Interactive Excel Charts and Selecting and Highlighting Areas on Excel Charts
• The tooltips shown when hovering over the map also take advantage of the ActiveX Label. Please refer to this post: Customizable Tooltips on Excel Charts
• The option buttons to filter the data are the usual suspects: form control radio buttons. I think, I don’t have to go into the details
• Finally, the “Search as you type” box at the bottom is an ActiveX textbox. Have a look at this post for more details: Scroll and Search in Excel Dashboard Tables 

Peaks of the Alps – Sorted List

• The view is a combination of a cell range (table) with an aligned bar chart as described e.g. here: Combine Tables and Charts on Excel Dashboards
• The scrollable table is a technique I described back in 2008 (!) in a guest post series over at my friend Chandoo’s blog: Creating KPI Dashboards in Microsoft Excel: You see: I am still eating my own dog food. The only difference: this time I am using an ActiveX Scroll Bar instead of a Form Control. The reason is simple: Form Control Scroll Bars are limited to a maximum of 30,000 and we have more than 36,000 peaks.
• The maximum of the Scroll Bar is adjusted by a small VBA code snippet based on some formulas on the worksheet [calculation]. The sub is called every time an option button is clicked, i.e. every time the filter changed
• The Bar Chart visualizes 7 data series: one for each of the bins, one for the selected peak and one scale dummy series to keep the proportions of the bars when scrolling through the table
• Finally, the option buttons are just a copy of the ones used on the map dashboard

Peaks of the Alps – Names

• The Slider Filter is an ActiveX Control as described here: Range Filter Slider Control in Microsoft Excel
• The left view is again a combination of a table and a Bar Chart including the option to scroll through the list of peaks. The implementation is exactly the same as used on the dashboard Sorted List
• The color coding of the bars by a third dimension (average altitudes instead of count of names) is done by VBA as described here: Color Coded Bar Charts with Microsoft Excel   
• Finally, the Word Cloud is simply using the algorithm provided in the following blog posts: Word Clouds with Microsoft Excel and The Implementation of Word Clouds with Excel 

The Shortcomings

Let’s call a spade a spade: it isn’t really a full replica of the Tableau visualization. No option to zoom into or pan on the map, to switch from the normal map to the satellite view, less options regarding the filtering, no highlighting, and many others.

Besides the fact that it takes some knowledge and effort to set up an Excel workbook like this, the main disadvantage is the fact that the map view requires a 100% zoom factor of the active window (managed by VBA). This could be avoided by interacting with the chart itself (as described in Customizable Tooltips on Excel Charts), but then the user has to activate the chart first to make it work. So, a decision between a rock and a hard stone.

In a nutshell: the Excel replica bluffs only a few selected features of Tableau and it comes with disadvantages. But still, I think it contains some interesting techniques and features, which might be helpful for your visualizations in Excel.

Please let me know, what you think.

Download Link

Download Peaks of the Alps (zipped Microsoft Excel Workbook, 4.3MB)

Stay tuned.

Update Sunday, 10th of May, 2020

This morning, my friend Marcus Small (TheSmallMan) pointed out that the workbook I originally posted did not work for earlier versions of Microsoft Excel. The formulas managing the tooltips returned an error in versions prior to the latest Excel 365 release. Root cause: I did not notice that Excel automatically inserted the SINGLE-function (@ operator in front of e.g. INDEX-functions) and this is apparently not downward compatible.

I updated the workbook now. This should work with earlier versions, too (tested only with Excel 2013, though).Please let me know, if it is still not working for you.

Good catch, Marcus. Many thanks for the heads-up.