Spreadsheet Smarts

lthough there’s hardly a CPA who doesn’t use a computerized spreadsheet, surprisingly, most users take advantage of only a tiny fraction of the application’s built-in tools. Those CPAs miss out on the software’s many laborsaving and work-enhancing functions.

This tutorial focuses on three very powerful spreadsheet functions: Conditional Formatting, Subtotal and Auto Filter. Although this article uses the more popular Microsoft Excel 97 to illustrate the functions, Lotus 1-2-3 has similar tools. Let’s look at how these features work.

CONDITIONAL FORMATTING

Would you like a spreadsheet to alert you when two numbers that should agree with each other don’t, or when a figure meets, exceeds or fails to meet conditions you set for it? An Excel function, Conditional Formatting, causes the appearance of cells to automatically change under circumstances the user specifies. The function is easy to set up and extremely useful in myriad applications.

To illustrate, review the balance sheet shown in exhibit 1. Notice that the number in Total Assets (C15) doesn’t equal the number in Total Liabilities and Owners’ Equity (C32) and that both figures are highlighted in red. To get this to happen, you can set the Conditional Formatting function to display those numbers in red whenever they fail to equal each other. The user could have programmed the function to turn another color or to provide a different signal or to respond to another triggering circumstance.

Here’s how to set up the function: Using exhibit 1 as an example, select cell C15 containing the Total Assets amount—$4,480,000—and then, holding down the Ctrl key, select cell C32 containing Total Liabilities and Owners’ Equity—$4,540,000. Then click on the Format menu and select Conditional Formatting. Those steps will evoke the Conditional Formatting screen, as shown in exhibit 2 .

A user can define as many as three separate, simultaneous conditions that must be met to make selected cells change appearance. For this example, create just one. You can change the format of your selected cells when the cell value falls between two chosen values, which can be either a fixed number or linked to cells on your worksheets; or you can create a formula that, when the condition is “true,” changes the format of the selected cells.

For this example, click on the drop-down arrow in the Cell Value Is field and select the second option, Formula Is. Then define a formula that, when true, changes the format of the selected cells to indicate a problem. The simplest way to accomplish this is to click on the range button to the right of the formula field, select the Total Assets cell, type <> to denote “does not equal,” select the Total Liabilities and Owners’ Equity cell and press Enter. The formula should appear in the formula field as =$C$15<>$C$32. Translation: When C15 does not equal C32.

Now click on the Format button to establish how the cells will appear when the formula is true—that is, when the cells don’t balance ( see exhibit 3 ). One good option is to set the Font Color to white and the Pattern to red for optimal contrast. Click OK and the Preview field will show how the cells will look when the formula is equal to true. Click OK again to complete the function. The cells for the dollar amounts for both the Total Assets and the Total Liabilities and Owners’ Equity appear red with a white font, indicating that the formula is true—the two cells are out of balance. If you correct either number, bringing the two values into balance, the cells will return to their default formatting.

When to employ it: You can use this function for many different situations—anytime you wish to compare one cell’s value with another’s. Two common examples: bank reconciliations and cash flow statements.

Helpful tip: Changing the format of one of the selected cells, for example, by increasing the size of the font or applying a bold style, won’t disturb the underlying Conditional Formatting formula.

SUBTOTALS

Nearly all spreadsheet users apply the Sum function (which adds a specified range of numbers). Few, however, use an even more powerful related function, Subtotals, which—in addition to simple adding—counts, multiplies, calculates an average, selects the maximum or minimum of a set of numbers and calculates standard deviation or variance from the mean.

The syntax of this handy function is

=subtotal (function_num,ref, …)

where the first parameter, function_num, represents a numeric value corresponding to the type of calculation the user wishes to perform. For example:

1. Calculates an average.
2. Counts.
3. Counts all values.
4. Returns the maximum.
5. Returns the minimum.
6. Multiplies.
7. Calculates the standard deviation.
8. Sums.
9. Calculates the variance from a sample.
10. Calculates the variance from a population.

Once you select a function type from the above list, you must select the second parameter, ref, which is the range on which you want to perform the chosen calculation. Exhibit 4 shows the Total Assets portion of exhibit 1, which was calculated by Subtotal. At first glance, you might conclude that Sum rather than Subtotal is the most efficient way to handle those numbers, since Subtotal requires a second parameter. However, when you use Subtotal within the range of another Subtotal formula—in other words, nest one Subtotal inside another—the resulting sum is not double-counted. For example, the formula in the Total Assets cell reads =Subtotal(9, C$7:C15), a range that includes Total Current Assets. As you can see, the Total Assets Subtotal ignored the Total Current Assets Subtotal in its calculation.

AUTOFILTER

One of the least-used functions, but possibly one of the handiest, is AutoFilter. With great simplicity it can analyze the various components of data in a table. Let’s say you have a table of data that includes sales information for a computer parts distributor, listed by part type, division and region and sorted by date.

If you want to see the sales totals for a particular division, you can

• Sort the table and create a Sum function in a new column that adds up the cells containing the data for each division (option A).
• Use Excel’s Subtotal feature (option B).
• Create a PivotTable (option C).
• Use AutoFilter (option D).

Option A will work, but if you need to add a row or if you switch the sort order of the table, you have to adjust all the formulas manually to reflect the correct range. Option A is not a handy solution.

Option B requires you to sort your data according to how you want subtotals calculated or—in this example—by company division. Then, using Excel’s Subtotal function, you have to insert a row after each division showing the totals for each division. Because the totals appear within your table, you technically no longer have a table, since there are rows that do not have values in each column. Although the ability to view each division’s total directly beneath the final row for that division is somewhat useful, it does interfere with how you view the data, since Excel inserts a row for each subtotal. (This option is available from the Data menu, and is different from the Subtotal function described earlier.)

Option C is a fairly good choice. However, you can’t view the details for each division without clicking on each division to drill down to the detail, which is then displayed on a new worksheet.

That leaves us with option D— AutoFilter —the best option. Here’s why:

• The data remain intact and unchanged.
• Data details are always visible and available within the original table.
• You never have to sort the data.

To activate AutoFilter, highlight a cell in the spreadsheet ( see exhibit 5 ) to which you want to apply the function. Choose the Data menu, then choose Filter and AutoFilter. Drop-down arrows appear next to the headings of each column ( see exhibit 6 ). If you have a heading that stretches at least two rows deep, highlight the cells in the lowest row in your heading before activating AutoFilter.

If you click on any of the drop-down arrows, you can select any of the values in that particular column, as well as All, Top 10 or Custom ( see exhibit 7 ).

So if you want to see all values for the West region, click on the drop-down arrow in the Region column and select West. The table adjusts instantly to show only the West rows ( see exhibit 8 ). But even more important, note that the totals below the data table, which were created with the Subtotal function, automatically adjust to include only those visible cells in the calculation, so the displayed totals represent only the West region. If you now want to see the results for the Monitors division, click on the drop-down arrow in the Division column and select Monitors and the table adjusts along with the totals. You can apply filters to as many columns as you wish and the data will adjust accordingly.

To remove AutoFilter, choose Data, Filter and clear AutoFilter. If you just want to restore the data table with no filters, but want to keep AutoFilter active, go back to Filter and select Show All. This is easier than choosing the All option from each filter, because if you had applied several filters, you would have to remove each to show the entire table.

If you want to apply the AutoFilter function to one column only, select the cell containing the column heading and the one directly beneath it; then activate AutoFilter as described above. You will see the drop-down arrow appear only in the selected column.

If you want to show the 20 largest sales, you first must be sure you’re viewing all of the records in the table: A quick way to confirm that is to check that all the row numbers within the table are black; blue row numbers indicate that a filter is active ( see exhibit 9 ). Then select the AutoFilter arrow in the Total column and click on Top 10. Click OK. (Tip: From the Top 10 dialog box you can change the 10 to some other number.) This is a great function to assist auditors in defining strata for selecting samples.

Another advantage: If you are using Subtotal to add a range of cells and you use AutoFilter to filter your data, the Subtotal formula will reflect only the unfiltered, visible cells. The Sum function does not adjust this way.

These three functions— Conditional Formatting, Subtotal and AutoFilter —add power to your spreadsheet tools. They provide you with the ability to improve both the functionality and efficiency of your spreadsheet work.

Use them and you’ll be delighted with how they speed your work.