I don't think that Excel was ever in need to be defended. It's one of the best analytic tools for tasks of proper scope. It was also the first tool that we had to learn and master in bioinformatics class before writing even a single line of code.
Maybe I'm in the wrong circles but many of the "hardcore" quant folk will belittle it. That's why I decided to write something up. I don't think Excel's the right tool for every job but it's a lot more powerful than many people give it credit for.
Haha - when I was a quant I used it all the time too. We had Perl jobs that would generate Excel reports that would then be distributed to the various PMs so that they could look into the details.
I work in aeronautics and everyone who works on a computer maintains some Excel spreadsheets. It's everywhere but so few people know how to use formula, pivot tables, etc, that it has become a real competitive advantage to master them.
It's fine if you want to keep a small budget or a quick bit of work with numbers, but it's awful for dealing even modest amounts of data and here's why:
Nothing you do is captured and everything is destructive.
Any time someone edits their spreadsheet they are irreparably altering the data without real documentation. Excel encourages moving, shaping, dragging, and futzing that may be easier, but ultimately can cause major problems because there's no audit trail. Ever try and replicate something modestly sophisticated that was done in Excel? Good freaking luck.
There's nothing wrong with Excel for some basic graphs and calculations. The problem is when people start to use Excel as a data base or as a serious tool for analytics (like some of those business folks).
The danger I've seen with Excel is that people who start using it have a very hard time letting go. At a prior role, I tried rewriting a ton of Excel reports into SQL queries that would run much faster and would be maintained to deal with various schema changes. Unfortunately, the people who were currently putting them together in Excel did not like that loss of control.
Do you think Excel with built in version control would help in solving the problem? I remember reading about some company that was trying to do that but no idea where they are now.
What's needed is a "serious tool" that's as easy for people to get into and understand as Excel is.
Excel is as ubiquitous as it is because the mental model of a spreadsheet is very simple and easy to wrap one's brain around. Here's a grid of cells, type stuff into them. Boom, you're an Excel user. (It can do a lot more than that, of course, but many many many people never realize that and still get lots of value out of it. Which is kind of remarkable, really.)
Proper databases are much more powerful, but none of them present the user with an interface whose basic metaphors are as immediately obvious as Excel's. You have to learn about tables, data types, relations, etc. to get anything done in them. Most of that stuff comes up with Excel too, but you don't need to know it to get started. The Excel user's first baby steps are much smaller than the database user's are.
Given a choice between a flawed tool that's immediately understandable and a perfect tool that requires learning, people will always choose the flawed-but-understandable tool. So they go with Excel.
I don't think we should necessarily aim to make "serious tools" easy for people to get into. I am not saying that the UI shouldn't be intuitive, but rather that making something 'too easy' results in low barriers to entry and you end up with the wrong types of people using that tool. Excel is very useful (I use it much in the same way the OP does), but it has it's limitations. Because it is so easy to get into, it's easy for individuals lacking the proper experience to over extend themselves.
I think much of the explosion in data science jobs these last few years isn't really about "big data", but rather the fact that your typical corporate analyst does not have the experience to tackle mid range data analysis jobs because the only tool they have under their belts is Excel. What really is needed is a larger number of people with experience in statistics, scientific computing (Python, R etc..), and database systems. The current tools we have are fine, they just need to be used properly (well, we should always strive to improve our tools, but you get the idea).
Every tool has a scope that it has to be used on. If you use Excel in situations where you need team collaborations, stability, or other really advanced stuff, you're doomed.
However, if you just need to check a quick hypothethis, or mock up a solution, it's really the best tool for the job.
Excel can be an excellent tool - I think the problem is in creating something that buries key calculations or is heavily prone to human error so major mistakes are made. Some of these things (recently reported in the news) move markets and made / lost billions. You should definitely check your math!
Tableau is great for visualizations but if you want to do anything on your own servers it is prohibitively expensive and there are other alternatives out there. Not sure it provides the same capability as Excel for analysis.
The finance industry definitely has love hate relationship with Excel. We're trying to encourage moving Excel style analysis into Python and have developed a library specifically to facilitate that, meanwhile we make sure our application platforms have first class Excel integration and export functionality.
To my mind the key point in the article is lack of facilities for code review of Excel spreadsheets. Yes there are tools to help with that, but they're not widely used. If our developers routinely pushed their development code directly to production without proper review and testing, someone (probably several people) would get fired on the spot, yet important business decisions get made based on unreviewed Excel spreadsheets all the time.
Sarbane Oxley will take a big bite out of MS Office licenses. The law is 10 years old, but there was a lot of lock in in 2003.
The JP whale provides a great just-so story for regulators who want to take powerful, flexible tools like excel out of analysts hands. Give it another 10 years, Excel will not have the ubiquity it enjoys today in financial institutions.
My problem with excel is that it is not conducive to making well-documented systems. The formulas are hidden and there's no way to comment them. The ability to name your variables is clumsy and pushed away from the main UI so most users are unaware of it and tend to use anonymous variables. The default UI for creating formulas binds them to a fixed range of cells instead of the entire column, which is absurd.
It's intensely powerful (my boss can do spectacular things with pivot tables and teaches all the business people to do this as a means to doing their own analysis of our reports) but the high-power features have a positively nasty UI compared to the corresponding SQL query.
I'd love to see an Excel designed-from-scratch that scratches the same itches of approachability and user-friendliness but with an awareness of Excel's failings and without obsessing over the spreadsheet metaphor. Something in-between Excel and Access.
Excel is super powerful, but I loathe that power. It leads to people doing a ton of amazing, great work in Excel that's super impressive... that no on else can replicate.
If you're really smart about it, Excel is one of the most remarkable tools for analysis ever devised. The problem is virtually everyone who is smart about it can use different better tools that are more suited to their purpose.
> Excel is super powerful, but I loathe that power. It leads to people doing a ton of amazing, great work in Excel that's super impressive... that no on else can replicate.
More than "replicate", I think the problem is no one else -- and, with enough time and complexity, not even the originator -- can maintain it, which results in Excel often being a vehicle for low-initiation-cost and rapidly-rising-maintenance-cost tools (which, in effect, often end up as straightjackets as no one wants to bear the cost of starting over, or the cost of updating, so business processes end up getting constrained by the tool rather than the tool adapting to processes.)
Interesting idea. It's such a difficult usability problem that it's tough to solve it for everyone. I had a job where I was responsible for getting business folk the data they needed and kept on trying to come up with a way for them to extract data from the database without having them to write queries. I got this nugget from the CTO: "To get the expressiveness of SQL you will be writing SQL"
Just need to figure out where the tradeoffs are and I think Excel made a pretty good one. I think Excel shouldn't be used for recurring jobs but for the one off it can't be beat.
Heh, at one smaller group I worked we ended up just creating some query-only SQL accounts for some select technically-inclined business folks and installing Toad on their machines, and let them hit the production database. It worked shockingly well, in spite of the 9000 reasons that this is a terrible idea. For more complicated stuff, we often could handle their service requests just by emailing out a query that they'd just save to the desktop.
It was obviously a really crude approach, but it worked... i've worked at a lot of places with more mature and sophisticated approaches to ad-hoc reporting requests but they were all way more of a headache in terms of IT workload.
Of course, it didn't hurt that one of the VPs quickly became a SQL select-statement guru within the exec team.
We had that going for a while too and it worked well. Someone with a SQL knowledge would write the query and then hand it off to the people with the read only SQL accounts. The issue we ended up running into is that the schema ended up evolving quite a bit so the queries became outdated and gave incorrect results. Then we had to have a process to centralize the queries and turn them into maintained views that could then be used as a the bases for the queries.
We are working on something close to that. A small, (hopefully) well-chosen subset of typical spreadsheet functionality and combining a few ideas from dimensional data modeling. Trying to make it fun to work and play with your data.
>My problem with excel is that it is not conducive to making well-documented systems. The formulas are hidden and there's no way to comment them.
You can leave notes in cells as documentation. I also have an instructions tab in just about every model I have that walks the user through the process.
The hidden formula thing can also be partially overcome by using certain colored cells to denote formulas, making the user aware that there are formulas.
> I'd love to see an Excel designed-from-scratch that scratches the same itches of approachability and user-friendliness but with an awareness of Excel's failings and without obsessing over the spreadsheet metaphor. Something in-between Excel and Access.
I proposed this idea once during a job interview; data analysts were using Excel to model data for providing short term financing. I proposed a web-based system that allowed users to share formulas over large datasets; datasets large enough that you wouldn't materialize them in spreadsheets (dropping the spreadsheet metaphor mentioned above).
Excel needs no defense: it is a rock-solid product which gives non-programmers lots of power. People have tried for decades to build alternatives with little success.
> The London 2012 organising committee (Locog) confirmed on Wednesday that a decidedly unsynchronised error in its ticketing process had led to four synchronised swimming sessions being oversold by 10,000 tickets.
[...]
> Locog said the error occurred in the summer, between the first and second round of ticket sales, when a member of staff made a single keystroke mistake and entered ‘20,000’ into a spreadsheet rather than the correct figure of 10,000 remaining tickets. The error was discovered when Locog reconciled the number of tickets sold against the final layouts and seating configurations for venues, and began contacting ticket holders before Christmas.
Isn't it a bit silly to blame Excel for a user's typing 20,000 instead of 10,000? Computers can't magically come up with the right answer from wrong input. You could argue that there should have been error handling or data validation, but most custom software doesn't do that either.
I'm not even singling out spreadsheets - thus the link to Panko and "human errors".
But fat-finger errors are very easy to make with spreadsheets, and very hard to find. This error, (20,000 instead of 10,000) was only found when LOCOG realised they didn't have enough seats for all the tickets they'd sold.
Spreadsheets are particularly tricky because people scatter input data among all those cells; Sometimes data is manually copied and pasted (which can either update automatically with outher changes, or not, depending how it's cut and pasted); they then manipulate those cells with formulas (sometimes across multiple sheets) and then they spit out a number. Or sometimes not even a number but a bar on a chart. Few people bother looking at the input or at the formulas used, they just see the output and sometimes apply a brief sanity check.
> You could argue that there should have been error handling or data validation, but most custom software doesn't do that either.
Most people don't write custom software. Spreadsheets give people a very powerful tool. Most people don't have the skills to protect themselves from that power - they don't know about error handling or data validation and spreadsheets make it hard to provide those sanitation and checking tools.
My point about custom software might have been unclear. Here's what I meant. A one-off ticketing app is a classic example of software that would normally get developed either in a spreadsheet or as some custom app that a team would build for the occasion. Such custom apps are typically bad at data validation and error handling, so it's plausible that the same typo would have had the same effect outside of Excel.
When I read that Eusprig literature on spreadsheet errors a few years ago, it seemed to me of rather low quality. Certainly many spreadsheets contain errors, but what are we comparing this to? Software in general contains errors. Custom app software—the only alternative to spreadsheets in most cases—tends to be of poor quality and is very expensive compared to spreadsheets. So it's far from clear that spreadsheets are a bad tradeoff.
Programmers take it for granted that "proper" software is better than spreadsheets, but I think that is mostly bias. Intellectually and technically, spreadsheets are low-status. Among users, on the other hand, they're high-status, for very good and deep reasons.
I'm also biased. I think that these problems need to be solved by innovation within the spreadsheet space itself.
I recently had to develop a little reporting system for my company's sales team and I have to say, Excel has some excellent interfaces on Windows. The little app I built is a GUI that lets someone choose a spreadsheet tied to a remote MySQL database via ODBC, refresh the data in that spreadsheet and email it at scheduled intervals. It literally took like a few days.
Also, coming from a Java background but doing all my recent work in Ruby (about which I feel ambivalent), coding in C# was a real pleasure. I'm more of a text-editor type, but I was also profoundly impressed with VisualStudio. Microsoft ain't all bad.
I'm currently attempting to write a roguelike using nothing other than Excel formulae (no macros etc.). It's going surprisingly well so far, although the limitations are obviously frustrating - not least the inability to correctly lock-in random numbers, although I think that can be worked around by linking the data from another workbook.
Obviously this is not a serious project, but it's kind of fun. I think this is where Excel gets dangerous. To a certain type of mind (perhaps like mine), the enjoyment from forcing a non-compliant system to do your bidding is greater than the enjoyment of using the proper tool to do the job quickly and effectively.
I still don't use pivot tables. Those are too practical.
> I think this is where Excel gets dangerous. To a certain type of mind (perhaps like mine), the enjoyment from forcing a non-compliant system to do your bidding is greater than the enjoyment of using the proper tool to do the job quickly and effectively.
You're hacking around with something for fun.
Other people? Not so much. EG, the guy who used MS Works spreadsheet (which was not loadable by anything else) for everything, including word processing. He'd open a spreadsheet, and type text in, and get "nicely" laid out documents.
The biggest problem with Excel is version control, because a spreadsheet is just a file (.xls are binary, .xlsx are XML but not human-readable), and people share it as an e-mail attachment. This is what makes it a horrible, unmaintainable mess.
This is also the advantage that Google Docs Spreadsheets have over Excel. The data is cloud-hosted and automatically keeps a revision history, allowing non-technical users to collaboratively edit without having to learn about version control.
But most companies don't allow their employees to upload company data into Google Docs, and with good reason, especially in light of the NSA spying scandal.
Right, but with the disadvantage of not having a free version, and they still have the same downside as Google Docs, which is that your cloud-hosted data is accessible by the NSA, who may then share it with other government agencies. Or it could simply be compromised if there were a security breach in the application.
On the other hand, as soon as you e-mail an Excel spreadsheet outside to anyone outside your corporate intranet, the NSA can see that data too, if they want.
It's really not the NSA or GCHQ that most companies are concerned about, at least in this regard. They're concerned that confidential information will be become available to competitors, the public etc.
Companies are generally much more afraid of Anonymous and Lulzsec than the NSA.
The concern with the NSA, if they have one, is usually the perception that customer/client information that they hold internally being abused.
Right, anyway the point being that cloud-hosting the spreadsheets resolves the biggest problem with Excel, which is version control, but it introduces a new problem, which is data security on servers that you don't control.
1. All the code is open source; you can host on your own servers if you want.
2. If you want to host on your own servers and you're a commercial outfit, you probably want a support contract.
3. If you don't want to be bothered with that and you don't mind about the NSA, you can use it as a cloud service on our servers with a minimal version for free and a commercial version for a subscription fee.
It would have been nice if this article had explained the shortcomings Google Spreadsheets have compared to Excel. As someone who has never actually run into a significant difference in practice, I would have found that interesting.
58 comments
[ 1.3 ms ] story [ 82.3 ms ] threadI guess our fund isn't "hardcore":)
I think the finance industry runs on Excel.
I work in aeronautics and everyone who works on a computer maintains some Excel spreadsheets. It's everywhere but so few people know how to use formula, pivot tables, etc, that it has become a real competitive advantage to master them.
It's fine if you want to keep a small budget or a quick bit of work with numbers, but it's awful for dealing even modest amounts of data and here's why:
Nothing you do is captured and everything is destructive.
Any time someone edits their spreadsheet they are irreparably altering the data without real documentation. Excel encourages moving, shaping, dragging, and futzing that may be easier, but ultimately can cause major problems because there's no audit trail. Ever try and replicate something modestly sophisticated that was done in Excel? Good freaking luck.
There's nothing wrong with Excel for some basic graphs and calculations. The problem is when people start to use Excel as a data base or as a serious tool for analytics (like some of those business folks).
Do you think Excel with built in version control would help in solving the problem? I remember reading about some company that was trying to do that but no idea where they are now.
Excel is as ubiquitous as it is because the mental model of a spreadsheet is very simple and easy to wrap one's brain around. Here's a grid of cells, type stuff into them. Boom, you're an Excel user. (It can do a lot more than that, of course, but many many many people never realize that and still get lots of value out of it. Which is kind of remarkable, really.)
Proper databases are much more powerful, but none of them present the user with an interface whose basic metaphors are as immediately obvious as Excel's. You have to learn about tables, data types, relations, etc. to get anything done in them. Most of that stuff comes up with Excel too, but you don't need to know it to get started. The Excel user's first baby steps are much smaller than the database user's are.
Given a choice between a flawed tool that's immediately understandable and a perfect tool that requires learning, people will always choose the flawed-but-understandable tool. So they go with Excel.
I think much of the explosion in data science jobs these last few years isn't really about "big data", but rather the fact that your typical corporate analyst does not have the experience to tackle mid range data analysis jobs because the only tool they have under their belts is Excel. What really is needed is a larger number of people with experience in statistics, scientific computing (Python, R etc..), and database systems. The current tools we have are fine, they just need to be used properly (well, we should always strive to improve our tools, but you get the idea).
Every tool has a scope that it has to be used on. If you use Excel in situations where you need team collaborations, stability, or other really advanced stuff, you're doomed.
However, if you just need to check a quick hypothethis, or mock up a solution, it's really the best tool for the job.
You did remind me of this amusing paper from a few years ago though: http://www.biomedcentral.com/1471-2105/5/80
Tableau is great for visualizations but if you want to do anything on your own servers it is prohibitively expensive and there are other alternatives out there. Not sure it provides the same capability as Excel for analysis.
88% Of excel spreadsheets have errors - I couldn't find the actual study after a quick googling. http://www.marketwatch.com/story/88-of-spreadsheets-have-err...
Recent national debt study found to contain erroneous calculations; http://www.nextnewdeal.net/rortybomb/researchers-finally-rep...
To my mind the key point in the article is lack of facilities for code review of Excel spreadsheets. Yes there are tools to help with that, but they're not widely used. If our developers routinely pushed their development code directly to production without proper review and testing, someone (probably several people) would get fired on the spot, yet important business decisions get made based on unreviewed Excel spreadsheets all the time.
The JP whale provides a great just-so story for regulators who want to take powerful, flexible tools like excel out of analysts hands. Give it another 10 years, Excel will not have the ubiquity it enjoys today in financial institutions.
It's intensely powerful (my boss can do spectacular things with pivot tables and teaches all the business people to do this as a means to doing their own analysis of our reports) but the high-power features have a positively nasty UI compared to the corresponding SQL query.
I'd love to see an Excel designed-from-scratch that scratches the same itches of approachability and user-friendliness but with an awareness of Excel's failings and without obsessing over the spreadsheet metaphor. Something in-between Excel and Access.
If you're really smart about it, Excel is one of the most remarkable tools for analysis ever devised. The problem is virtually everyone who is smart about it can use different better tools that are more suited to their purpose.
More than "replicate", I think the problem is no one else -- and, with enough time and complexity, not even the originator -- can maintain it, which results in Excel often being a vehicle for low-initiation-cost and rapidly-rising-maintenance-cost tools (which, in effect, often end up as straightjackets as no one wants to bear the cost of starting over, or the cost of updating, so business processes end up getting constrained by the tool rather than the tool adapting to processes.)
Just need to figure out where the tradeoffs are and I think Excel made a pretty good one. I think Excel shouldn't be used for recurring jobs but for the one off it can't be beat.
It was obviously a really crude approach, but it worked... i've worked at a lot of places with more mature and sophisticated approaches to ad-hoc reporting requests but they were all way more of a headache in terms of IT workload.
Of course, it didn't hurt that one of the VPs quickly became a SQL select-statement guru within the exec team.
What does fronting a database with a web service buy you? Rate throttling? Load balancing? Metering?
Why isn't there a JBDC proxy server thingie that does those things for straight JDBC?
Yeah, that.
You can leave notes in cells as documentation. I also have an instructions tab in just about every model I have that walks the user through the process. The hidden formula thing can also be partially overcome by using certain colored cells to denote formulas, making the user aware that there are formulas.
I proposed this idea once during a job interview; data analysts were using Excel to model data for providing short term financing. I proposed a web-based system that allowed users to share formulas over large datasets; datasets large enough that you wouldn't materialize them in spreadsheets (dropping the spreadsheet metaphor mentioned above).
If I had to generate these plots every week then I could script it easily with R, but in a fast-paced environment excel has its place
EDIT: My favorite example is http://carywalkin.ca/download-arena-xlsm/
But people are bad with numbers and spreadsheets give them the power to be bad with lots of numbers very quickly.
Spreadsheets are tricky to audit, and often they're not audited because they're just a tool that some guy uses without anyone knowing.
I've mention the EU SPreadsheet Risk Interest Group before, but here's the link for people who haven't seen it (http://www.eusprig.org/)
Panko has a nice website with some information about human error and the things that people do with spreadsheets (http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm)
If you like Excel and pivot tables you might like PowerPivot, a free addon from MS. Here's some information about it: (http://www.powerpivotpro.com/what-is-powerpivot/)
EDIT: I like some of the horror stories here (http://www.eusprig.org/horror-stories.htm), eg
> The London 2012 organising committee (Locog) confirmed on Wednesday that a decidedly unsynchronised error in its ticketing process had led to four synchronised swimming sessions being oversold by 10,000 tickets.
[...]
> Locog said the error occurred in the summer, between the first and second round of ticket sales, when a member of staff made a single keystroke mistake and entered ‘20,000’ into a spreadsheet rather than the correct figure of 10,000 remaining tickets. The error was discovered when Locog reconciled the number of tickets sold against the final layouts and seating configurations for venues, and began contacting ticket holders before Christmas.
I'm not even singling out spreadsheets - thus the link to Panko and "human errors".
But fat-finger errors are very easy to make with spreadsheets, and very hard to find. This error, (20,000 instead of 10,000) was only found when LOCOG realised they didn't have enough seats for all the tickets they'd sold.
Spreadsheets are particularly tricky because people scatter input data among all those cells; Sometimes data is manually copied and pasted (which can either update automatically with outher changes, or not, depending how it's cut and pasted); they then manipulate those cells with formulas (sometimes across multiple sheets) and then they spit out a number. Or sometimes not even a number but a bar on a chart. Few people bother looking at the input or at the formulas used, they just see the output and sometimes apply a brief sanity check.
> You could argue that there should have been error handling or data validation, but most custom software doesn't do that either.
Most people don't write custom software. Spreadsheets give people a very powerful tool. Most people don't have the skills to protect themselves from that power - they don't know about error handling or data validation and spreadsheets make it hard to provide those sanitation and checking tools.
When I read that Eusprig literature on spreadsheet errors a few years ago, it seemed to me of rather low quality. Certainly many spreadsheets contain errors, but what are we comparing this to? Software in general contains errors. Custom app software—the only alternative to spreadsheets in most cases—tends to be of poor quality and is very expensive compared to spreadsheets. So it's far from clear that spreadsheets are a bad tradeoff.
Programmers take it for granted that "proper" software is better than spreadsheets, but I think that is mostly bias. Intellectually and technically, spreadsheets are low-status. Among users, on the other hand, they're high-status, for very good and deep reasons.
I'm also biased. I think that these problems need to be solved by innovation within the spreadsheet space itself.
Also, coming from a Java background but doing all my recent work in Ruby (about which I feel ambivalent), coding in C# was a real pleasure. I'm more of a text-editor type, but I was also profoundly impressed with VisualStudio. Microsoft ain't all bad.
Obviously this is not a serious project, but it's kind of fun. I think this is where Excel gets dangerous. To a certain type of mind (perhaps like mine), the enjoyment from forcing a non-compliant system to do your bidding is greater than the enjoyment of using the proper tool to do the job quickly and effectively.
I still don't use pivot tables. Those are too practical.
You're hacking around with something for fun.
Other people? Not so much. EG, the guy who used MS Works spreadsheet (which was not loadable by anything else) for everything, including word processing. He'd open a spreadsheet, and type text in, and get "nicely" laid out documents.
This is also the advantage that Google Docs Spreadsheets have over Excel. The data is cloud-hosted and automatically keeps a revision history, allowing non-technical users to collaboratively edit without having to learn about version control.
But most companies don't allow their employees to upload company data into Google Docs, and with good reason, especially in light of the NSA spying scandal.
On the other hand, as soon as you e-mail an Excel spreadsheet outside to anyone outside your corporate intranet, the NSA can see that data too, if they want.
The concern with the NSA, if they have one, is usually the perception that customer/client information that they hold internally being abused.
1. All the code is open source; you can host on your own servers if you want.
2. If you want to host on your own servers and you're a commercial outfit, you probably want a support contract.
3. If you don't want to be bothered with that and you don't mind about the NSA, you can use it as a cloud service on our servers with a minimal version for free and a commercial version for a subscription fee.