Opinion

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.

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.

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.

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.

Exhibit 4

Desired action Using comparison phrase Expression entered in text box
Highlight an expense (in cell F6) if equal to or more than 10% over budget (in cell E6) Greater than or equal to =E6*1.1
Highlight any accounts receivable balance greater than 90 days old Greater than =NOW()-90
Highlight sales that are less than $1,000 or greater than $10,000 Not between 1000 in first box
10000 in second box

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 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:A10)>100)
Highlight cell if the sum of the units sold was greater than 1,000 and the average was greater than or equal to 50 =AND(SUM(A1:A10)>1000,
AVERAGE(A1:A10)>=50)

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

Exhibit 6

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

Exhibit 7

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.

Exhibit 8

Exhibit 9

Exhibit 10

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.

Excel syntax Entered in cell B12 Displayed in B12
=COUNTIF(range, criteria) =COUNTIF(B2:B10,”
>50000”)
2

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

Excel syntax Entered in cell B14 Displayed in B14
SUMIF(range, criteria) =SUMIF(B2:B10,”
>50000”)
$185,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 11

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.

Excel syntax Entered in cell B13 Displayed in B13
=SUMIF(range,
criteria,sum_range)
=SUMIF(A2:A11,”Adams”,
B2:B11)
$29,000

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 cell B16 Displayed in B16
=SUMIF(range, criteria) =SUMIF(C2:C11,C15,
B2:B11)
$23,000
or, using the range name =SUMIF (C2:C11,
Region, B2:B11)
$23,000

Exhibit 12

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

Related Articles

Back to top button
Stay up to date
Don't miss out on the latest industry news and articles
Stay up to date
Don't miss out on the latest industry news and articles
You are Subscribed!
Your subscriptions means a lot to us.
Don't miss out on the latest Industry news
You are Subscribed!