I have been working in public accounting for seven years now and I am still amazed at how much more there is to learn about Microsoft Excel. However, I haven't always been a self-proclaimed Excel nerd. While I used Excel in various college class projects, it played only a supporting role, helping compile the financial statements for a fictitious company or putting together a far-fetched imaginary budget for a new invention or small business idea.
I'm happy to say that my Excel skills have since expanded beyond formatting T-accounts to make my homework assignments more aesthetically pleasing, but it wasn’t until I began to use it every day that Excel went from supporting role to center stage.
When people ask me how I came to be so good at Excel, I answer honestly - it started out of pure laziness. It took only a couple instances of being tasked with what seemed like an endless undertaking before I had the sense to Google a faster way to complete the assignment. One small formula transformed hours of work into only a 15 minute exercise, and from then on, I was hooked.
From staff all the way up to director, Excel is utilized constantly in public accounting. Clients provide us trial balances, general ledgers, account reconciliations, and other source documents in Excel. Excel enables us to quickly foot each report received from the client, go through countless lines of data reflecting the company's activity, and analyze changes in accounts by identifying recurring or infrequent transactions. All of our workpapers are typically maintained in Excel spreadsheets (especially on jobs I work on); we design the samples, make selections, and complete the testing all in an Excel workpaper. We perform variance and ratio analysis, build the financial statements, and even maintain some memos in Excel.
Excel is what you make of it. Say you have a list of employees, and a payroll report containing information on that list of employees. If you want to CTRL + F your way through that whole payroll report, typing out the Gross Pay for each employee you selected next to each person's name, that's your call. However, if you're lazy like me, you'll use a VLOOKUP formula and pull in the Gross Pay, Date of Hire, and Hourly Rate for each employee, all by using the same formula, in a matter of minutes.
Along those same lines, if you are content filtering and sorting through 16,000 lines of general ledger detail to find one unusual transaction that will explain the change in account balance from prior year to current year, again, that's up to you. But as an alternative, you could use an AVERAGEIFS formula to see which transactions are above average, or a COUNTIFS formula to see how many times that vendor was used during each month in prior vs. current year, helping you find the outlier much quicker. The faster method allows you the additional time to have an informed discussion with your client about that transaction, rather than blindly asking why an account increased or decreased and hoping the explanation makes sense.
As with everything in life, you never stop learning with Excel. I'm still discovering new formulas and working on learning macros so I can eventually cut down the "matter of minutes" to mere seconds for some of the more menial tasks I find myself performing. You should always keep challenging yourself, and continually seek out new ways to more efficiently complete even the smallest of assignments - that's how you learn. It's the difference between coming up with a formula to recalculate depreciation expense for one fixed asset, and coming up with a formula that can be copied and used for each and every asset on the fixed asset listing, factoring in different acquisition and disposal dates, useful lives, and varying methods of depreciation dependent upon the asset class (we call this a "Nested IF" formula).
Don't just stop when you learn how to sort and manually scan a list of TB accounts for duplicate account numbers. Learn how to conditionally format those account numbers so they appear on your spreadsheet in an obnoxious pink color that stands out. Excel is smart, but it needs to be told what to do. Learn how to tell it what to do and you'll be more efficient with your work, and feel like a boss.
Moving up the ladder from staff to senior, and so on, you are given more and more responsibilities. Each of these responsibilities takes up time, which is why it's important to be as efficient as possible with that time. It's also important to develop your skills. We are not auditing a client's Excel spreadsheet, we're auditing the content, and what it represents. But using the best tools available to you is what makes a good auditor, so use the most advanced formulas to audit the content of that spreadsheet. If you don't, the spreadsheet might as well be a PDF, and who likes PDFs? At the end of the day, the slow method may still get you the same results for your audit procedures as the fast method, but would you rather have that end of the day be at 6pm, or 11pm?
Liz McAleer is a manager in Kreischer Miller’s Audit & Accounting group. She is a member of the Employee Benefit Plan industry group and works on various retail, distribution, and manufacturing clients. Liz graduated from the George Washington University and, after working in public accounting for two busy seasons, joined Kreischer Miller in 2013. When she’s not dreaming about spreadsheets, Liz enjoys going to Phillies games and spending time with her fiancé and dog. Contact Liz at Email.
Also check out: