This month’s highlights

Introduction

This post was inspired by a practical issue I came across when reviewing a list of monthly results spread over a few years. I wanted to quickly be able to compare the results for the same month in other years. In particular, I wanted to see how the figures for the current month compared to previous years. Obviously, there are several ways in which I could have achieved this, including filtering the table of results to just show those for a particular month but I decided to just add a cell fill that would highlight the cells for the relevant months. I also wanted to make the highlight cell fill appear automatically. Given my current obsession with extolling the virtues of PivotTables, I thought I would base the example on a PivotTable, but the technique would also work on a simple Excel table of values.

Conditional formatting with a formula

The Conditional Formatting dropdown in the Styles group of the Home Ribbon tab allows a range of conditional formats to be applied with a mouse click or two. However, it is one of the more advanced conditional formatting options that we need to work with: ‘Use a formula to determine which cells to format’. We can use this type of condition by selecting the cells to which we want to apply it, and then choosing ‘New rule’ from the dropdown:

In this example, we have created a PivotTable from our table of data and, for demonstration purposes, placed it on the same sheet. We have selected cell I2 and set the formula for our condition to:

=MONTH($H2)=MONTH(TODAY())

We use the MONTH() function to extract the month number from our date cell H2 and then compare it with today’s month number using the MONTH() function again, together with the TODAY() function which returns the current system date. We have set our conditional format to a light green cell fill.

At the moment, our conditional format just applies to cell I2. For a normal conditional format, if we apply it to a cell within a PivotTable data area, a Formatting Options icon will appear allowing us to apply it to an area of the PivotTable rather than just to an individual cell. This does not happen for a formula-based rule, so instead, we can choose the appropriate PivotTable area in the Apply Rule section of the Edit Formatting Rule dialog. The difference between choosing ‘Sum of Sales’ and ‘Sum of Sales values for Invoice Date’ is that the latter option would exclude total and subtotal values which could be particularly significant if we were choosing one of the graphical formats such as data bars:

It's also worth mentioning that we have set our cell reference to $H2, using the dollar sign to fix the reference to column H whilst allowing the row reference to change so that it will apply to each of the rows in our PivotTable area. By fixing the column reference, we allow the format to be applied to multiple columns if we were to add a field to the column area of our PivotTable:

As we can see, because I’m writing this article in the month of September, all the September rows are automatically highlighted by our conditional format.

We have seen that our format continues to work when we add columns to our PivotTable but changing the row area by adding a field to it for example, is likely to prevent the format working as intended.

Conclusion

Straightforward conditional formats can be very useful, but understanding how to work with rules based on formulas, including formulas that use one or more Excel functions, can extend the power of conditional formatting significantly. For formulas to work in this way, it is usually vital to ensure that you get the dollar signs in the right places in the cell references that are included in the formula.

Additional resources

You can explore all aspects of Excel, including conditional formatting and PivotTables using the ICAEW Excel archive portal:

The 'Open in full-screen mode' icon in the bottom right-hand corner of the embedded report should show the contents at a more readable size with the Escape key returning you to the post.