By David Ringstrom, CPA
Accountants are often given the mundane task of identifying variances that exceed specific tolerances. Rather than trying to eyeball columns of numbers, you can use Excel's Conditional Formatting feature to make such variances leap out on the screen. I'll demonstrate how you can use Excel's conditional formatting to identify variances that are both $2,000+/- and 10%+/- of the budget.
Conditional Formatting is a powerful feature, but it has some nuances that can frustrate uninitiated users. Depending on the conditions you're trying to apply, it's often best to apply the formatting to a single cell and then use Excel's Paste Special Formatting feature to transfer the formatting to the remainder of the related cells. Formula-based Conditional Formatting, which I'll demonstrate in a moment, often won't return the desired results when you attempt to apply it to multiple rows or columns at once.
Figure 1 shows a representative actual versus budget comparison. Our goal is to apply yellow highlighting to any variances of both +/- $2,000 and +/- 10%. Specifically, a row should only be highlighted when both tests are met. I'll first describe the steps for use in Excel 2007 and later, and then offer a brief rundown in Excel 2003. To implement such a rule, carry out these steps:
Figure 1: We'll use Conditional Formatting to identify variances that are both +/- $2,000 and +/- 10%.
1. Excel 2007 and later: Click cell D2, choose Conditional Formatting on the Home tab, and then click New Rule, as shown in Figure 1.
Excel 2003 and earlier: Click on cell D2, choose Format, and then Conditional Formatting.
2. Excel 2007 and later: Choose Use a Formula to Determine Which Cells to Format, as shown in Figure 2.
Excel 2003 and earlier: Change Cell Value Is to Formula Value Is.
Figure 2: Conditional Formatting offers a variety of options, including crafting your own formulas.
3. All Excel versions: Enter this formula:
=AND(ABS($D2)>2000,ABS($E2)>0.1)
4. All Excel Versions: Click the Format button, choose Fill tab, click the desired color, such as yellow shown in Figure 3, and then click OK twice.
Figure 3: Color is one of many types of formatting that you can apply via Conditional Formatting.
5. All Excel versions: Select cell D2, click the Format Painter as shown in Figure 4, and apply the formatting to cells D2 through E10. The Format Painter appears on the Standard toolbar in Excel 2003.
Figure 4: Use the Format Painter icon to transfer formatting from cell D2 to cells D2 through E10.
6. All Excel versions: Reapply the percentage number formats in cells E2 through E10, as shown in Figure 5. Excel doesn't offer a way to transfer conditional formatting by itself without overwriting the existing number formats. However, it's far easier to get Conditional Formatting right by applying it to a single cell first, and then reapply any number formats as required.
Figure 5: You may have to restore some number formats after transferring Conditional Formatting from one cell to many.
If you're using Excel 2007 or later, you can now right-click on one of the yellow cells, choose Filter, and then Filter by Selected Cell's Color, as shown in Figure 6. You can also sort by color as well in Excel 2007 and later. Figure 7 shows the filtered list. To remove the filter, click the Filter icon on the Data tab in Excel's menu known as the Ribbon.
Figure 6: You can filter (as well as sort) based on color in Excel 2007 and later.
Figure 7: The variances are identified and isolated.
With regard to the formula that I used:
- The ABS function returns the absolute value of a number – in other words ($17,396) converts to $17,396.
- The AND function allows you to test for up to 255 conditions at once. In this case, I'm testing for the absolute value of the variance in column D being greater than $2,000, and the absolute value of the variance percentage in column E being greater than 10%. If both tests are true, then conditional formatting will be applied.
- The dollar signs before the column letters are critical when applying Conditional Formatting in this fashion since in effect we're copying and pasting the format to a second column. If you omit the dollar signs before the column references, Excel will adjust the column references, meaning that your Conditional Formatting won't return the desired result.
A previous version of this article first appeared on www.accountingweb.com .
About the author:
David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link
