Happy Spreadsheet Day !
I’ve had the idea for this article for some time now but in honour of the upcoming Spreadsheet Day on 17th October , I thought I’d finally get around to writing it up.
If you are hardcore- you can also view this article itself in a Google Sheet ; )
Or this xlsx file….
https://1drv.ms/x/s!AqZfpTxqClrLhnCPhc2VaPNSa023
Article Outline
- A Brief History Of Spreadsheets
- Spreadsheets As Collaboration/Workflow Management Tools
- Spreadsheets As Web/Mobile App Creation Tools
- Spreadsheets As Platforms For Work Productivity Innovation
- The “FUTURE” Of Spreadsheets — What’s Next ?
- When NOT To Use Spreadsheets
- Concluding On A Lighter Note
1. A Brief History Of Spreadsheets
Love them or hate them , since the first spreadsheet program Visicalc was released in 1979, spreadsheets have now been around the market for more than 40 years and are ubiquitous in nature even if some people only ever use spreadsheets as glorified calculators.
Such is the pervasiveness of Excel that it even made scientists change their naming system for human genes.
Spreadsheets often get looked down upon by serious developers / programmers as the poor cousin of proper data analytics tools and database applications
However in this article we’ll look at a few interesting extensions that may change your mind about what is possible with the humble spreadsheet application and a couple of interesting start-ups who are trying to re-define the ‘future of spreadsheets.
2. Spreadsheets As Collaboration/Workflow Management Tools
It is a common scenario where you might not just have a spreadsheet that you share with different stakeholders to complete but a more detailed way to control who can edit which sections and in what sequence and maybe even update other associated spreadsheets once complete. This necessarily means that you have to create some sort of a workflow.
In the past, this had to be done “managed manually” outside of the application but there are now a lot of tools that allow you to integrate a workflow inside a spreadsheet directly.
If you are an Excel user then Microsoft a ‘native’ solution that integrates Excel into their “Power Platform” of apps. While technically Power Automate is not part of Excel itself , it does the job.
However there are plenty of similar competitor services that do similar things where you can start with data from existing Excel files , Google Sheets or csv files and link them up to form a data-flow where the bulk of the end users will still only interact with the spreadsheet interface at the front end even if the back-end is supported by something more complex in the background (e.g a database etc)
Edit Oct 2022
Actiondesk is a spreadsheet interface that connects to SaaS and databases to allow users to work on their data live through either a Google Sheets or Excel interface.
Similarly Coefficient is a sidebar app for Google Sheets. Coefficient connects to any data source, imports live data into Google Sheets, automates spreadsheet workflows, and exports data into business systems
Visyond
Is a spreadsheet based Decision Support System that allows for creation of predictive dashboards, automated Financial Statements, What-if and Risk analyses.
3. Spreadsheets As Web/Mobile App Creation Tools
There are also a number of low code services that allow anyone with minimal experience in coding to start building their own web or mobile apps with spreadsheets acting as the ‘backing’ database.
Personally , I’ve used Glide myself so I can attest to how ‘low-code’ spreadsheet to app tools vastly simplifies the app creation process versus having to spend hours figuring out why my Java/Kotlin code won’t compile and isn’t communicating right with Firebase.
4. Spreadsheets As A Platform For Work Productivity / Efficiency Innovation
Guilty admission — Excel VBA was my ‘gateway’ language towards programming. I know many people rail against how clunky the language is (There are a number of quirks about how it handles certain scenarios like delegates and reflection) in spite of this, it is possible to still create a whole bunch of interesting applications using excel (For example this totally crazy Excel based Point Of Sales System)
This ‘programmable’ aspect of Excel has also spurred the growth of online marketplaces for Excel Add-Ins…
While Microsoft has said that Office Scripts (which looks a lot like Javascript) will eventually replace VBA, with ten of thousands of legacy spreadsheets out there, this switch is likely to drag on for some time.
(UPDATE Dec 2020 — Also now this Microsoft just announced the release of Lambda, a programming language native to Excel, to develop and manage formulas https://www.theregister.com/AMP/2020/12/04/microsoft_excel_lambda/)
In Google Sheets any ‘macros’ are written in App Script (which again is basically Javascript) and they their own marketplace (which seems to be a bit healthier and more vibrant than MS Excel’s Add-In Marketplace)
5 The Future Of Spreadsheets
Although I could write about what Google Sheets and Microsoft Excel has planned, I thought it would be more interesting to turn towards a few start-up companies that are trying to ‘re-invent’ the basic ‘spreadsheet’ format.
The aptly named ,Spreadsheets.com , has a product that is similar to the conventional spreadsheet but with added features to make collaboration a lot easier and native integration of ‘in the cells’ attachments
Coda also wants to take things one step further, by doing away with the division between text editors/spreadsheets/workflow apps by creating something that purportedly does all three in a single ‘document’. I haven’t tried it myself but it certainly looks ambitious.
Lastly unlike the other two examples which are start-ups of less than 1–2 years, Airtable has a more proven track record having been around ~8 years. Airtable is a spreadsheet-database hybrid that has many of the features mentioned above (Turning it into apps, workflow management, etc) and quite a wide range of integrations with other API-s.
6. A Word Of Caution…
Even with everything that we’ve seen so far that can extend and enhance what is possible with spreadsheets, there are times where a spreadsheet is NOT the right tool for the job !
While there is something to be said about the ‘intuitive’ nature of how data is laid out in spreadsheets — in that you get to ‘see the data’ directly , this can be a weakness as it ‘couples up’ the data model and any underlying calculation logic — while this is sometimes convenient for ad hoc modelling, this can limit the ability to generalize the model across larger datasets as the ‘logic’ is closely tied to how the data is formatted.
Also unlike programming code that have more robust version control tools like Git or SVN , revision management for spreadsheets is limited — making errors more likely especially if multiple parties are collaborating on the same spreadsheet. To make matters worse, in Excel there used to be more functionality in the previous Shared Workbook feature that has since been lost when Microsoft moved to the new “Co-Authoring” feature that replaced it in Excel for MS 365)
==========================
Updated for Oct 2022’s Spreadsheet Day Celebration
If you want to learn more about version management — this article from Layer.io (one of the companies mentioned in Section 2.0 above) has a great Beginner’s Guide to Excel Version Mgmt (…and a sales pitch as to how Layer’s spreadsheet management platform can solve the version control challenge)
However — if you are dead set on still using XLSX with Git there are a few workarounds you can try to still use:
==========================
There is also the issue that unlike regular SQL or NoSQL database applications , spreadsheets tend to have lower limits for the number of columns and rows. In Excel the limit for newer versions i.e XLSX format — each Worksheet can support up to 1,048,576 rows and 16,384 columns i.e column XFD). For Google Sheets it’s 5 million cells or 18,278 columns (column ZZZ)
A more current and unfortunate example of when spreadsheet ‘mis-use’ h ad serious consequences is this incident:
Apparently the source of the error was that “column lists on an Excel spreadsheet reached their maximum size and stopped new records from being added automatically”.
7. Ending On A Lighter Note…
Despite of it’s potential shortcomings, spreadsheets remain a powerful force in ‘democratizing’ data literacy due to it’s quotidian nature and ease of use (compared to having to learn a programming language or dive into SQL).
To celebrate Spreadsheet Day, I wanted to end witg a few funny articles on some unconventional (and probably unintended) use of spreadsheets
Spreadsheet Art:
Spreadsheet Music :
Spreadsheet Stand-Up Comedy :
This is a rather amusing comedy routine by Matt Parker about Excel. It’s a bit dated (2016) but still worth a chuckle.
Spreadsheet Virtual Reality :
Finally, the winner of the “…but why?” prize for me is this definitely this particular entry:
Jokes aside though (Apologies for the shameless self promotion) : If you or anyone you know is looking for some help with your spreadsheets, look me up on my Fiverr page !
