DIY : Unleashing the spreadsheet – AirDev – Medium
New tools are transforming ordinary Excel models into powerful and connected applications. Consultants take note.
Excel has had a mighty good run.
As a former McKinsey consultant, I recognize that I have been programmed for blind allegiance to the spreadsheet. I also admit that Bill Gates has by now received adequate wealth and honor for his unseating of Lotus123 as the standard-bearer for modelers and analysts. Still, I think a piece ultimately critical of Excel’s shortcomings and suggestive of a better alternative is best to start with some flattery.
Why spreadsheets are great
Three factors have made Excel so valuable (perhaps more, but did I mention I was a consultant?):
(1) Flexibility: A spreadsheet is just a grid of inputs, each of which can be set either explicitly or through a formula. This exceedingly simple premise allows spreadsheets to “excel” in an immense range of tasks, including:
- Performing basic or recursive arithmetic
- Storing relational data tables
- Conducting statistical analyses
- Comparing multiple scenarios
- Solving equations
- Visualizing results
From basement “war rooms” to executive boardrooms, this uniquely broad range of applications makes it worthwhile for most of us how to use Excel.
(2) Transferability: Spreadsheets are ultimately a form of communication. They allow the creator to share their methodology, results, and implications with others. Excel’s program + document model allows for a seamless transmission of ideas. Anyone with Microsoft Office can send and receive individual files to open, use, and modify them directly. This setup means that the more people use Excel, the more valuable it becomes as a standard for communication — the definition of a network effect.
(3) Empowerment: If you have created a spreadsheet, you are officially a coder — congratulations! Perhaps the most under-appreciated aspect of Excel is that it is both a software application and a rudimentary software-maker tool. When you type an equals-sign into a cell and conjure a formula, you are writing code in Excel’s proprietary language, telling a cell how to perform an automated task to achieve a result. This means a spreadsheet exists as two things at once:
- First, a canvas where an amateur coder (you) can define logical machinery
- Second, a live software application, where others can send data through your machine and see the results
By empowering countless individuals to (unwittingly) code applications, Excel has given rise to the amateur developer.
Where spreadsheets fall flat
Despite its impressive range and power, the common spreadsheet has been dogged by a few key limitations:
(1) Local: An Excel doc is all-inclusive: the data and calculations are all contained within the file and run from your local computer. This has a number of obvious drawbacks:
- You can’t collaborate on a live doc, making “version control” a nightmare
- Your data is static based on your latest upload, rather than updating in real-time as conditions change
- If you are working with a lot of data or complex logic, your file will become really big, threatening to crash your computer
(2) Fragile: The spreadsheet’s dual function as both the application-maker and the application itself is a double-edge sword. Have you ever felt that queasy feeling as you share your precious model with others so they can use it as a live application? Despite your best efforts to hide tabs and lock cells, your audience will inevitably find a way to break your formulas, enter invalid data, and create Frankenstein versions that you never anticipated.
(3) Ugly: Some (Excel) models are truly beautiful. Most feature a mundanely familiar grid of numbers, or worse yet, a mangled mess of tables and charts. This can make it difficult for someone to identify the most critical assumptions and results. A modern web application, on the other hand, puts most of the logic behind the curtain while guiding you through a visually engaging “user experience”. Thinking outside the grid can have tremendous impact in communicating key messages.
Unleashing the spreadsheet
Many of the above deficiencies are the result of Excel’s rise in the pre-SaaS era of CD-ROM installations. Until recently we have had to live with their existence, but the new generation of web-delivered software is filling in the gaps, and in some cases rethinking entirely how spreadsheets work.
Spreadsheets-as-a-Service: The natural way to overcome the “local” limitation is to deliver spreadsheets as a web service. Indeed this trend is more than a decade old, and since Google Sheets popularized the cloud-based approach in late 2006, others have followed suit, from online suites like Microsoft’s Office 365 and Apple’s iCloud, to newcomers like Quip. An online spreadsheet is still a spreadsheet, but by hosting your document in the cloud, you can open it up to nifty tricks like real-time collaboration, simple version control, and more horsepower when needed.
Live integrations: Once spreadsheets are connected to the web, they can tap into the vast universe of application programming interfaces (APIs) to pull in live data, push results to third-party applications, and even trigger automated workflows. A number of exciting new tools are jumping on this trend:
- Airtable describes itself as “part spreadsheet, part database” and lets users pull in live data from lots of sources into a variety of interfaces from grid to calendar to user-friendly tiles
- Blockspring is an add-on for Excel or Google Sheets that allows creators to request live data from a large library of public APIs using formulas that resemble the standard Excel types
- Zapier (a general app-to-app connector tool) lets you set up rules to automatically send your latest Typeform survey results to your spreadsheet, or to create a new Trello task from a new row you add to your spreadsheet
- Dashdash, a stealth startup from Berlin, recently received an $8m investment led by Accel to help people trigger automated actions from spreadsheets
Web apps with spreadsheet guts: For anyone looking to share their spreadsheet as a live application, the “fragile” and “ugly” limitations can be dealbreakers. Even a well-made and cloud-based spreadsheet fails to meet the design standards of a modern web app, and can only service one user’s scenario at a time.
The solution here is to turn your model into a full-fledged web app. At AirDev, we have seen a recent trend in managers looking to upgrade their Excel-based tracking systems to robust software, and consultants looking to use apps to deliver more personalized insights to clients. There are two flavors:
- Spreadsheet-powered: The quickest option is to create a pretty web interface that sends a user’s inputs to your spreadsheet and gets the results back. This avoids having to rebuild existing logic. We developed a free self-service tool called Algo to do just that. Just connect a Google spreadsheet, tell Algo which cells are the inputs and which are the outputs, and the tool will generate a live webpage you can share with anyone looking to run a scenario through your model.
- Fully-contained: The most robust solution is still to build a custom web application containing the data, logic, and interface your users will need. This is a bit more time-consuming up-front, but will perform much better at scale than a spreadsheet-based solution. I recommend using the option above as a lean test, and this option once you have confirmed the value for your audience.