Clearly and Simply

London Excel Meetup Workbooks

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

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.

A Neural Network to solve Travelling Salesman Problems in Excel

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.

Excel Oddity using Named Formulas in Charts

With Named Formulas as the Data Source of a Chart Series, Excel does not always plot the values correctly

959 words, ~5 minutes read

Update on Monday, August 3, 2020: The described issue is solved, thanks to Andy Pope. Please refer to the update at the end of the article or to Andy's message in the comment section. Many thanks, Andy.

The recent articles Dynamic Storyboards in Excel and A practical Example for Dynamic Storyboards described a way how to plot several data series in one XY Scatter Chart by offsetting the data points.

During the implementation of the workbooks coming with these articles, I stumbled across a weird Excel oddity:

If you are trying to transfer the offset calculations into Named Formulas and use these Named Formulas as the source of you chart series, Excel does not always plot the data correctly.

Please be advised that today’s post is only a description of the issue. I have no clue why this is happening and therefore I do not have an explanation of what is going on, let alone a solution.

I have a question for you, though: have you ever encountered this problem? And if so, do you know what the root cause might be and how to overcome the problem? If so, please let me know in the comment section of the post.

Animated Races of the 4 Big Football Leagues

Complementing the previous post: Animated Races of the 4 big Football Leagues in Europe: Premier League, Serie A, La Liga and Bundesliga

297 words, ~1.5 minutes read

The previous article showed an an Alternative to the Bar Chart Race in Microsoft Excel.

As an example, the workbook visualized the results of the English Premier League of this season (2019/2020) in an animated chart.

A few people apparently liked the visualization. I received a couple of emails and LinkedIn messages asking, if I could also provide the same workbook for other football leagues.

No sweat.

Here are the according Excel workbooks for Italy’s Serie A, Spain’s La Liga and the German Bundesliga:

Download Animated Serie A 2019/2020 (zipped Excel workbook, 1.4MB)

Download Animated La Liga 2019/2020 (zipped Excel workbook, 2.6MB)

Download Animated Bundesliga 2019/2020 (zipped Excel workbook, 1.6MB)

And for the sake of completeness, here is the link to the Premier League version again:

Download Animated Premier League 2019/2020 (zipped Excel workbook, 2.8MB)

Please note that

• Except for the Bundesliga, all leagues are still ongoing. The data in the workbook contains only the results up to July 9, 2020. If you want to have a full view of the season, you will have to complete the results on the worksheet [Data] during the upcoming weeks. The fixtures of the remaining matchdays are there already, but the results are missing
• The workbook for Italy’s Serie A contains an inaccuracy. Just like in the other three leagues, teams tied in points are ranked by Goal Difference first and Goals scored second. This is not correct for Italy: in Serie A, the tie breaker are the matches of these two teams (head-to-head records). Thus, the final table shown in the workbook might not be the official / correct one

Stay tuned.