One of the things that always struck me as odd about using subtotals is that only the words in the subtotals turn bold, and not the actual subtotals themselves. With a long list of data this can make it hard to see which numbers are the subtotals amongst the data. Fortunately this is very easy to fix using conditional formatting.
Assume that you have a list of data with Date, Vendor and Amount columns. After going to Data --> Subtotals and selecting to Sum the Amount for every change in Date, you’d end up with a subtotaled list like this:
Let’s assume that you’d like to see the subtotals in the Amount displayed in bold with a line on the top and bottom.
- Start by highlighting Column C
- Excel 2003: Go to Format --> Conditional Formatting --> Change the dropdown to read “Formula Is”
- Excel 2007 and higher: Go to Home --> Conditional Formatting --> New Rule --> Use a formula to determine which cells to format
- Enter the formula =Right($A1,5)=”Total”
- Click Format
At this point you’ll see the familiar “Format Cells” dialog box where you can choose what properties you’d like used to display the data in your cells. On the Font tab select Bold, and on the Borders tab click the top and bottom border of the cell. Once done, click OK until you are back at the spreadsheet.
Notice that your data list is now much more readable!
There are two things that you need to understand to be able to construct effective conditional formats:
- When constructing formulas, the references are always based from the active cell. In the case of our example we selected column C which meant that the active cell became C1. This is why we looked at $A1 to see if it contained “Total”. In cell B2 and C2 we’d be looking at $A2 and so on down the list. The active cell is usually the first cell in your selection, and can be identified as its background is always clear, compared to the other cells in the selection which have shaded backgrounds.
- All formulas are evaluated to see if they are TRUE or FALSE. So in our example we checked to see if the right 5 characters = “Total”. If so, the conditional format is applied, if not it isn’t. As you can see from the second image this test would have returned false on A8, but returns true on A9.
Conditional formatting is an incredibly useful tool that is not restricted to working with subtotals, and can be applied to anything that lives within the spreadsheet grid, including Pivot Tables. Just a few examples of where I use conditional formats are: Highlighting the largest or smallest numbers in a list, highlighting invalid data that has been entered into a worksheet, colour banding alternate rows to make large spreadsheets easier to read and highlighting row(s) in large data sets.
This tool has existed in Excel for many versions, but went through a large upgrade in Excel 2007. Until then you were limited to using only 3 conditional formats in any cell, but with the release of Excel 2007 this limitation was removed. You can now use as many formats as you wish, with the only limit being the memory in your computer. Other additions in Excel 2007’s conditional formatting toolset include Data Bars, Icon Sets, Heat Maps, overlapping formats and the ability to stop any further formats from being applied once one is triggered.
Used properly, conditional formats are a truly powerful feature in Excel, and well worth exploring if you haven’t done so already.
Download a copy of the sample workbook so you can try it yourself!
This article was originally contributed to CMA Update Magazine - Summer 2009
