Targeting Spreadsheet Data

How to get a bull’s-eye every time.
BY PAUL L. BORDELON

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 .

An Invitation

If you have a special how-to technology topic you would like the JofA to consider for inclusion in this series or an application shortcut you devised and would like to share with other professionals, contact Senior Editor Stanley Zarowin. His e-mail address is zarowin@mindspring.com .

SPONSORED REPORT

Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.

QUIZ

News quiz: IRS warning on cyberattacks and a change in pension rules

Once again, the IRS sounds the alarm about a threat from cyberthieves. See how much you know about this and other recent news with this short quiz.

CHECKLIST

Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.