By Russell Kommer, founder of ESW.
When was the last time you reviewed the data you use to make decisions about your company's operations? If anyone in your organization works with Microsoft Excel, take a look now. Your numbers might not be as accurate as you think.
According to research shared by F1F9, a financial modeling services firm, in large companies, at least 50 percent of spreadsheets have material defects. As founder of a nationwide provider of custom Excel programming and Excel training, I see these issues -- and their impact on businesses -- all the time. Unfortunately, they often go unnoticed until it's too late.
In 2003, a spreadsheet slip up at Fannie Mae resulted in a $1.136 billion error in total shareholder equity. Manual copy and pasting, in part, cost JPMorgan $6 billion in trading losses during the London Whale incident. During the 2012 London Olympics, 10,000 tickets were oversold for a synchronized swimming event because of a single keystroke mistake.
Challenges arise in Excel when you scale to larger datasets. This multiplies the odds of mistakes, especially when several employees use the same spreadsheet. In addition, few companies have review processes in place. So they typically miss errors found in an editing stage because first drafts quickly become final versions.
Here are three overlooked ways to use Excel to its full potential while also avoiding costly mistakes:
Automate Tasks That Require Manual Entry
Employees who use Excel for at least 30 minutes a day are invested in adding or reviewing data. Regardless of their role, they rely on accurate and useful information. Yet work performed on a repeated basis has a tendency to turn inconsistent. In addition to data entry errors, you run the risk of faulty formulas or calculations becoming outdated.
One way to avoid these issues is to create automated tasks within the software. Everything from creating PDFs and sending emails to interacting with data in other programs is possible -- and you can automate it all. In fact, you can automate just about any repetitive task in Excel, including client proposals, billing and payment reports and investment analysis.
Start this process by simply creating a macro in Excel. Once in the software, go to View > Macros > Record Macro. Then go through a process you'd like to automate. Once complete, you'll have an Excel recording of your routine steps that you can replay with just the press of a button or keyboard shortcut.
Employ IT Staff Specifically Trained in Excel
Most IT departments focus on desktop support. They're not trained or certified in Excel, so figuring out fixes or workarounds for Excel requests can become a long, drawn-out process. This downtime puts your staff at a disadvantage, decreases their productivity and, ultimately, costs money.
So if you use Excel extensively in your organization, consider hiring a support staff specially trained in the software. At the very least, map out your support process so you can identify bottlenecks. Then bring in an expert to diagnose where you can make the process more efficient. You should also consider ongoing training specific to Excel. The extra learning will help cut down on your support requests while allowing your employees to use Excel more efficiently.
Make Your Data Accessible From One Centralized Location
Most companies manage multiple sources of data, which is being used to some extent. Yet how are you maximizing this data's potential? Could it be better correlated? To find out, create a single repository to serve as the foundation for all your business data. That way, all the isolated spreadsheets you have over shared drives can "live" in one location, making your information easier to analyze.
Now, once all your data in a central spot, imagine picking up on a new key concept from the information that gives you a competitive edge. Maybe it's a sales opportunity, an operational process improvement or even a brand new offering. What would this do for your business? Often, we see these opportunities multiply and lead to new ideas and cross-correlations. So the net effect of just one great find can have a ripple effect across your entire organization. Review your processes regularly to ensure everything remains error-free and efficient.
Russell Kommer is the founder of ESW, experts in Microsoft Excel, Database SharePoint & Office365 custom solutions for businesses and corporations.