Clearly and Simply

So long, and thanks for all the fish

Robert — Fri, 29 Aug 2025 22:00:00 +0200

TypePad is shutting down on September 30, 2025 and this also means the end of this blog

Recently, TypePad announced that it will discontinue its services effective as of September 30, 2025. This blog used TypePad as a blogging platform since the blog started in 2009.

Bad news: with TypePad shutting down, this blog will also go away for good.

I admit, I haven’t done much on this blog for a long time (except for one new post some days ago), so this blog was already sleeping for quite a while.

That being said, since 2009, I published a total of 176 articles here, focussing on data visualization, dashboards, techniques and tricks using Microsoft Excel and Tableau. All content was provided for free. No ads. All workbooks for free download. No strings attached.

All this content will be gone soon. So, if you are still interested in surfing through the blog posts and downloading some maybe helpful workbooks, time is running out. As of today, you have a little more than 4 weeks left.

Finally, please bear with me that I can’t acknowledge all the people by name, who helped me during this great journey of blogging. They are just too many.

So, to all of you who contributed guest posts, supported me by reviewing my drafts, linked to my blog on your own sites, mentioned me on LinkedIn or elsewhere, commented or simply read my posts: thank you so much!

So long, and thanks for all the fish.

Select and Highlight across an Excel Dashboard

Robert — Sat, 16 Aug 2025 18:00:00 +0200

Interactive Excel Dashboard visualizing unemployment rates in the EU. Select a country, filter by age group and gender, and see the selection highlighted across all visuals on the dashboard

1,176 words, ~6 minutes read

10 years ago, I published a post demonstrating how to highlight one selected item across all charts and views on an interactive Excel Dashboard:

Highlighting on Excel Dashboards

Unlike most of my other publications here, that post actually did make some friends.

And even more, it won a real fan: my good friend and Microsoft Excel MVP Carlos Barboza (Carlos' LinkedIn Profile, Carlos’ blog: Spilled Graphics) liked this workbook so much that he even included it in some of his presentations, e.g. his great speech on the Global Excel Summit in 2024.

Recently Carlos asked me, if I could provide a version with an updated data source. Of course I can. And while I was at it, I also made a couple of (hopefully helpful) changes.

Today’s article provides this updated version of my European Union Unemployment Rates Dashboard from 2015. It also briefly discusses why interactive dashboards in Excel are still a viable option, even nowadays with Tableau and Power BI.

The Basic Idea

The basic idea of this dashboard is enabling the user to select one item (the member state of the European Union in this example) and highlight this selection across all charts and views on the dashboard. The interactivity allows the selection in different ways by clicking directly into a table, a chart, a map or by using slicers.

Now, applications dedicated to create data visualizations and dashboards, like Tableau or Microsoft Power BI, provide this as built-in features. That being said, you can implement comparable selection and highlighting features in good old Excel, too, provided you are willing to include a little bit of VBA in your model.

What can you do with this dashboard?

The underlying data source contains only one metric: the unemployment rates (percentage of population in the labour force) in the European Union per year since 2010, and by country, age group and gender.

You can easily analyse the data by directly clicking into most of the views on the dashboard to change the current selection or filtering:

Click on one of the row headers of the data table to select a country or on a column header to select a year
Click on on any item of the vertical axis of the bar chart to select a country
Select a country by clicking on it on the map
Filter the data by age group by clicking on the vertical axis of the dumbbell chart
or, use the slicers on the right of the dashboard to select a country, year, age group or gender
finally, you can even pick one of 22 predefined colour schemes to be used in the filled map with the drop down menu at bottom right of

All views on the dashboard will highlight the selected country and filter the relevant views by year, age group and gender.

Here is a small demonstration of the features as a gif:

If you want to play around with the dashboard on your own, you will find a link to the Excel workbook for free download at the end of this post.

What’s new?

Here is what is new compared to the workbook published back in 2015:

First and foremost the data, of course. It now covers the years 2010 through 2024, including more age groups (7 instead of only 2) and also gender. The data was taken from the official EU EuroStat website
Drop-downs (combo boxes) to select a country, year or age group (and now also gender) have been replaced by slicers at the right side of the dashboard. This makes the selection and filtering easier and more intuitive
A dumbbell chart at bottom right shows the distribution of the unemployment rates for all age groups in the selected year: the selected country, the countries with the smallest and highest values and the (unweighted) average
Many formulas have been rewritten to use the modern Excel 365 functions (like SORT, SORTBY, LET, XLOOKUP and others) and dynamic arrays
Finally, some formatting and rearrangements on the dashboard have been made, e.g. showing the current selections / filter at top left, switching the data table and the band/line chart and others

Why Excel?

I assume, some of you are now tempted to post a comment like “Why in the name of God are you still using Excel to create an interactive dashboard? Use Power BI or Tableau, instead. They have all the features built-in you are showing off here.”

And I fully agree. For a data set like the one used in this example, the implementation of an interactive dashboard with similar (or even better) features would be way easier and faster with Power BI or Tableau. No doubt about it. But using this simple data source here was for demonstration purposes only.

That being said, what if your data analysis requirements are beyond aggregation, sorting and filtering? What if you do not simply visualize a data model from an external database or a simple data table? What if you have to perform complex calculations before you can visualize the results on a dashboard?

For instance, some months back, I implemented a comprehensive financial model in the field of IFRS 16. The model forecasts all relevant IFRS 16 measures (lease liabilities, interest, right of uses, depreciations and others) for thousands of planned leasing contracts for the next 30+ years on a monthly basis. Maybe this would have been possible with DAX in Power BI or Calculated Fields in Tableau, too. But the task was tailored for Microsoft Excel and guess what? I implemented it in Excel. And of course, the model also provides interactive dashboards to enable the users to analyse the calculation results directly in Excel.

So, here is my take:

Power BI and Tableau are the best tools to visualize an external data model or table as long as you do not have to perform a lot of complex calculations to get to the results you need
However, if the data to be visualized is the result of sophisticated calculations or even an algorithm, you are definitely better off by taking advantage of Excel’s flexibility and calculation engine. Today’s example shows that you can still provide a highly interactive dashboard inside of your Excel workbook

So, even with all the great built-in features of Power BI and Tableau, interactive Excel dashboards still have a lot of relevant use cases.

The Download Link

Are you interested in having a look for yourself? Download the workbook for free here:

Download EU unemployment rates 2010-2024 (Microsoft Excel 365, 1.02 MB)

Well, I used to close my blog posts with “stay tuned”, but after not publishing anything in 5 years, I probably shouldn’t do this anymore. Maybe there will be some new content here in the next few months, but I am not making promises anymore.

Anyway, thanks for stepping by and reading.

London Excel Meetup Workbooks

Robert — Thu, 03 Sep 2020 22:00:00 +0200

The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020

233 words, ~1 minute read

Earlier today (September 3, 2020), I had the honour and privilege to give a little presentation about Analytical and Interactive Dashboards in Microsoft Excel at Tea Kuseva’s and Alan Murray’s great London Excel Meetup.

For everyone who attended the session and anyone else interested, here are the workbooks used in my presentation for free download.

This is the showcase of the interactive English Premier League Dashboard shown at the beginning of the session:

Download Dashboard Showcase Premier League (zipped Excel workbook, 2.5MB)

And this is the workbook used to present the different tips and tricks:

Download Analytical and Interactive Dashboards (zipped Excel workbook, 2.2MB)

I have to admit, the live presentation and performance was a bit sloppy on my side (my apologies), so I recommend to download and dissect the workbooks to get the most out of the meetup. I think both workbooks are worth a look.

Many thanks go to Tea Kuseva and Alan Murray for organizing the event and having me, to Carlos Barboza for suggesting me as a speaker and of course to everyone who took the time to attend the session.

If you have any feedback or questions, please leave me a comment here or contact me by email.

Stay tuned.

Visual Workbook Navigation with a Chart Carousel

Robert — Sun, 23 Aug 2020 21:00:00 +0200

A Carousel of Charts as a visual and interactive Navigation Control for Microsoft Excel Workbooks

726 words, ~4 minutes read

If your Excel workbook has many output worksheets, you should provide your users with an option to facilitate the navigation within the model.

Sure, the user always has the option to right click on the arrows left to the first tab and select any worksheet from there, but this requires that she/he exactly knows where to find what.

It is best practice to give the users guidance regarding the content of your model and to enable them to easily navigate to the sheets they are most interested in. Usually, you insert an extra worksheet containing a list of all tabs with hyperlinks or buttons to quickly navigate to those sheets.

You are interested in something more visually compelling? If so, you came to the right place. Today’s article provides a visual workbook navigation control with a chart carousel dynamically displaying the content of the relevant sheets. The user can easily browse through all views, select the desired one and get to the view with one mouse click. As always, the post comes with an example workbook for free download.

The Inspiration

A couple of weeks back, my good friend and long-time Microsoft Excel MVP Chandoo published an innovative and interesting post about how to create an interactive chart slider in Excel: How to make an Interactive Chart Slider Thingy

The Idea

I liked Chandoo’s idea a lot and I thought this could also be used for a visual and interactive workbook navigation control. Here is a screenshot of what I have come up with, based on Chandoo’s solution:

On a dedicated navigation worksheet, linked images to 9 different views (worksheets or dashboards) in the workbook are shown as a carousel of thumbnails. The larger thumbnail at the bottom of the carousel serves as the “currently selected view”.

You can

click on the Left Arrow or Right Arrow icons to change this selection to the view at top left or top right of the large thumbnail
click on one of the 8 smaller thumbnails to directly select this view. The arrangement of all views will be adjusted accordingly
click on the large thumbnail to navigate to the worksheet where this view lives

The Implementation

My implementation is a bit more complicated, but still based on Chandoo’s ground work.

Here are the main parts of my solution:

Just like Chandoo, I am using linked images. I have more views (9 instead of 7) and they are arranged as a carousel, but that’s all
The linked images are named “shp01”, shp02”, etc. The larger image at the bottom is the first one (shp01) the others are named clockwise
Names are defined for all ranges of the 9 worksheets where the charts / views live. E.g. the name myView01 refers to ='view 1'!$B$4:$L$17, where [view 1] is the name of the first output sheet
On worksheet [calculations], cell C6 contains the currently selected view, i.e. the one which is shown in the large linked image (shp01):

The small table (range C11:D19) beneath this central model parameter is the heart of the solution. It maps the defined target ranges to the shape names. The formulas are as simple as can be. If you are interested, please have a look at the workbook (download link see below).

Here is a screenshot of the mapping table if the second shape was selected:
The images on the worksheet [navigator] are then linked to 9 additional Named Formulas called myTN01, myTN02, etc. These Named Formulas fetch the target range name from the mapping table and turn it into a range using the function INDIRECT:

Finally, a very simple piece of VBA code is assigned to all shapes on the worksheet [navigator] respectively to the arrow icons. It is really simple VBA code and only 26 lines. If you are interested, please download the workbook and have a look.

Acknowledgement

Many thanks go to my friend Chandoo. You are always thinking out of the box and this makes you a fantastic source of inspiration for all of us. Thank you very much!

Download Link

Here is the Excel workbook for free download:

Download Visual Navigation w/ a Chart Carousel (zipped Excel workbook, 3.8MB)

Stay tuned.

A Neural Network to solve Travelling Salesman Problems in Excel

Robert — Fri, 07 Aug 2020 16:00:00 +0200

Artificial Intelligence in Microsoft Excel: watch a Neural Network solving a Travelling Salesman Problem

869 words, ~4 minutes read

Terms like Artificial Intelligence, Machine Learning, Deep Learning and (Artificial) Neural Networks are all over the place nowadays.

If you are reading Tech News, Data Science blogs or your LinkedIn feed, it will be little short of a miracle, if you don’t see one of those expressions at least once.

This is just the revival of those techniques, though. Neural Networks, for one, have been around for many years. In the mid 1990s (!), I did some research and wrote my thesis about Artificial Neural Networks. We even had a blog post here on this topic 10 years ago: Where the rubber meets the road. For whatever reason, that article didn’t make many friends. I was always wondering why. Probably too academic and not visually appealing enough.

Now, with the recent revival of Artificial Intelligence and Neural Networks, I decided to give it another shot. Today’s post provides an updated, improved version of a Neural Network solving Travelling Salesman Problems in Microsoft Excel.

You always wanted to watch a Neural Network solving an optimization problem? If so, this article is for you. Either watch one of three videos provided in the post or download the Excel workbook and play around with it at your own speed. No add-in or third party software necessary. All you have to do is to enable macros.

The Algorithm

The Neural Network provided today is exactly the same as in the original post (Where the rubber meets the road). It is based on the article “an analogue approach to the travelling salesman problem using an elastic net method”, by Richard Durbin and David Willshaw published in Nature back in 1987.

Without going into the details of the mathematics, here is the basic idea of this approach:

Many other algorithms for solving TSPs start with one feasible solution and try to switch to better ones from iteration to iteration (i.e. shorter total tour length). The approach of Durbin and Willshaw is different: they don’t start with a feasible solution. They start with an elastic net (or adaptive ring), i.e. a circle of nodes where the number of nodes is a multiple of the number of cities of the TSP. This circle is gradually deformed until it finally passes through all cities and describes a feasible and short tour.

You can think of this ring as if it would be a rubber band. In each iteration, the Neural Network randomly selects a city, finds the nearest node on the ring and pulls the node and its neighbours towards this city. If you visualize this behaviour of the algorithm, it looks as if this rubber band is deformed and elongated until a feasible solution is found. The tension of the rubber band guarantees that dragging the rubber band complies with the objective function of the TSP, i.e. to minimize the total tour length.

The Updated Version

As already stated above, the used algorithm is exactly the same as in the original post. However, I made quite a few changes in the workbook to make the handling of the model easier and the views more interesting:

The most obvious change is the map. Instead of showing generic points in the Euclidean space, today’s workbook solves Travelling Salesman Problems through cities of the United States and provides the geospatial context with a US map in the background.

Furthermore, I simplified the dashboard:

Only the most important user-selections are still available on the dashboard and only a selection of results is shown.

With that said, I did not only simplify, I also added a few helpful features:

With the play buttons, you can start, pause, resume and stop the algorithm at any time
With the two checkboxes, you can decide whether or not all cities of the US shall be shown on the map (i.e. not only the ones of the TSP) and whether or not the names of the TSP cities shall be displayed

At the bottom right of the dashboard, four charts visualize some key metrics of the algorithm during execution:

a column chart shows the distribution of the cities, i.e. how often the cities are randomly selected and processed during the algorithm
the three line charts visualize the development of the length of the adaptive ring, the decreasing learn rate and the size of the radius, i.e. the decreasing number of nodes being moved on the ring

Now, let’s see this in action:

Watch examples right here in a video…

Here is a 10 cities TSP. The algorithm is slowed down intentionally by waiting 200 milliseconds after each step to better understand how the Neural Network works:

The next video shows how a 20 cities TSP is solved. No waiting time, but still updating the screen after each iteration:

Finally, a 100 cities TSP. The views on the dashboard are updated every 50^th iteration:

… or download the workbook and have a look for yourself

Not only interested in watching the videos, but also in playing around with the model or even dissecting the code? Here you go:

Download Neural Network Solving TSP (zipped Excel workbook, 663K)

Stay tuned.