Vigilant Spreadsheets: Make Data Analysis Fast and Easy
Charles F. Kelliher, Lois Schafer Mahoney · Journal of accountancy online/Journal of accountancy · 2001
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. …