Skip to content
Home » Learning Area » SlideFab tutorials » Tutorial: How to create a heat map in PowerPoint automatically

Tutorial: How to create a heat map in PowerPoint automatically

Introduction

A data table is often rather sober, when it is just black on white. To convey additional information colouring does help. This is then called heat map. This tutorial is on how to create a heat map in automatically. The idea is to apply conditional format from Excel to a table in PowerPoint.

Overview

This tutorial starts with sales data obtained from the AdventureWorks fictional database. First of all, we will prepare the heat map in Excel. Secondly, we will create a PowerPoint template slide for this heat map. Finally, we will use SlideFab to connect Excel with PowerPoint and then create the heat map on the slide programmatically. This tutorial will show two different approaches for this. One uses a table as target in PowerPoint while the other uses separate shapes as table cells.

This tutorial does not contain any multiplication of slides using Iteration Loop or Slide Loop. This was left out on purpose to avoid making this tutorial too large.

What is required for this tutorial on How to create a heat map in PowerPoint automatically

Of course, SlideFab is required. For this example the free version SlideFab Lite is sufficient only for the first approach. Namely the one using a table. The approach using (lots of) shapes instead requires a full version of SlideFab.

A PowerPoint presentation serves as template

This tutorial will start with a rather empty PowerPoint presentation containing just one slide. The preparation steps will be shown later on.

An Excel workbook will be connected with the PowerPoint template presentation

The Excel workbook used for this tutorial is lean. It contains only one worksheet with a data table. This data is about sales by category and country over time.

To create the heat map in PowerPoint automatically with SlideFab, follow these steps.

This tutorial involves a larger number of steps. However, most of these steps are related to creating the Excel workbook and the PowerPoint template. SlideFab comes into play towards the end.

Time needed: 30 minutes

  1. PART 1: Creating the Excel workbook with the heat map calculation and colouring

    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. Obtaining the country list

    – Open the Excel workbook.
    – Create a new worksheet.
    – Add the country list using the formula below.

    =SORT(UNIQUE(Data[SalesTerritory.Country]))Getting a sorted unique list of countries serving as row header for the heat map

  3. Obtaining the months list

    The column header shall contain the most recent 13 months.

    To collect the data UNIQUE and SORT help to retrieve the date entries from the Order Date.MonthKey column in a descending way (newest first). The SEQUENCE function creates a list with the numbers from 1 through 13. Then INDEX gets all date entries from all positions of the 1,..,13 list. However, for the heat map the oldest month shall come first, so the other SORT changes the order to ascending.

    =SORT(INDEX(UNIQUE(SORT(Data[Order Date.MonthKey],,-1)), SEQUENCE(1,13)),,,TRUE)

    Add this formula to the worksheet to collect the date entries.Getting the 13 most recent months as preparation step for the column headers for the heat map

  4. Formatting the months list for proper outputting

    With a little bit of formatting these date entries look much better. This formula uses DATE to create a real date. As the actual day does not matter, a 1 is merely used. Then TEXT formats this date as MMM YY, for example Jan 25.

    =TEXT(DATE(LEFT(C2#,4),RIGHT(C2#,2),1),"MMM YY")

    Add this formula to the worksheet to format the date entries.Adding the header row in proper format for the heat map

  5. Calculating the margin %

    With column and row headers in place, the actual data gets into the focus. The table shall show the margin in percent. So it is the margin in absolute terms divided by total sales. As the table does this for each country and month, the SUMIFS functions does the job.

    =
    SUMIFS(Data[Margin abs],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#)
    /
    SUMIFS(Data[Sales Amount],Data[SalesTerritory.Country],'Export to Table'!B4#,Data[Order Date.MonthKey],'Export to Table'!C2#)

    Add this formula to the worksheet to calculate margin % per country and month.Adding a formula to calculate the margin in % which is used for the heat map

  6. Formatting margin %

    – Select the entire range with the margin % results.
    – Format the entries as percentagesSetting the number format

  7. Creating the conditional formats for heat map colouring

    So far the calculation for the heat map is complete. What is missing is some heat. Conditional formats will apply the colors. In this example only one rule with a 3-color scale is enough. But in general, this also works with multiple rules.

    – Select the entire area with the margin % results.
    – Add a new rule for conditional formats.Add conditional format rules for heat map colours

  8. Setting up a conditional format rule to the specify the exact heat map colouring

    – Choose the 3-color scale with in the type “Format all cells based on their values”.
    – Set reasonable thresholds.For the heat map, conditional format settings are made

  9. So far so good. The Excel workbook is ready with a working heat map.

    The heat map prepared in Excel is ready to be brought to PowerPoint

  10. PART 2: Using a table on a PowerPoint slide for the heat map

    Within this section there is a PowerPoint table serving as target for the heat map. This is easy to use and also quite flexible.

  11. Setting up the template slide

    For this example having a static title is alright

    Add a title for the template slideAdding a title to the template slide

  12. Add a table to the template slide with 14 columns (for country name + 13 months) and 2 rows (1 for header and 1 for data).
    If there is more data than rows, then SlideFab will replicate the last row to make it fit.

    Creating a target table in PowerPoint which is then used for the heat map automation from SlideFab

  13. Format the target table for the heat map

    – Add some dummy text to the cells of the table to make formatting easier.
    – Format table as desired, e.g. setting column width, text size, font attributes.The template table contains text which helps with the formatting

  14. Linking the template presentation with the Excel workbook for the heat map

    – Open the SlideFab tab in the ribbon.
    – Click the small chevron (down angle) button where it says “Excel: Closed”.
    – Choose either of the first three entries:
    — “Select and Open Excel file” will let you browse the hard drive for the Excel file.
    — “Open Excel file” will re-open an existing file, if there had been one already. If not, it has the same effect as the one mentioned before.
    — “Try to use currently open Excel workbook” will try to establish a connection with the Excel workbook which was active most recently.Opening the Excel workbook from the PowerPoint template using SlideFab

  15. Then SlideFab will align Excel next to PowerPoint and signal that the connection is established with the green, open folder icon saying “Excel: Open”.

    Connect the Excel workbook containing the heat map with the PowerPoint template presentation

  16. Connecting the heat map Excel range with the table on the PowerPoint template slide

    – Select the whole range containing the heat map (with header row and header column).
    – Select the target table 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.To let SlideFab create the heat map within the target table in the PowerPoint template, the link needs to be set up

  17. 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. It will work even without an Iteration Loop using the current state of the Excel workbook.To create the heat map in PowerPoint, the slide makes automation of SlideFab is started here

  18. SlideFab created the heat map. However, the text wraps within the header row.
    To fix this the Excel may remain untouched. Setting column widths in Excel has no effect on the PowerPoint column width. But changing the cell margin in the table of the PowerPoint template presentation does the job.

    The created heat map in PowerPoint which is not perfect yet as there is text wrap in the header row

  19. With the template table selected, go to the Table Layout tab and reduce the cell margin.

    The column margins fine tuning to avoid text wrap within the header row

  20. Rerun the slide making automation as explained above and check the output.

    Perfect. The created heat map in PowerPoint looks just fine.The created heat map in PowerPoint

  21. PART 3: Using lots of shapes on a PowerPoint slide for the heat map

    An alternative to use a table as target in PowerPoint are shapes. Lots of shapes. While so far the whole heat map range was connected to a whole table, this time each heat map Excel cell will be linked to a shape.

    This section is kept shorter to avoid repeating basic steps from above.

  22. Create new slide in the template presentation

    – Create a new slide within the template presentation.
    – Add and format shapes for the heat map. 14 columns and 7 rows (1 for the header, 6 for the countries).The PowerPoint template slide with separate shapes instead of one table used for the tutorial on how to create a heat map in PowerPoint

  23. Link the PowerPoint shapes to cells from the heat map

    – Select one of the heat map cells
    – Select the corresponding 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 cells into the control field.
    – Repeat until all heat map cells are connectedScreenshot showing Excel and PowerPoint side by side. It explains how to link the shapes in PowerPoint to cells in Excel. It is used for the heat map tutorial

  24. Rerun the slide making automation as explained above and check the output

    The created output slide looks fine. SlideFab created the heat map in PowerPoint automatically using shapes.

    Using shapes instead of a table seems to be a little silly at first glance. It takes more effort to link all cells separately. It is also not so flexible regarding more/less rows and columns. However, the shapes have also benefits. The more custom-tailored the output slides are going to look like in the end, the greater the benefit of separate shapes can be. Formatting and linking cells separately is not only more effort, it also gives more freedom and control.
    The created heat map using PowerPoint shapes instead of a table

What may come next?

With the heat map in place, there are a couple of things to look at

Making the heat map dynamic using Iteration Loops

This tutorial was about static heat maps. But combining the heat maps with Iteration Loops is a highly powerful scenario which will be the topic in the upcoming tutorial.

Making the heat map with shapes a little more flexible using shape conditions

When the number of rows changes the heat map approach using shapes is not so flexible. This can be improved. With shape conditions it is possible to delete unused shapes when making slides. This also works in combination with Iteration Loops, of course.

Colouring cells based on a second calculation

This example used margin % both for the table entries as well as the foundation of the conditional format. Even cooler it would be to have the absolute margins as table entries while sticking to the conditional formats based on margin %. With a modification within the Excel worksheet this is possible.