Clearly and Simply

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.