Save content
Have you found this content useful? Use the button above to save it to your profile.

Give your reports more oomph 2: Conditional formats

by
12th Aug 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

Simon Hurst continues his series exploring ways to improve reporting with a look at conditional formatting.

Introduction

You are likely to have to put a lot of work into preparing the information for your Excel reports and ensuring that it is correct, so it would be a shame if your final report fails to deliver the maximum possible clarity and impact. In this series we will look at a variety of ways to improve reporting, including the secrets of Excel number formatting through to replacing printed pages of figures with interactive on-screen reports. The second part of the series looks at using conditional formatting to focus attention.

Conditional formatting and Excel versions

Conditional formatting allows you to set criteria to automatically control the formatting applied to a cell or group of cells. In Excel 2003 the process of setting up conditional formatting was manual and the range of formats available was limited to basic cell and font formatting. There was also a limit of three different conditional format settings per cell. Excel 2007 introduced significant enhancements to conditional formatting. Conditional formatting based on simple criteria could be set up by selecting preset options; an extended range of graphical formatting options was added including Data Bars, Colour Scales and Icon Sets, and the limit of three settings per cell was removed. The improvements did not stop with Excel 2007. Excel 2010 brought some obvious changes such as 'solid fill' data bars and additional icon sets and colour scales. Delving into the detailed rule options reveals further improvements including greater control over how negative values are dealt with when using data bars and the ability to 'pick and mix' icons from different icon sets.

A further significant improvement allowed conditional formatting to be applied more effectively in PivotTables. In Excel 2003, conditional formatting could only be applied to a range of cells. This made it difficult to use with most PivotTables as the range of cells to which the formatting needed to be applied was subject to frequent change. From Excel 2007 onwards, conditional formatting could be applied to the Values area of a PivotTable as well as to fixed ranges of cells.

Conditional formatting in action

There are many ways to use conditional formatting to improve the impact of your management reports but you also have to ensure that, in adding emphasis to particular aspects of your reports, you don't also compromise clarity. In this example of a simple profit and loss report, we have used three different conditional formatting methods to highlight important values:

In column F we have applied a colour scale with a green – amber – red progression from low to high values. In column E we have used a traffic light style icon set and reversed the order of the icons to show the highest values in red. We have left column D unchanged but have created a set of references in column C to the values in column D and then formatted those values in column C in the same way as the values in column D. We have then gone on to set one of the detailed rule options to Show Icon Only. In Excel 2010 and later we can also choose individual icons for each level and we have set the lower two levels to 'No Cell Icon' so we are just left with the red icon to emphasise the high values without in any way detracting from the clarity of the numbers in column D:

So far, we have just used the default values for the three icon bands based on dividing the range of values into thirds. It will often make more sense to highlight those values that lie above a certain threshold value. The 'Type' dropdown allows the use of Number, Percent, Formula and Percentile. To use a threshold value we can set the type to Number and enter the threshold as the Value. While it is possible just to type in the value, it is much better to refer to a cell that contains the threshold value. This makes is much easier to see what the value is and to change it. It also makes it easier to use a dynamic calculation to set the threshold using a formula. In this case we have applied the Range Name 'WarningLevel' to the cell that contains our threshold value and changed our rule accordingly, using the Range Name in the Value field:

We can see the effect of entering 2000 into our WarningLevel cell:

warninglevel cell

Click image to enlarge

Excel 2007 and using a single icon

Excel 2007 includes the Show Icon Only option, but does not include the ability to choose icons separately for each level. Accordingly, we need to use a different method to avoid the amber and green icons. When multiple rules are set up for the same range of cells it is possible to set one or more to 'Stop If True'. We could therefore add a rule that identifies all the values below our threshold value without needing to apply any format. By setting this as 'Stop If True' we can ensure that our Icon rule is only applied for the values over our threshold:

excel conditional rule manager

Negative data bars

In Excel 2007 conditional formatting the widths of data bars were based on the total range of values including negatives, so that negative bars were smaller, but in the same direction, as positive bars. From Excel 2010 it is possible to set the colour and position of negative bars separately from the positive bars. In this example, we have also used the ability to apply a conditional format to the PivotTable Values area:

conditional formati

Click image to enlarge

When we apply a conditional format to a single cell within the PivotTable Values area, a Formatting Options button appears and this lets us apply the format  to either the whole Values area, including any grand totals, or to just the individual values. In this example, we have set the 'Show Values As' option for the Values area to 'Difference From' and chosen to base the calculation on the difference of each value from the value for the Salesperson, Andrew Fuller. This results in the Andrew Fuller column being blank and some positive and some negative values in the other salesperson columns. We have applied a data bar, turned on 'Show Bar Only' and chosen to show the negative values as a black bar, with positive bars in red.

Next time

The first part of the series looked at number formats and in the rest of the series we will be looking at graphics including Sparklines and the Excel Camera, and using a range of Excel tools and features to create interactive reports that allow the user to explore the relevant information for themselves.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.