Share this content
AIA

Excel 2010 KPI tutorial 1: Conditional formatting

by
5th Sep 2011
Share this content

Simon Hurst returns to the scene of a previous management reporting tutorial to see how he can improve the presentation using new facilities in Excel 2010.

With the advent of PowerPivot and the graphical enhancements in Excel 2007 and 2010, Microsoft is positioning Excel as a “self-service business intelligence” tool. If you are going to be presenting key performance information to your colleagues or clients, it’s worth paying particular attention to presenting the information clearly and with maximum impact. It would be a shame to spend days collecting and collating the vital figures and then fail to present them in a way that provokes appropriate action.

For this first part of the series we are going to revisit some data we looked at almost exactly three years ago in a previous KPI reporting tutorial:

KPI table - sales dataHere we used the conditional formatting in Excel 2003 to help focus attention by using a simple “traffic light” colour coding for the sales values.

This time we will use one of the new types of conditional format that Excel 2007 introduced – the data bar.

Step one

Add a column to our data to hold references to the SalesTotal values:

Sales dataStep 2

Use Home ribbon, Style section, Conditional Formatting to choose a red Solid Fill data bar:

The Solid Fill data bars are an Excel 2010 enhancement and make it easier to see the exact position of the right hand edge than the previous ‘gradient’ fill. This is the result:

Step 3

We can improve the appearance of our data bars and adjust the various parameters by going back to Conditional Formatting and choosing Manage Rules, then select our data bar conditional format and choose Edit Rules:Excel 2010 Edit Formatting Rule dialogue

Here we have chosen a more vibrant red colour and decided only to show the bar, not the underlying figures:

Sales KPI figures presented as conditionally formatted bars

We now have a graphical presentation of our data using only a small additional column, rather than as a separate chart.

Step 4

In Excel 2007 and 2010 we could turn our data, including the graphical totals column, into a Table using the keyboard shortcut Control+T, or: Insert ribbon, Tables section, Table or: Home Ribbon, Style Section, Format as Table. Here we have chosen a plain format for our Table. As a Table, if we add a new row, then the formatting and formulae from the existing rows will automatically be copied to the new row:So far so good, but the red colour bars don’t reinforce the traffic light formatting of the numbers. My initial experiments to achieve this have been somewhat long-winded. If you really want to get your teeth into a challenge until the next ExcelZone KPI reporting tutorial appears, how would you go about combining the traffic light and data bar conditional formatting to create traffic light data bars?

Other ExcelZone management reporting tutorials

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping accountants make effective use of technology. He is a regular contributor to AccountingWEB's ExcelZone and the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website.

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By Gaffer29
21st Oct 2011 12:48

Conditional formatting

Why not try the icon sets? The flags and traffic lights look pretty good! Gaffer

Thanks (0)