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.

]]>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 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

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

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.

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!

Here is the Excel workbook for free download:

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

Stay tuned.

]]>*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 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.

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:

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:

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.

]]>*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.

In order to facilitate the explanations in this post, I will use a pointless, but more illustrative and easy-to-follow data set: I created coordinates to show the digits 1 to 9 when plotted in an XY Scatter Chart:

Each digit has coordinates between 1 and 45 on the horizontal and the vertical axis.

If you plot the data as it is, all 9 digits will be displayed in the same range sitting on top of each other. But this is not what we want. What we want to see is a display showing 1 at top left and 9 at bottom right, like this:

The challenge is exactly the same as in Dynamic Storyboards in Excel.

To get there, we need to add appropriate offsets to the coordinates according to the image number. It is a pretty simple calculation: for image 1, for instance, there is no offset added to the horizontal coordinate [X], but an offset of 90 is added to the vertical coordinate [Y]. Image 3 gets a horizontal offset of 90 and a vertical offset of 90. image 7 gets no offset, neither horizontally nor vertically. You get the idea.

First approach is to add calculated columns to the data table like this:

Let’s start with the usual approach: directly link the data series to the calculated columns in the table:

This works as expected and we get this chart:

The offset calculations work and the digits are displayed in correct order from top left to bottom right.

Now, since we need the calculations for this chart only, we might want to get rid of the calculated columns in the data table by replacing them with Named Formulas.

Let’s start with simple Named Formulas, myXPlotSimple and myYPlotSimple, to replace the columns [XPlot] and [YPlot] in the data table.

Here are the definitions in the Name Manager:

Just like in the calculated columns of the table, the values in column [XOffset] are added to the values in column [X] and [YOffset] is added to [Y].

Next step is to replace the data ranges in the data source of the chart series:

Next, we also transfer the calculated columns [X Offset] and [Y Offset] into Named Formulas called myXPlotFull and myYPlotFull as shown here:

The formula refers to the value in the column [X] of the data and adds the result of exactly the same INDEX-function we used in the calculated column [X Offset] of the table. The results of the formula should be equal to the results in column [X Plot]. We do the same for myYPlotFull.

Once again, we use these Named Ranges as the source of the chart data series:

Surprisingly enough, this does not work anymore:

All data points are plotted, but somehow all calculated offsets seem to be not working correctly and all 9 digits are shown at top left of the chart stacked on top of each other.

The interesting thing about this oddity is the fact that all images get the offset of the first image (XOffset = 0, YOffset = 90). If you sort the data table by [Image] descending (i.e. image 9 is the first image in the data table), all points are plotted at bottom right of the table.

So, what is the problem here? To find out, I inserted a check sheet:

On this simple worksheet, the results calculated in the data table ([XPlot] and [YPlot]) are compared to the multi-cell array-entered Named Formulas myXPlot Full and my myYPlotFull.

As you can see in the check columns right, there is no difference, i.e. the Named Formulas return exactly the same results as the calculations in the table.

If we use the ranges where we just array-entered the Named Formulas as the source of the chart series, the chart works perfectly fine. But if you use the Named Formulas, it doesn't.

Excel calculates the values accurately, but it does not plot them correctly.

I am not sure if anyone will be interested, but just in case, here is the workbook for free download:

Download Named Formula Data Source Oddity (zipped Excel workbook, 731K)

Any ideas why this is happening? Have you encountered the same or a similar problem before? Or did I miss something and there is an error in my approach?

Let me know what you think in the comment section below.

Stay tuned.

I published a link to this post on LinkedIn and reached out to the real experts on Excel charts. Wouldn’t you know it. The brilliant Andy Pope provided a perfect explanation and solution within a couple of minutes (see also the comment section):

*"The INDEX function does not return an array, so only the single values for XOFFSET (Calculation!E13) and YOFFSET (Calculation!F13) are used. If you change those values all the plots will have the relevant XY start point. To get the INDEX to return an array use this trick:*

*myXPlotFull: =tab_data[X]+INDEX(Calculation!$E$13:$E$21,N(IF({1},tab_data[Image])))*

*myYPlotFull: =tab_data[Y]+INDEX(Calculation!$F$13:$F$21,N(IF({1},tab_data[Image])))"*

Many thanks Andy, this is very impressive.

]]>*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.

]]>