A Finance Fitness Resolution for the New Year: Do Your Heavy Lifting in Excel
“Stop using Excel, finance chiefs tell staffs” was the title of a recent Wall Street Journal article that “stirred up a tempest” among readers according to its follow-up piece (tellingly titled “Finance pros say you’ll have to pry Excel out of their cold, dead hands”). Among other claims, the initial article said Microsoft Excel “hasn’t kept up with the demands of contemporary corporate finance units,” but is this really the case? We spoke with Bill “MrExcel” Jelen, a former financial analyst and current Microsoft Certified trainer, about whether some of those tempest-stirring statements are valid.
To those who think Excel hasn’t kept up with the demands of modern corporate finance needs, what would you say in Excel’s defense?
In Microsoft Office 365, Excel can definitely do some of the “heavy lifting” for finance teams that the analyst quoted in the WSJ implied it can’t. About 150 million of the 700 million Excel users worldwide have access to this version, but they aren’t always aware of its power.
The Excel in Office 365, whether desktop or cloud (about 99 percent are desktop, however), can continuously receive new, powerful functionality from Microsoft as soon as developers dream it up, as opposed to waiting for the three-year release cycle. However, Microsoft will sometimes add these features to the ribbon without much fanfare, so people aren’t readily aware of them. Also, many IT departments are risk averse about upgrading to higher versions of Excel, so users don’t have access to more advanced features.
The WSJ noted how one company purchased a new cloud-based system to keep finance planners from importing and manipulating data from various systems in Excel. Could they have actually accomplished this in Excel?
As of Excel 2010, you can combine data from multiple data sets, including accounting and ERP systems, within Excel using a feature formerly called Power Query, and now called “Get & Transform”. It does seem that Excel users haven’t yet really embraced this feature, perhaps because they don’t know about it or because it would require that they know the underlying structure of the database they were trying to access and query. Some Excel add-ins integrate directly with the database or application to mask the requirement for such knowledge and also access the data through a secure connection (like https), whereas Get & Transform uses an open database connection (ODBC). Get & Transform, however, can be particularly useful for quickly pulling in .csv, text, or other Excel files and preparing the data for analysis, such as removing duplicate headers.
With either approach, you just open the Excel or Excel add-in file and click refresh to access the latest and greatest data from an enterprise system. However, if you aren’t using an approach like these, pulling data from systems into Excel can take days, as the article points out.
How does using advanced Excel functionality or add-ins help with concerns that CFOs and others have about spreadsheet errors?
With Get & Transform or an Excel add-in, you pull data into Excel from your single source of truth, such as an ERP or other enterprise system, where you can manipulate it for presentation and analyze it. These two approaches also create an audit trail of what’s happened and assure that you aren’t working from old or inaccurate information. There’s also a feature that was added for Excel 2013 called “Inquire”, which helps you identify risky areas in a spreadsheet.
What was the most troubling aspect to you in the initial “Stop using Excel” article?
For one, the article made it sound like the CFO at one company simply left Excel because it didn’t offer co-authoring, where multiple people can safely edit the same workbook at the same time. Excel, as of July 2017, does support this. Another concern was that this same CEO was trying to collect data from 415 restaurants across the country in Excel, which is really something you should do in an enterprise system. So, he wasn’t getting rid of Excel completely; he was just getting rid of Excel for that one very unwieldy piece of the process.
That said, I’m sure that collecting the restaurant data in Excel was initially a stopgap measure that became perpetuated. I can’t argue with moving the data collection part of the process to another system, but I hope the company comes back to analyzing and presenting data in Excel, which is absolutely the right thing to do. But as I said when contacted by the WSJ for the follow-up piece, “No one should be doing anything 100% in Excel.”
Are there any other features coming down the road that address the heavy lifting aspects needed from Excel by finance teams? How can companies best utilize them?
In addition to features like the ones I discussed in a blog last year and those I continually update on my website, Microsoft introduces new functionality to Get & Transform each month, which Office 365 users will get. On the back end, once you have your data in Excel, Microsoft is also investing in Power BI tools to assist with distribution, letting you share your Excel data in some really nice interactive visualizations on your mobile devices.