Skip to content
Home » Learning Area » SlideFab tutorials » Tutorial: How to create multiple heat maps with different conditional formatting in PowerPoint

Tutorial: How to create multiple heat maps with different conditional formatting in PowerPoint

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

  1. 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 the SalesTerritory.Country column within the table called Data.

  2. 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.Creating the list of categories and subcategories which is used for the PowerPoint automation with SlideFab

  3. 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)Defining the master cell for the PowerPoint automation with SlideFab

  4. 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. The TRANSPOSE 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.Splitting the concatenated entry of the master cell into its components

  5. 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, the IFERROR 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.Adding further conditions to the sumifs formula

  6. 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.Creating an Excel formula which is used for the PowerPoint slide title

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

  8. 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.Linking the title cell with the title shape for the PowerPoint automation with SlideFab

  9. 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 master cell for the PowerPoint automation with SlideFab

  10. 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.Setting up the candidate range for the PowerPoint automation with SlideFab

  11. 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.

    Hidden slides will be excluded from the automation

  12. 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.
    To create multiple heat maps the slide making window is the starting point

  13. Checking the output

    The screenshot shows the output for the template slide using shapes. For tables it works also well.Multiple heat maps in PowerPoint were created through the automation