How to Excel at Excel: Tips and Tricks for Getting the Most Out of a CPA’s go-to Application

By:
JOEL LANZ, CPA/CITP, CFF, CISA, CISM, CISSP, CFE
Published Date:
Jul 2, 2014

As an adjunct professor, I consider it my good fortune to be in regular contact with the next generation of CPAs because it has taught me a valuable lesson: Sharing experiences and transferring knowledge can be a two-way street. Though we veteran professionals have accumulated expertise to share with aspiring and younger CPAs, we can also learn a few tricks from them as well.

One area where I’ve found this to be true is in NextGeners’ street-smart use of Excel. Like many professionals, I first learned Excel on the job. As a result, I often feel like I’m playing a game of catch-up—I continue to discover features that I wish I had learned a long time ago. On the other hand, I find that younger people, whether they’re students or staff, have an intuitive sense about this and other applications, and have identified productive ways to use Excel that I wouldn’t have thought of. 

I’ve listed a few of my favorite Excel features below. (I actually found out about them while doing statistical research to improve my fantasy baseball team!) In addition, I asked four NextGeners to share some of their favorite tips as well. These aren’t complicated tricks—I like them in part because they’re so simple; if you’re not already using these features, you can easily incorporate them into your routine. Thanks to my students at SUNY–College at Old Westbury, for their help with this column.

1. Import table data from the Web

The Internet has an endless supply of tables with useful data that can be analyzed, such as government economic research tables or financial tables used for investment decisions. When working in Excel, if you first select the “Data” tab and then hit the “From Web” tab, you’ll get a Web prompt query. Enter the Web address containing the data you wish to drop in. You’ll  be prompted to click on the tab you’re interested in (it will turn green once you have) and select “Import.” The table will then be downloaded into your spreadsheet, allowing further analysis.

2. Make conditional formatting your friend

Sometimes, you need to interpret data at a glance. For example, you might be presenting information to a client who prefers visuals to words. Located on the Home ribbon, the conditional formatting tab allows you to automatically color code conditions that appear in the data, using, for example, top-bottom analysis (e.g., top 10 percent, bottom 10 percent). You can also create bar graphs that compare one cell to a range of cells or quickly identify and color code duplicates and other relationships.

3. Filter by color

OK—it isn’t the most complex feature, but it’s a great convenience and an especially useful tool for those of us (yes, myself included) who have simple sorting needs. You assign a color to a particular type of record and then filter records based on the color, rather than traditional filter focuses.  You can find this feature on the Data>Sort ribbon.
Now, here’s some advice from my NextGen friends:

Ryan Steinberg
Student, SUNY–College at Old Westbury,
M.S. in Accounting, Class of 2015

For help in mastering Excel, visit
ExcelExposure.com, which offers a free online class designed to help the user learn the application. The site provides lectures and tutorials on everything from basic formatting to the VLOOKUP function, which retrieves information based on a unique identifier, and Pivot Tables, a data summarization tool. The lessons are in-depth and can help beginners as well as more advanced Excel users in refining their skills. This, paired with the master workbook that is provided, makes for an indispensable tool that any accounting professional can utilize.

Mica Fink
IT Auditor, CohnReznick LLP
Excel spreadsheets, especially ones with many or sometimes complex formulas, can get very confusing when you’re trying to figure out exactly where all the numbers come from. However, Excel supplies helpful tools called Trace Precedents/Dependents, which can be found on the formula auditing toolbar of the “Formulas” tab. In order to use these tools, simply highlight the cell in question and click the button. The Trace Precedents button will create arrows to every cell that is used in the specified formula, while the Trace Dependents button will point to every formula that uses that cell. If the formula uses cells on a different tab, a little box will appear. Double clicking the box will give you the option of going to those cells.

Another tool that Excel has to help with complex spreadsheets is the Name Manager. It’s located under the defined names toolbar of the “Formulas” tab. It allows you to give a name to a cell or range of cells, and then use that name in any formula referencing the cell(s). Using this, you can more easily identify the different elements that are in play in a formula, instead of trying to figure out which cell or range is being referred to.

Alyssa Contarino
Audit Intern at a top-20 firm in New York City
The Excel functions SUMIF, COUNTIF and AVERAGEIF can be used to analyze large amounts of data by grouping identical characters within a certain range. In order to do this, the criteria you’re looking for must be defined. When analyzing data, you can use SUMIF to add, COUNTIF to count the cells or AVERAGEIF to average the criteria you are looking for. The data will only be summed, counted and averaged if the criterion for the range is true.  By using this tool, the amount of time spent analyzing data is significantly reduced, and you are able to work more efficiently in Excel.

Lauren Rendina
Staff Auditor, Marks Paneth LLP

Though the DATEDIF function is undocumented in Excel, it’s one of the most useful date-related calculations formulas that can be used by an auditor in many of his or her audit procedures.

The syntax of the DATEDIF function is as follows: “=DATEDIF (Start Date, End Date, Interval).” It calculates the difference between time intervals by using a start date and end date, along with an interval of time. The interval is expressed in either “d” for days, “m” for months or “y” for years, depending on what you are trying to calculate.

The most common application of the formula for auditors is for testing the accuracy of dates in an aging report. For instance, a practitioner may use the function to determine the accuracy of an accounts receivable aging report. The purpose of using the DATEDIF function is to verify that each accounts receivable transaction has been aged correctly and is in the correct aging “bucket.”

Here’s how it works:

If the invoice date is 11/15/13 and the aging report date is 12/31/13, we would express the  DATEDIF formula interval in days, and the formula would be written as “=DATEDIF (11/15/13, 12/31/13, d).” The result of the calculation is 46 days—the number of days between the invoice date of 11/15/13 and the date the aging was run, or 12/31/13. The auditor would then verify that this invoice is included in the correct bucket, which would be 31 to 60 days outstanding.

When the DATEDIF function produces a negative amount or an error message, the auditor should review these invoices, as the error or negative amount indicates that the invoice date is after the audit period and could be a prebilling.

Other applications of the DATEDIF function include interest calculations, as well as the ability to calculate the amount of time between when products are purchased and payment is made. By using the formula, practitioners are given useful, accurate data in a short amount of time.

Joel Lanz, CPA/CITP, CFF, CISA, CISM, CISSP, CFE, is the sole proprietor of Joel Lanz, CPA P.C., and an adjunct professor at SUNY–College at Old Westbury. He is a member of the NYSSCPA’s Technology Assurance Committee and The CPA Journal Editorial Board.

Click here to see more of the latest news from the NYSSCPA.