Vigilant spreadsheets: make data analysis fast and easy.

Would you like to be able to scan your company's financial operations spreadsheet and instantly see which departments are over budget or behind schedule or which accounts receivable are past due? There's an easy way to do that in Excel, which can automatically flag cells that meet most any condition you establish. You can set the cells to display different formatting flags--colors, font styles, shading, patterns, underlining--with each custom format identifying a specific financial condition. For example, you can program Excel to flag costs that are over budget by displaying them as red; under-budget costs may appear blue.

The Excel function that does this job is conditional formatting. What makes the function especially handy is that it's not static--that is, when the data in the worksheet change, the cells instantly reflect that by taking on the appropriate formatting.

To set up the function, first highlight the cells you want to include. Then click on Format, Conditional Formatting, which brings up the dialog box shown in exhibit 1, at right.

In the dialog box you can specify the conditions that will trigger specific formats. The first field--Cell Value Is--is the first selection in a pull-down menu. If you click on the down arrow to the right of the field, the screen will display the alternate menu item--Formula Is--as shown in exhibit 2, at right.

Excel allows two formatting criteria: one based on a constant, referred to as Cell Value Is, or a formula, which is labeled Formula Is. We'll get back to how both are applied.

The next step is to set the condition that triggers a format. Again, clicking on the arrow to the right of the between condition evokes a drop-down menu, as shown in exhibit 3, above.

Use the Cell Value Is option when you want to compare the cells you're conditionally formatting with a constant, using any of the logical operators in the exhibit 3 menu: between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.

After you select an operator from the drop-down menu, enter comparison information in the two boxes to the right of the between field.

For most of the other operators--such as equal to and less than--a single text box is displayed. When using a value as the formatting criteria, you can enter a number (100), a cell reference (=C16), a date (January 3, 2001), text (="Smith") or a formula (=E6*1000/2). All formulas must start with an equal sign, and text must be enclosed in quotes. Exhibit 4, above, lists a few examples using the Cell Value Is option.

Exhibit 4

Desired action               Using comparison    Expression entered
                                  phrase             in text box

Highlight an expense         Greater than or     =E6*1.1
(in cell F6) if equal to     equal to
or more than 10% over
budget (in cell E6)

Highlight any accounts       Greater than        =NOW()-90
receivable balance greater
than 90 days old

Highlight sales that         Not between         1000 in first box
are less than $1,000 or                          10000 in second box
greater than $10,000

Use the Formula Is option to change the format of the cell you're conditionally formatting depending upon the data, or a condition, in another cell or cells. The Formula Is option displays a text box in which to enter a formula with a logical condition that can be evaluated as either true or false. If the logical value is true, then the conditional formatting is applied to the cells. Exhibit 5, below, shows several examples using this option.

Exhibit 5

Desired action                                  Formula entered in text

Highlight cell if greater than 100              =A1>100

Highlight cell if the sum of the units sold
was greater than 100                            =(Sum(A1:A1O)>100)

Highlight cell if the sum of the units sold     =AND(SUM(Aa:A10)>1000,
was greater than 1,000 and the average          AVERAGE(A1:A10)>=50)
was greater than or equal to 50

Exhibit 6, page 43, shows the completed dialog boxes that cover the first example given above for the Cell Value Is option.

Exhibit 7, page 43, shows the completed dialog boxes that cover the first example given above for the Formula Is option.

TIME TO FORMAT

Once you've selected the desired formatting condition, you must specify the format to apply to the cells if they meet the specified condition. To do this, click on the Format button, which evokes the Format Cells dialog box. By clicking on any of the three tabs on top of the box (Font, Border or Patterns), you can select from a wide choice of formats. Remember also that the conditional formatting will show up on your screen and on the printouts of your spreadsheet. If you plan to print the worksheets and a color printer isn't available, just use the shading, borders and font options.

Exhibit 8, below, illustrates the Font screen; exhibit 9, page 44, the Border screen; exhibit 10, page 44, the Patterns screen, which lets you set both a pattern and a color format.

When you're finished selecting the format options, click on OK to apply the conditional formatting. If you later need to change the criteria or the format of the cells, simply highlight the cells, open the Conditional Formatting dialog box and make any changes you wish. Delete conditions no longer wanted by clicking on Delete, checking the box for the condition you want to remove and clicking on the OK button.

You may find one condition is too limiting for what you want to do, so Excel allows you to have up to three additional conditional tests. To add a condition, click on the Add button in the Conditional Formatting dialog box to display another set of drop-down menus and text boxes. Use the process described above to set the additional operators, values and formulas.

If you have developed a conditional format that you want to use in other cells, you can easily copy it using the Format Painter tool.

MOVING THE DATA

When you sort, count or regroup data for further analysis, the numbers constantly change. Conditional formatting will stand up to those moves if you use the COUNTIF and SUMIF functions.

Excel's COUNTIF and SUMIF functions make sorting, counting and regrouping much more efficient because you don't need to sort and resort the data to find the correct counts and sums. Both functions can analyze subsets of your data that match a specified condition. The SUMIF function will add only the cells in a range that match a set condition, while the COUNTIF function will find the number of cells in a range that meets a certain criteria. Thus, you can easily find the total sales for each salesperson and count the number of sales without sorting the data. You also can use the same conditional formatting in your COUNTIF and SUMIF cells time after time simply by adjusting the range of data input in your formulas.

Let's see how that's done. There are two parts to the SUMIF and COUNTIF functions--the range to check and the criteria to match. As with most Excel commands, the first step is to specify the range of cells that you want to analyze by highlighting them. Then, you set the criteria or condition. As is the case with conditional formatting, the condition can be a number (101), a range (">1000") or text ("Smith").

Exhibit 11, page 45, illustrates both the SUMIF and COUNTIF functions. The spreadsheet contains customer names (column A) and outstanding accounts receivable balances (column B). The COUNTIF function could be used to find the number of customers with accounts receivable balances greater than $50,000.

Excel syntax               Entered in cell B12      Displayed in B12

=COUNTIF(range,            =COUNTIF(B2:B10,"        2
  criteria)                >50000")

Similarly, the SUMIF function could be used to find the total dollar amount of all accounts receivable balances greater then $50,000.

Excel syntax               Entered in cell B14      Displayed in B14

=SUMIF(range,              =SUMIF(B2:B10,"          $185,000
  criteria)                >50000")

In the SUMIF function, by default Excel adds up the cells in the range that meet the criteria. If you want to find the sum of another range of cells rather than the range used to match the criteria, you can use the sum_range option, which allows you to define another range of cells to add (sum_range) but only if the criteria specified in the range are met. For example, you may want to sum the sales for a particular salesperson or region, sum invoices for a particular vendor or customer or sum hours an employee worked.

Exhibit 12, page 45, illustrates that function. The spreadsheet contains data with salesperson (column A), total invoice (column B) and region (column C). The SUMIF function with the optional sum_range can be used to find the total sales for Adams.

Excel syntax               Entered in               Displayed
                           cell B13                 in B13

=SUMIF(range,              =SUMIF(A2:A11,"Adams",   $29,000
criteria,sum_range)        B2:B11)

The cells in the sum range (B2:B11) are added only if the value in the check range (A2:A11) matches the criteria "Adams." The SUMIF function also can calculate sales by region. In exhibit 12, cell C15 has been assigned the name "Region."

Excel syntax               Entered in               Displayed
                           cell B16                 in B16

=SUMIF(range, criteria)    =SUMIF(C2:C11,C15,       $23,000
                           B2:B11)

or, using the range name   =SUMIF (C2:C11,          $23,000
                           Region, B2:B11)

With very little time and effort, conditional formatting and SUMIF and COUNTIF functions help you to analyze spreadsheet data. In addition, they alert you to significant changes--thus focusing on the most important data in your worksheet.

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces.

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type indicates commands and instructions that users should type into the computer and the names of files.

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series, or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is [e-mail].

CHARLES KELLIHER, CPA, is an associate professor at the School of Accounting, University of Central Florida, Orlando. His e-mail address is [e-mail]. LOIS S. MAHONEY, CPA, is an assistant professor at the university. Her email address is [e-mail].