# Microsoft Excel: Use Color to Identify Variances

*By David Ringstrom, CPA*

**Figure 1:**We'll use Conditional Formatting to identify variances that are both +/- $2,000 and +/- 10%.

*Click cell D2, choose Conditional Formatting on the Home tab, and then click New Rule, as shown in Figure 1.*

**1. Excel 2007 and later:**

*Excel 2003 and earlier:**Click on cell D2, choose Format, and then Conditional Formatting.*

**Choose Use a Formula to Determine Which Cells to Format, as shown in Figure 2.**

*2. Excel 2007 and later:**Change Cell Value Is to Formula Value Is.*

**Excel 2003 and earlier:****Figure 2:**Conditional Formatting offers a variety of options, including crafting your own formulas.

*Enter this formula:*

**3. 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.*

**4. All Excel Versions:****Figure 3:**Color is one of many types of formatting that you can apply via Conditional Formatting.

*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.*

**5. All Excel versions:****Figure 4:**Use the Format Painter icon to transfer formatting from cell D2 to cells D2 through E10.

*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.*

**6. All Excel versions:****Figure 5:**You may have to restore some number formats after transferring Conditional Formatting from one cell to many.

**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.

- 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.

**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* [9]* or follow him on **Twitter* [10]*. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner **CPE Link* [11]*.*