Reporting in Excel and PowerPoint

Conditional Formatting in PowerPoint

 

NEW: Please click here and have a look how we enable conditional formatting in PowerPoint in OfficeReports version 11

 

Below you can read how we did this 4 years ago:

 

Besides the data, OfficeReports Link can show the background colors of cells in a Microsoft 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 walk through some steps on how to create conditional formatting to show icons in PowerPoint based on significant information in an Excel table. Here is a sample Excel table where each year is compared to the previous year, and how we would like to visualize this in PowerPoint: 

 

We want to color cells with a value significantly higher than the last year green, and color them red in case the value is significantly lower than last year (as shown). To do this you can select the 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, you can define the condition. To define the red color, we check if a cell in the column contains say, a B. This is done by using the formula =FIND("B";B2;2).

Now, the conditional formatting for the cell is done. To copy the conditional formatting to the rest of the column, select the cell and click the 'Format Painter'. Now select the range of cells, and Excel copies the conditional formatting to the rest of the column.

To format the next column, select the cells again again, and click the "Format Painter". Now, select the next range of cells. 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. To do this, you click 'Conditional Formatting->Manage Rules...', and change the formula to match. Follow the same procedure for the next column, and you are done.

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 versus relative reference: conditional formatting rules simplified. And here a complete tutorial: use conditional formatting in Excel.

If you have any questions or remarks, please contact us! Gem

Author

Fred Balkenende