Go Beyond Excel to Analyze Your Data
Overview | What you'll learn:
Excel has its uses. But it's not always the best fit for the analytics job. Whether the data is out of date, or multiple versions of the same file are circulating among employees, limitations often arise.
Read this whitepaper to understand where Excel works best, and where you need a more robust analytics tool to save time, money, and frustration.
We've also pulled out the first several pages of the whitepaper for you to read. Download the PDF via the "Download Whitepaper" link on the left to read the rest.
There’s no doubt that Excel has been one of the tools of choice for analysis and reporting. Users love the control they have, the rapid and easy creation of models and graphics, and the ability to be both independent and self-reliant. With Excel, you’re in the driver’s seat. But just like driving a sports car when what you need is an SUV, using Excel for analysis means that you’re over-served in some ways and in others, much underserved. Out-of-date data (and no easy way to update), error-prone analytics, multiple versions of the same file circulating among colleagues, too much manual involvement—these are just a few of the frustrations commonly associated with Excel. And what of the time wasted developing your analysis instead of spending time actually analyzing?
Despite these issues, many departments—and entire businesses—run on Excel, swearing by it one minute and swearing at it the next.
There are better ways, from simple improvements you can make yourself to large scale, enterprise-oriented changes. This paper focuses primarily on improvements you can make to improve how you conduct your analytics without sacrificing the advantages of Excel.
1. Eliminate non-essential analyses.
You can improve the quality of all of your analytics if you stop doing so many reports and presentations and just focus on the ones you and your colleagues need to make decisions. Inventory the various analytic reports you and your team maintain and then uncover which ones matter and how often they require updating. You’ll have a much better idea of the metrics that matter and the value that’s being provided to the team.
As a bonus, not only will you reduce your team’s workload and focus on the most important tasks but you’ll probably also eliminate a lot of previously unidentified duplicated efforts.
2. Be smarter about how you use Excel.
We’re all so busy doing the same thing over and over that sometimes we forget to stop and ask, “Is there a slightly better way?” And, unless you’re a super power user, when it comes to Excel, there probably is.
- Use the online help! Excel has some great examples. But don’t just read them. Do them. Create a spreadsheet with the same data Microsoft shows in its help and replicate the help files. Doing will help you more than reading.
- Look for help. For the vast majority of data problems you have, there is probably someone with the exact same problem who has described the answer somewhere on the Internet. Search for “pivot tables”, “Excel add ins”, etc. You’ll be amazed at how much there is out there.
- Learn the “analysis essentials.” Tasks and features like “lookup” functions, pivot-tables, the date functions, find/replace, subtotals, named ranges and keyboard shortcuts can help speed your analysis.
- Evaluate yourself and your team for the right technical fit. For example, you may be able to use the “ODBC” driver (Open Database Connectivity standard) to connect to data warehouses and centralized databases. This can be effective but the requirements are steep: 1) you must have access; 2) you must know what you’re looking at and 3) you need to be connecting to or pulling fewer than 1 million rows (for Excel 2010.) So don’t expect your analysts to turn into Excel macro experts or database programmers. If you need that kind of help, look to add those skills via part-time contractors or developers.
3. Be obsessive about the details
The data challenge today isn’t that people don’t have the information. It’s the way they get at it, cleanse it and ensure its integrity. Because everyone does these tasks differently, there are often multiple “versions of the truth.” Excel only makes this worse. When you add in the logical errors made in spreadsheets, we’re talking about a lot of potential incorrect or misleading information. In fact, there are industry estimates that 90% of spreadsheets contain data or logical errors. Quality checking and clear communication are crucial.
Obviously, no one’s perfect so never assume you cleansed your data, wrote your formula or sorted your data right every time. Do a lot of quality checking along the way. You have to provide accurate data, and you must go out of your way to explain where you got it, how it was calculated and why it might differ from the reports they’ve been seeing.
Be careful and descriptive about naming, including column names, file names and version numbers. Good, clear documentation would be ideal. More attentiveness to naming and documentation is not going to stop the problems but it may reduce them.
Don’t overestimate what other people know about using Excel. For example, Excel does have some file management and change tracking capabilities. Do you know what percent of people know how to use and comply with them? It’s not many. You can decide you want to be the one to educate the world or you can decide to do things in very clear, simple ways so that you get back the input you need.