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
- 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 theSalesTerritory.Country
column within the table calledData
. - 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]))
- 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 theOrder Date.MonthKey
column in a descending way (newest first). TheSEQUENCE
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 otherSORT
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. - 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 asMMM 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. - 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. - Formatting margin %
– Select the entire range with the margin % results.
– Format the entries as percentages - 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. - 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. - So far so good. The Excel workbook is ready with a working heat map.
- 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.
- Setting up the template slide
For this example having a static title is alright
Add a title for the template slide - 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. - 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. - 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. - Then SlideFab will align Excel next to PowerPoint and signal that the connection is established with the green, open folder icon saying “Excel: Open”.
- 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. - 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. - 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. - With the template table selected, go to the Table Layout tab and reduce the cell margin.
- Rerun the slide making automation as explained above and check the output.
Perfect. The created heat map in PowerPoint looks just fine.
- 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. - 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). - 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 connected - 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.
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.