3 Excel tips to help accountants avoid common errors
In a recent Sageworks webinar, 3 Excel Tips for Accountants to Avoid Common Errors, Jeff Lenning, CPA and Author of Excel University, described key tips that can help accountants avoid common Excel errors and get their work done more efficiently. Lenning’s webinar is one of many resources Sageworks provides to accountants and valuation professionals regarding best practices for business advisory services and audit work.
Tip 1: SUM vs. SUBTOTAL
Lenning began by asking attendees, “Which Excel function is used in more of your workbooks than any other?” According to Lenning, the SUM function is the number one, most often used function of all time, but suggests that for accountants, “SUM” should not be the go-to function for adding up values on reports. Why? The SUM function requires a “pick and choose method” for each cell, and is not efficient in recurring use workbooks. Errors are likely to occur, and the only way to fix them is to go back and re-write the formula each time.
Lenning demonstrated his point by calculating totals versus the grand total in assets on a balance sheet when new data was entered in additional financial reporting periods. Lenning noted, “By using the SUM function, you are always left picking and choosing specific cells, and it will always be slower than using the “SUBTOTAL” function that allows you to tell the function what kind of math to apply to an entire range at once. For a full narrative on the benefits of avoiding use of the SUM function in recurring use workbooks, Lenning suggests his article “SUM No More,” from the Excel University website.
Tip 2: Identify duplicates
Identifying duplicates is another area where Lenning stressed that accountants can benefit from leveraging specific Excel features. A standard practice is to sort, scan and check manually for duplicate values. This may work well for a small number of items, but is error prone and can be overwhelming as data entries grow. Lenning suggests using conditional formatting, highlighting cell rules and then applying a filter. When summing filtered results, Lenning again stressed using the SUBTOTAL function, which can be applied to the entire range and will exclude those cells that have been hidden by the filter. Lenning’s blog post “Sum by Color” recaps and explains each step in detail.
Tip 3: New transactions
When adding new transactions into a workbook, Lenning demonstrated that Excel does not rewrite your formula to include new items in a range. Instead of re-writing the formula for those new items with the SUM function, Lenning suggested using the new table features introduced in Excel 2007, where tables will automatically expand to include new data and “total rows.”
Altogether, Lenning cites Excel proficiency as his secret weapon, and encourages accountants to increase their own Excel proficiency as a practical means for getting more work done with less effort and time.
Initially, as Lenning searched for a better way to do things, he found that he was unable to locate a source with any type of structured curriculum designed for accountants and their data needs. Lenning eventually formed Excel University as resource to fill that gap and provide a progressive learning experience in Excel.
To learn more about these Excel tips, watch the webinar: 3 Excel Tips for Accountants to Avoid Common Errors.
Additional Resources for Accountants
Whitepaper: Help Business Clients Win Loans
eBook: Audit & Review Best Practices and Pain Points
ProfitCents helps thousands of accounting firms and business consultants acquire and retain clients through differentiated, high-value services. Build or strengthen an advisory practice, leverage robust benchmarking data or streamline the audit process. Learn more by watching a brief overview video.