Using Excel to Harness the Power of Data
February 12, 2018
You’ve noticed your clients collecting massive amounts of internally and externally generated data. This wealth of data will yield significant and profitable opportunities, right? Well, maybe. The value of that data is only unlocked when it is used to make better-informed business decisions; making this happen is not easy. Data must be processed and analyzed before it can extract insights and be used in decision making. This requires technical expertise as well as significant and specific knowledge of a business. Clients typically need help in this area, and forward-thinking accountants and CPA firms can offer the solution.
Accountants are well positioned to be the problem solver in this situation, to unlock the business intelligence residing within client data. CPAs are already well versed in converting raw data into useful and actionable information. However, accountants often spend the majority of their time extracting, cleaning and formatting data and less time than they’d prefer on analytics. In fact, a recent study found that those involved in analytics spend 80 percent of their time preparing data for analysis. Further, survey respondents indicated this is the least enjoyable part of their job.
For accountants, the analytical part is easy. But how to efficiently and effectively convert all of that data into useful information; here there’s room for improvement. After all, the reason data excites is not the data itself but rather the insights that can be distilled from raw data to help clients function and create value more effectively. Fortunately, Excel can help solve data problems and increase time spent generating insights.
Know Your Data and Work Smarter
There is a tendency to get a file and begin working right away, which wastes time. Analysts can work for hours only to realize near the end that a crucial piece of information is missing or that different sets of data were confused, which translates into time and effort wasted. Instead, analysts should take 15 minutes to get familiar with a set of data. Simple steps like scrolling through the dataset, reading each header, and reviewing the high and low values, will provide a quick sense of the data and will likely uncover how to work most efficiently.
To work smarter and more productively, always begin with the outcome in mind. Quickly researching a solution can save hours or even days. Whatever the problem, someone has likely already solved it and posted it online whether in a forum or a short article. Remember, it’s not about knowing every formula or trick, but rather being able to research a solution and then apply it.
Know Your Tools
Most business analysts are familiar with five or six formulas that they consistently utilize in every Excel situation. Learning a few more formulas can pay large dividends. For example, did you know you could sum a set of values based on multiple conditions with the SUMIFS formula? This formula saves time filtering columns based on each condition and then adding together the values. Commit to learning one new formula a week. It will change your Excel life for the better.
Keep it Simple
When you spin your wheels trying to understand complex formulas and workbook structure, productivity is lost. For anything you are trying to accomplish in Excel, someone has likely done it before. You can ask colleagues, or do a quick search online; it’s amazing what you can find. Identify the simplest solution and the one users of your files can easily utilize. A good rule of thumb is that every formula or workbook should be explainable, portable and easy to maintain.
These simple tips can save time and enhance your effectiveness in working with large amounts of data. You already have Excel at your fingertips, and knowing these little tricks can have a huge payoff.
Brian Fitz-Gerald, CPA, is the founder and CEO of Akseshen LLC, a boutique data management and analytics company helping accounting firms adopt data-driven solutions that increase profitability and efficiency in both back office and client facing operations.
Read more by Brian Fitz-Gerald:
Learn more from Brian Fitz-Gerald:
This article appeared in the Jarnuary/February 2018 issue of New Jersey CPA magazine. Read the full issue.