Best practice for mass-producing PowerPoint slides

Service and Training

The following collection of recommendations has been developed for the last years. With the experience from thousands of Excel models and hundreds of PowerPoint to Excel automations using SlideFab, this is distilled knowledge. So it is really best practice. The following collection does not contain basic ideas for beginners. Instead it focusses on people who mastered Excel and who are full of getting explained how a VLOOKUP works. The following list is supposed to grow, so this is a small start for the beginning. In case that you argue with one of the recommendations or suggest another one, feel free to comment at the end of this page.

Best practice 1) Using named ranges and Excel tables instead of cell addresses

When linking PowerPoint shapes with Excel through SlideFab, don’t link using the cell address (e.g. Sheet3!H4). Instead use a named range or an Excel table instead. Among many other benefits which these two options bring (e.g. cf. here or here), there is a clear point to use them with SlideFab: Whenever the worksheet changes structurally, e.g. through new columns or rows, the named range and Excel table keep the right reference. With Excel addresses this is not the case: What was column J might be column K after a new column was inserted on the left of it.

Best practice 2) How to decide whether to use named ranges or Excel tables

When deciding between named ranges and Excel tables, the decision, which to use, is hard sometimes. So, the recommendation is to use Excel tables all the time… But there is no rule without exception. And this is where named ranges will pay off: 1) If there is need for some flexibility regarding cell coverage, a named range based on a formula using might help. 2) If a range containing chart data (also for think-cell charts) needs to be addressed properly, the named range can help. The reason why in most cases Excel tables are superior is that they are faster to use and more transparent as the name manager in Excel is not so user friendly. Not even mentioning the awesome features when using Excel tables for their intended purpose, namely tables.

Cf. article explaining why Excel tables exceed named range when writing legible formulas

Best practice 3) Iteration Loops vs. Slide Loops. What’s the difference and how to decide which to use?

There are two features which allow looped slide creation within SlideFab. 1) is the Iteration Loop feature and 2) is the Slide Loop feature. The difference is that the Iteration Loop takes all slides into regard. This makes sense when mass-producing decks with a couple of slides each. The Slide Loop, however, repeats only one slide. Might look redundant, but it isn’t.

First of all, the Slide Loop feature does not need to know how many slides need to be created as runs from 1 to X where X is a (calculated) number in some cell. The Iteration Loop knows all its candidates which needs to loop upfront.

Secondly, the killer feature is, when both loop functions are combined: Having an overall Iteration Loop for all slides and then for some selected an additional Slide Loop. This makes sense when each of the mass-produced decks contains some data, which might not find on one slide, e.g. tables with too many rows. Then the flexibility of the Slide Loop can create as many additional slides for each deck as individually required.

Cf. example Table Spreading across slides

Best practice 4) Excel file backup approach. What can be done better here?

Many people backup their Excel and PowerPoint files by creating copies and adding some kind of version number at the end, e.g. “my excel file v28.xlsx”. In general, the idea of creating duplicates of an Excel file is great. This helps to keep track and also in cases where the file gets corrupted.

However, this approach is not so helpful with SlideFab. Reason is that SlideFab memorizes the exact file name and when a newly versioned file is used, it has to be selected again.

Instead, the recommendation is to use a file name without versioning, e.g. “my excel file.xlsx”. Backups should be created by creating a separate folder (e.g. “backups”) and copying in the Excel file without overwriting the existing ones. Windows adds a number after the second copy, e.g. “my file name(3).xlsx” which helps to keep track of the versions, in particular with the timestamp of the last changes made.

Best practice 5) How to update Pivot filters in every loop with SlideFab

Sometimes getting certain numbers in Excel gets much easier with a Pivot table. This is typically the case when aggregation functions like sum or count come into play. Whenever the Pivot tables show numbers for the current item (in the Loop Master Cell) only, it gets tricky as Pivot filters are involved. Accordingly, the filter needs to be adapted when SlideFab works on the next. As updating a Pivot filter is not possible with Excel alone, something more powerful is required. This is why SlideFab can execute existing VBA code (aka “macros”). Accordingly, SlideFab makes sure that Pivot filter is correctly set in every loop. To set it up two things are required:

1) A method within Excels VBA editor (Click Alt+F11 in Excel to get there) which updates the Pivot filter:

Sub FilterPivotByCategory()
  ThisWorkbook.Worksheets("Model").PivotTables("PivotSubcategoryCompany").PivotFields("Category").CurrentPage = _
    ThisWorkbook.Worksheets("Model").Range("SelectedCategory").Value
  ThisWorkbook.Application.Calculate
  ThisWorkbook.Application.Calculate
End Sub

2) A call defined at SlideFab which runs this method (to being entered in the VBA window (into the “After” tab) which can be opened through the SlideFab advanced tab).

FilterPivotByCategory

Cf. example Category Tree