Introduction
The previous tutorial followed the idea of creating a heat map in Powerpoint based on Excel. Here, we will think bigger. An iteration loop will apply conditional formatting in PowerPoint to different heat maps, one after the other.
Overview
This tutorial builds upon the previous tutorial. This was about creating a heat map in PowerPoint for some sales data. We depicted margin % by country and month. In this tutorial we will enrich the Excel calculation to take product category and subcategory into account. Accordingly, for every subcategory there will be an own heat map with different numbers and different conditional formatting applied in PowerPoint. The key feature which helps for this is the Iteration Loop, which was also introduced in a previous tutorial.
What is required for this tutorial on How to create multiple heat maps with different conditional formatting in PowerPoint
Of course, SlideFab is required. For this example the free version SlideFab Lite is sufficient for the table-based heat map. The heat maps using multiple shapes instead require a full version of SlideFab.
The PowerPoint template presentation from the previous tutorial is the starting point
In the last tutorial we created two different template slides for the heat map based on conditional formatting. One using a single PowerPoint table and one using shapes.

The Excel workbook with the heat map calculation from the previous tutorial is our foundation
In the previous tutorial we created only one heat map based on the exemplary sales data. It contained margin % for every combination of country and month for the 13 most recent months. The numbers were calculated using SUMIFS
and the colours were applied using conditional formatting.

To set up the Iteration Loop for creating multiple heat maps in PowerPoint with SlideFab, follow these steps.
This tutorial partially repeats steps which were explained in previous tutorials. Consequently, it is a little more concise.
Time needed: 5 minutes
- PART 1: Making the Excel workbook which holds the conditional formatting for PowerPoint more dynamic
An important note upfront: The sales data comes within a table called
Data
. When referencing columns within this table in a formula, Excel puts the column names in brackets. So for example,Data[SalesTerritory.Country]
refers to all data from theSalesTerritory.Country
column within the table calledData
. - Calculating the Candidate Range
The iteration loop is supposed to create one slide for each subcategory. As there might be subcategories from different categories having the same name, a unique identifier will help. The combination of category and subcategory is unique. This formula concatenates category and subcategory with an exclamation mark in the middle to allow for later splitting.
=SORT(UNIQUE(Data[Product.Category]&"!"&Data[Product.Subcategory]))
This will serve as Candidate Range which holds all the entries SlideFab will iterate when creating the slides.
– Open the resulting Excel workbook from the previous tutorial.
– Create the combined category + subcategory list using the formula from above. - Defining and working with the Master Cell
The Master Cell is one cell in Excel into which SlideFab writes the current entry from the Candidate Range while iterating. Formulas in other cells can reference the Master Cell in order to look up further data, for example.
Define one cell as Master Cell and copy and paste one of the concatenated identifiers from the Candidate Range into it. (The latter is not required but it is more convenient than letting the Master Cell empty while creating the workbook logic. Which entry it is does not matter, SlideFab will overwrite it anyway when iterating the Candidate Range) - Adding helping calculations to obtain current category and subcategory
Using the concatenated content in the Master Cell for lookups is cumbersome. So it makes sense to split it into its components, namely category and subcategory with
TEXTSPLIT
. TheTRANSPOSE
function here is used to get the result as rows instead of columns. This is not a must have.=TRANSPOSE(TEXTSPLIT(S4,"!"))
Add this formula to the worksheet to split the Master Cell into category and subcategory. - Enriching the margin calculation formula
With the current category and subcategory calculated, the margin % calculation needs an update. Merely adding the conditions for category and subcategory to the
SUMIFS
functions does the job. The heat map shows the numbers and colours for the current Master Cell entry now.
Not surprisingly not all countries have data for all categories and subcategories. This leads to dividing zero absolute margin by zero sales. To avoid showing an ugly#DIV/0!
error, theIFERROR
function returns an empty text instead.=IFERROR(
SUMIFS(Data[Margin abs],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#,Data[Product.Category],'Export to Table'!S8,Data[Product.Subcategory],'Export to Table'!S9)
/
SUMIFS(Data[Sales Amount],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#,Data[Product.Category],'Export to Table'!S8,Data[Product.Subcategory],'Export to Table'!S9)
,"")
Update the existing formula to calculate margin % per country and month for the current category / subcategory in the Master Cell. - Calculating a dynamic slide title
In contrast to the previous tutorial lots of heat map slides will be created. Consequently, we need to write category and subcategory to the slide title. Fortunately, a simple formula concatenating a fixed title with the information from the Master Cell does this job. Writing category and subcategory into separate shapes in the template slide is also an alternative. But this would not work with 2 shapes per template slide limitation from SlideFab Lite. So putting it in the title makes more sense for this tutorial.
="Margin % by Country over time
" & S8 & ": " &S9
Add this formula to the worksheet to create a dynamic slide title. - PART 2: Adding the new features to the existing PowerPoint template presentation
At this stage it is time to link the PowerPoint presentation with Excel once again. This is done via the button showing “Excel: Closed” in the SlideFab tab in the ribbon, as explained in the previous tutorial.
- Adding the connection between title cell in Excel and title shape Powerpoint
The following three steps are the same for both template slides. For sake of brevity, it is explained only once.
– Select the cell with the title formula in Excel.
– Select the target shape in PowerPoint.
– Click the cross hair icon in the “Excel Source Range” to define the link. SlideFab will write the address of the selected range into the control field. - Setting up the iteration loop: Defining the Master Cell for the current category / subcategory combination
– Select the Master Cell into which SlideFab writes the current category / subcategory during the iteration loop.
– Click the crosshair icon in the “Loop Master Cell” control to define the link. SlideFab will write the address of the selected cell into the control field. - Setting up the iteration loop: Defining the Candidate Range with all category / subcategory combinations
– Select the column with concatenated categories and subcategories which serves as Candidate Range.
– Click the crosshair icon in the “Loop Candidate Range” control to define the link. SlideFab will write the address of the selected cells into the control field. - Excluding hidden slides
From the two template slides (with tables and shapes respectively) it takes only one. To avoid creating the hidden slide, the “Exclude hidden slides” checkbox within the Advanced Settings tab of SlideFab needs to be ticked. - Running the automation and fine-tuning
Click the “Make Slides” button in the SlideFab tab of the Powerpoint ribbon to open up the actual window for this.
When hitting the “Start” button, SlideFab will run the automation.
Creating all these slides takes as a couple of minutes. But the output is perfectly fine. For every category / subcategory combination the slides show the right heat maps. - Checking the output
The screenshot shows the output for the template slide using shapes. For tables it works also well.