Skip to main content

3 Excel tips to help accountants avoid common errors

Mary Ellen Biery
November 29, 2016
Read Time: 0 min

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

 

About ProfitCents

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.

 

About the Author

Mary Ellen Biery

Senior Strategist & Content Manager
Mary Ellen Biery is Senior Strategist & Content Manager at Abrigo, where she works with advisors and other experts to develop whitepapers, original research, and other resources that help financial institutions drive growth and manage risk. A former equities reporter for Dow Jones Newswires whose work has been published in

Full Bio

About Abrigo

Abrigo enables U.S. financial institutions to support their communities through technology that fights financial crime, grows loans and deposits, and optimizes risk. Abrigo's platform centralizes the institution's data, creates a digital user experience, ensures compliance, and delivers efficiency for scale and profitable growth.

Make Big Things Happen.

 

Looking for Banker’s Toolbox? You are in the Right Place!

Banker’s Toolbox is now Abrigo, giving you a single source for all your enterprise risk management needs. Use the login button here, or the link in the top navigation, to log in to Banker’s Toolbox Community Online.

Make yourself at home!

Looking for MainStreet Technologies? You are in the Right Place!

MainStreet Technologies is now Abrigo, giving you a single source for all your enterprise risk management needs. Use the contact us button here, or the link in the top navigation, to reach product support for your MST products.

Make yourself at home!

Looking for Sageworks? You are in the Right Place!

Sageworks is now Abrigo, giving you a single source for all your enterprise risk management needs. Use the login button here, or the link in the top navigation, to log in to your Sageworks products.

Make yourself at home!

Looking for Farin? You are in the Right Place!

Farin is now Abrigo, giving you a single source for all your enterprise risk management needs. Use the login button here, or the link in the top navigation, to log in to your Farin client portal.

Make yourself at home!

Abrigo acquires construction loan management solutions

Coupled with our lending suite, Construct and +Pay from BankLabs enable end-to-end automated residential/commercial construction loans.

Read the press announcement

BankLabs Logo Abrigo Logo