Vigilant Spreadsheets

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.

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.

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.

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 lists a few examples using the Cell Value Is option.

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 shows several examples using this option.

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

Exhibit 7 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 illustrates the Font screen; exhibit 9, the Border screen; exhibit 10, 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 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.

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

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

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

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.

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