Conditional Formatting in PowerPoint

Besides the data, OfficeReports Link can show the background colors of cells in an Excel range in PowerPoint tables, charts and shapes. It is even possible to turn these background colors into icons in PowerPoint. Since we are linking data, we also want the background colors and icons to change automatically when the data changes. This means we have to use Excel’s conditional formatting functionality.

In this post we will have a look at how to create conditional formatting so we can show icons in PowerPoint based on significance information in an Excel table. Let us have a look at a table where each month is compared to the previous month, and how we would like to visualize this in PowerPoint:

FromExcelToPowerPointSigTable

We want to color cells with a value significantly higher than the last month green, and  color them red in case the value is significantly lower than last month.Let us start with cell C2. Select this cell, and click ‘Conditional Formatting->New Rule…’, and choose ‘Format only cells that contain’. If the cell contains ‘A’, we want the cell to appear green, so define the condition like you can see here.CF definition1To define the red color, we check if cell B2 contains a ‘B’. This is done by using the formula =FIND(“B”;B2;2), as you can see below.

CF definition2

Now, the conditional formatting for cell C2 is done. To copy the conditional formatting to XLS Format Painterthe rest of the column, select cell C2 and click the ‘Format Painter’. Now select de range from C2 to C6, and Excel copies the conditional formatting to the rest of the column.

To format the next column, select cell C2 again, and click the “Format Painter”. Now, select the range from D2 to D6. The conditional formatting is copied again, but now we have to edit the definitions, so we look for the right characters in the right columns.

Click ‘Conditional Formatting->Manage Rules…’, and change the “B” in the formula in red condition to “C”, so the formula is =FIND(“C”;C2;2), and change the green condition to ‘Contains B’. XLS SigTable with CF

Follow the same procedure for the next column, and you are done. This gives the following result:

In this blog post, you can read how to turn these background colors into icons in PowerPoint.

An other interesting blog post you might want to look at explains more about conditional formatting and when to use absolute reference vs relative reference: conditional formatting rules simplified. And here a complete tutorial: use conditional formatting in Excel.

If you have any questions or remarks, please leave a comment below!

Gem

Leave a Reply