This will be you as an analyst — headphones on, baffled by some spreadsheet your coworker just sent.
This post is part of the Hired with Excel series. Read the previous post on the advantages of Excel here.
The Bad
Excel is dominant, but not invincible. Here are the three biggest downsides you should consider as an analyst.
Big data is getting too big for it.
Excel was set up for desktop computing. Most data sets have become so big that they must be stored on a bigger server — hence, the “big data” movement. It is very hard to load such large sets into Excel. Microsoft has made Excel more flexible toward large data sets, but there is a way to go. I guarantee you will overload some spreadsheets as an analyst. So remember to save frequently and break things apart when possible.
Collaboration is difficult.
On a big spreadsheet with many users, it is difficult to track changes. You can easily overwrite work, modify formulas, and change numbers. For most purposes, only one user is allowed write access to a file at a time. This means that if your boss or coworker is in the file, you can’t make any changes.
It Can be Difficult to Detect Errors.
This is the most serious Excel problem and the one we attempt to improve. Remember “Spreadsheetgate?” It turned out that a paper that had helped a team of economists win the Nobel Prize contained spreadsheet errors. If Nobel Prize winners have Excel issues, then you should be on the alert.
While Excel is great for off-the-cuff data analysis, it’s really hard to follow along with calculations. One miskey, one overlooked step, and the final answer is going to be wrong. It is difficult to trace back the steps. Excel is not good for complex, multi-step calculations because there is no good way to check accuracy. Yet this is precisely what Excel has become for many analysts.
My advice is to avoid hard-coding and summations as much as possible. This series will show you to look up, summarize, and report data in ways that avoid error-prone hard-coding.
When reading this book, have these questions in mind: How can I prevent errors with this tool? And more importantl, how can I use this to get and succeed in a job?
Now that you understand what employers are looking for and what attitude is needed, let’s learn the most important Excel skills for an entry-level analyst.
Matthew R Walje
Great post. Like you say, some of these “bads” can be easily avoided by properly using Excel. In the Reinhart-Rogoff example, for instance, if the economists had used tables instead of ranges, as you suggest, the error would likely have been entirely avoided. Incorporating table functions into formulas is one of the quickest ways to avoid basic errors. Likewise, with the Too Big Data issue, if an analyst uses the PowerQuery and PowerPivot functions to add data to their data models, rather than the standard Excel functions, they massively increase the horsepower of their analytical models without requiring use of a separate database and analysis system.