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. | 
ave you ever needed to analyze a specific group or range of data in a complex spreadsheet with many rows and columns? For example, you may want to review a depreciation schedule to determine whether your current-year asset additions were assigned the appropriate tax life. If you have numerous prior-year assets commingled with your current-year additions, it may take you hours scrolling through the depreciation schedule and searching for current-year asset additions—a tedious chore that may lead to inaccurate results. Excel provides a useful tool that will enable you—with minimal effort—to narrow your search and then gather all the targeted material together.
For demonstration purposes, set up a simple depreciation schedule as shown in exhibit 1. To download the file, go to http://www.aicpa.org/download/pubs/jofa/exhibit1.xls . | EXHIBIT 1: ABC Co. and Subsidiaries | 2000 Tax Depreciation Schedule | Company | Account | Description | Placed
in
Service | Life | Cost | Beginning
Accum.
Deprec. | Current
Year Deprec. | Ending
Accum.
Deprec. | Net Basis | | Able Co. | 1200 | Waco Software | 1/1/99 | 3 | 45,000.00 | 14,998.50 | 20,002.50 | 35,001.00 | 9,999.00 | | Bradley Co. | 1200 | Pineville Software | 3/1/99 | 3 | 25,000.00 | 8,332.50 | 11,112.50 | 19,445.00 | 5,555.00 | | Bradley Co. | 1200 | Houston Software | 7/1/99 | 3 | 10,000.00 | 3,333.00 | 4,445.00 | 7,778.00 | 2,222.00 | | Bradley Co. | 1200 | Lufkin Software | 5/1/00 | 3 | 80,000.00 | — | 26,664.00 | 26,664.00 | 53,336.00 | | Bradley Co. | 1200 | Dallas Software | 8/1/00 | 3 | 5,000.00 | — | 1,666.50 | 1,666.50 | 3,333.50 | | Laura Inc. | 1200 | Ruston Software | 2/1/98 | 3 | 23,000.00 | 17,889.40 | 3,406.30 | 21,295.70 | 1,704.30 | | Laura Inc. | 1200 | Monroe Software | 3/1/99 | 3 | 18,000.00 | 5,999.40 | 8,001.00 | 14,000.40 | 3,999.60 | | | | | | | 206,000.00 | 50,552.80 | 75,297.80 | 125,850.60 | 80,149.40 | | | | | | | | | | | | | | Able Co. | 1400 | Copier | 1/1/99 | 5 | 15,000.00 | 3,000.00 | 4,800.00 | 7,800.00 | 7,200.00 | | Able Co. | 1400 | Computer | 1/1/99 | 5 | 1,500.00 | 300.00 | 480.00 | 780.00 | 720.00 | | Able Co. | 1400 | Monitor | 1/1/99 | 5 | 300.00 | 60.00 | 96.00 | 156.00 | 144.00 | | Bradley Co. | 1400 | Computer | 3/1/99 | 5 | 1,650.00 | 330.00 | 528.00 | 858.00 | 792.00 | | Bradley Co. | 1400 | Monitor | 3/1/99 | 5 | 350.00 | 70.00 | 112.00 | 182.00 | 168.00 | | Bradley Co. | 1400 | Computer | 4/1/00 | 5 | 1,650.00 | — | 330.00 | 330.00 | 1,320.00 | | Bradley Co. | 1400 | Monitor | 4/1/00 | 5 | 350.00 | — | 70.00 | 70.00 | 280.00 | | Bradley Co. | 1400 | Printer | 4/1/00 | 5 | 250.00 | — | 50.00 | 50.00 | 200.00 | | Bradley Co. | 1400 | Printer | 3/1/99 | 5 | 250.00 | 50.00 | 80.00 | 130.00 | 120.00 | | Laura Inc. | 1400 | Computer | 2/1/98 | 5 | 1,200.00 | 624.00 | 230.40 | 854.40 | 345.60 | | Laura Inc. | 1400 | Monitor | 2/1/98 | 5 | 450.00 | 234.00 | 86.40 | 320.40 | 129.60 | | Laura Inc. | 1400 | Printer | 2/1/98 | 5 | 200.00 | 104.00 | 38.40 | 142.40 | 57.60 | | | | | | | 23,150.00 | 4,772.00 | 6,901.20 | 11,673.20 | 11,476.80 | | | | | | | | | | | | | | Able Co. | 1600 | Desk & Chair | 1/1/99 | 7 | 450.00 | 64.31 | 110.21 | 174.51 | 275.49 | | Able Co. | 1600 | Lamps | 1/1/99 | 7 | 150.00 | 21.44 | 36.74 | 58.17 | 91.83 | | Able Co. | 1600 | Filing Cabinets | 1/1/99 | 7 | 180.00 | 25.72 | 44.08 | 69.80 | 110.20 | | Able Co. | 1600 | Sofa | 1/1/99 | 7 | 400.00 | 57.16 | 97.96 | 155.12 | 244.88 | | Bradley Co. | 1600 | Desk & Chair | 4/1/00 | 7 | 475.00 | — | 67.88 | 67.88 | 407.12 | | Bradley Co. | 1600 | CEO Desk & Chair | 1/1/98 | 7 | 600.00 | 232.68 | 104.94 | 337.62 | 262.38 | | Bradley Co. | 1600 | VP Desk & Chair | 1/1/98 | 7 | 550.00 | 213.29 | 96.20 | 309.49 | 240.52 | | Bradley Co. | 1600 | Lamps | 1/1/98 | 7 | 300.00 | 116.34 | 52.47 | 168.81 | 131.19 | | Bradley Co. | 1600 | Filing Cabinets | 1/1/98 | 7 | 300.00 | 116.34 | 52.47 | 168.81 | 131.19 | | Bradley Co. | 1600 | Sofa – Lobby | 2/1/99 | 7 | 400.00 | 57.16 | 97.96 | 155.12 | 244.88 | | Bradley Co. | 1600 | Table – Board Room | 2/1/98 | 7 | 1,500.00 | 581.70 | 262.35 | 844.05 | 655.95 | | Bradley Co. | 1600 | Chairs – Board Room | 2/1/98 | 7 | 700.00 | 271.46 | 122.43 | 393.89 | 306.11 | | Laura Inc. | 1600 | Desk & Chair | 1/1/98 | 7 | 350.00 | 135.73 | 61.22 | 196.95 | 153.06 | | Laura Inc. | 1600 | Filing Cabinets | 1/1/98 | 7 | 125.00 | 48.48 | 21.86 | 70.34 | 54.66 | | Laura Inc. | 1600 | Lamps | 1/1/98 | 7 | 100.00 | 38.78 | 17.49 | 56.27 | 43.73 | | | | | | | 6,580.00 | 1,980.58 | 1,246.24 | 3,226.82 | 3,353.18 | | | | | | | | | | | | | | Company total | | | | 235,730.00 | 57,305.38 | 83,445.24 | 140,750.62 | 94,979.38 | | Notice that the spreadsheet contains data on three companies; each has different asset classes that were placed in service over a period of three years. Imagine what you’d have to do to manually separate out the data. We’re going to get the job done in minutes by using Excel’s AutoFilter function. Open exhibit 1 and highlight all the cells from A6 to J48. Then go to the toolbar and click on Data, Filter and AutoFilter . At that point arrows will appear to the right of all the column titles in row 6 (see exhibit 2, below). | EXHIBIT 2 |  | If you click on any of the arrows, a drop-down box will appear next to the column description (exhibit 3, below). The box contains the filtering options for that column. For example, among your choices are your three target companies: Able Co., Bradley Co. and Laura Inc.
| EXHIBIT 3 | | EXHIBIT 4 |  | |  | Clicking on any of the three companies will filter for that choice and display all the data for it. You also will be able to select the range of data you want to view exclusively. The filtering does not delete data nor does it permanently alter your spreadsheet. Assume you want to view the fixed asset additions for Bradley for the year 2000. Click on the drop-down box that is next to the Company in cell A6 and select Bradley (see exhibit 4, above). You should now see only the assets that are assigned to Bradley (see exhibit 5, below). | EXHIBIT 5 |  | The drop-down box in the Company column changed from black to blue, indicating the filtered field. Although we now see Bradley’s assets, our objective for this example is to view only year 2000 asset additions. Therefore, one more step is required to complete our task. Next, click on the drop-down box next to the description Placed in Service in cell D6 (see exhibit 6, below). | EXHIBIT 6 | | EXHIBIT 7 |  | |  | When you select Custom , the Custom AutoFilter screen appears (see exhibit 7, above). Now you can customize AutoFilter to display the specific range of dates you want to view. Begin by clicking on the drop-down box in the upper-left quadrant and select is greater than or equal to (see exhibit 8, below).
| EXHIBIT 8 |  | Then click on the drop-down box in the upper-right quadrant and select 4/1/00 . With those two instructions you are asking AutoFilter to view all rows with assets placed in service on or after April 1, 2000. Why does AutoFilter specify 4/1/00? That’s the first date that Bradley placed an asset in service for 2000. Next, click on OK . Your spreadsheet now should show only year 2000 asset additions for Bradley as shown in exhibit 9, below. | EXHIBIT 9 |  | Once you’ve finished your analysis, you can return the depreciation schedule to its original view of all companies and assets by clicking on the drop-down boxes in the Company and Placed in Service columns and then select All . As you can see, the AutoFilter command enables you to effectively and efficiently view a specified range of data without permanently altering the existing spreadsheet. You no longer have to waste time manually scrolling through numerous spreadsheet rows and columns trying to find a specific piece or range of information. The answer is now only a few mouse clicks away. 
PAUL L. BORDELON, CPA, MPA, is a senior tax accountant at Cleco Corp. in Pineville, Louisiana. His e-mail address is paul.bordelon@cleco.com . |
