Tutorials / Examples

When it comes to programming most coders or programmers accept that code and data need to be separated. “Avoiding hard-coding” is a common best practice in this regard. Unfortunately, when it comes to Excel this idea hasn’t prevailed yet. Even when it comes to VBA coding within Excel many developers still rely on intermingling code into huge XLSB files, wondering why performance and maintainability are so poor. However, this post is not about writing VBA add-ins for Excel (using XLAM files). Find my opinion regarding using VBA for Excel to PowerPoint automation over here.

This post is about keeping data and calculation logic (instead of code) separate within mere Excel files. Basically an outrageous idea, as “dragging formulas” and “adapting ranges” are instantaneous associations for most Excel users once input data changes. I will explain how Dynamic Array Formulas will help to build highly robust Excel models which will adapt to changes in the raw data automatically.

Accordingly, the concepts provided here are ideal for Excel calculations that need to last, like survey evaluations or corporate reporting. Both have in common that data structure remains rather similar while new surveys or new months are just around the next corner, respectively.

Just a heads up: There is a tutorials section here, which also serves as a good starting point when creating automations with SlideFab.

Disclaimer: What you will find here is my personal opinion based on more than 10 years of Excel and VBA experience. You might have a different opinion. But maybe you will learning something new nevertheless 😉

The art of creating PowerPoint presentations is topic of a whole industry: Templates, tutorials, manuals, best practices, etc. are offered to enable users to sculpture one perfect presentation. However, so much effort for one presentation does not scale well, e.g. when each country, business unit, team leader, etc needs a dedicated presentation. In such cases mass creating slides and presentations can help: Same structure but different data. In most cases the outcome is not very emotional or thrilling as titles or annotations do not reflect the message conveyed through the data. The slides tell no story. In fact, one might argue that a phonebook is more exciting. Unfortunately, the situation is as follows: One gets either customization or economies of scale.

However, this article is about approaches to bring more customization to mass-produced slides. Even though these tipps are meant for SlideFab, some will find them helpful for manual automation coding with VBA or Excel modeling in general.  So, here are some ideas which explain how to get more customization when mass producing PowerPoint slides or presentation based on Excel.

There are plenty of ways to visualize results from surveys and benchmarks. A classical one is a line plot of the Likert scale (i.e. Likert chart / plot). While it is a great chart type, it is a pity that Excel does not offer it out of the box.

There are manuals on the internet available on how to build a Likert chart with Excel. However, this post explains how to mass create multiple slides with Likert Charts with SlideFab for free. This can be useful in benchmarking surveys for example, where each participant shall receive a personalized result presentation.

UPDATE: There was a bug in the Excel model which led to incorrectly drawn charts. Thanks to Thomas Seitz from http://www.tsm-marktforschung.de/ for pointing this out. The example files was updated, cf. below.

UPDATE 2: There is a new tutorial available which uses table stretching to automatically align the chart with the table.

Creating legible Excel formulas is something which is very handy, typically. No matter whether another person or oneself needs to adopt and understand an Excel file. It can be difficult to find into the logic of an old or alien Excel file: Then even plain calculations can be hard to grasp. But there are also cases where complex workbooks are easy to understand. It depends, as always:

Imagine a classical way to make an Excel file hard to understand: Adding numerous references across worksheets to single cells. This happens quite often. Even to adapt Excel users: For example, they separate calculation sheets from a parameter sheet. Conceptually, this is a good idea, but things can get messy.