People are all talking about how it's useful for quick stuff with datasets, but it goes far beyond that. It's useful for quick anything you might want a small database for. Imagination is the limit.
For example, if any of you play D&D online, you might be familiar with dndbeyond's character sheets. They're a fantastic way to onboard new players who might not have the inclination to spend hours with the rule books before they even start playing. It does all the calculations for you and gives you some buttons like "roll athletics" and doesn't let you add more spells than your character can have with their stats.
I recently persuaded some friends to give FATE a try and built analogous push-button character sheets with google sheets [0]. It was quick and simple. With conditional formatting, you highlight bad states (rules say you can't have more of X than Y!). With the script editor, you can add full on buttons for dice rolls and other state changes with whatever logic you want (anything you can code up!). Checkboxes are obvious but super useful. And the transparency of the calculations is helpful for teaching people the system (this stat is "min(A4, B1+C5)").
Without google sheets, it would be a serious endeavor to build a stateful, database backed, live collaborative GUI that can be added to and customized on the fly by my users. With google sheets, it was a quick fun afternoon hack. Excel/google sheets is an amazing piece of technology.
We started a company with the core premise that spreadsheets will never die [0]. Spreadsheets are so good at the most casual data viewing and exploring tasks to creating complex financial models. They are also the de-facto choice when you have data (not big data) from multiple sources that you need to "join". We tend to underestimate the beauty of this tool which can be used productively at all points of the skill spectrum. Everyone feels at ease in the familiar territory of a spreadsheet, which is what makes it ubiquitous and basically impossible to kill.
If spreadsheets were two-way connected with your core systems like SaaS tools, DBs, Slack, etc then you could represent serious business logic and actions without being a programmer. It is the best platform to build a "no code" tool for non-programmers.
That's awesome, Navneet! I have heard from big hedge fund managers, and seen first hand working at "cutting edge" 401K fintech companies that spreadsheets are the backoffice workhorse in many many companies doing serious business and handling serious data.
I am curious about one aspect though: Debugging spreadsheets is seriously hard. How do you help customers verify their spreadsheet has the right things they are looking for, avoiding regressions due to a random change by some inexperienced person, etc.?
Also, at what point do you see companies move from spreadsheets to simply hiring developers to do what they want? It seems like beyond a point, spreadsheets can get in the way, and the company has enough resources to hire a team to build custom internal tools.
Yeah spreadsheets are ubiquitous workhorses -- small companies to large, old to new, across geographies ...
Spreadsheets have many problems. We are going after their "connectivity" to the rest of the company systems. Hidden errors / debugging is definitely another big problem which we are only tangentially solving. If your data is imported into the sheet through Coefficient (instead of a copy-paste of a downloaded CSV), then you can always trace the lineage/timestamps/etc all the way to source.
As for hiring developers, the truth is that day-to-day business needs grow faster than you can hire devs. So yes, at a certain point companies move some workflows to dev-built tooling or specialized SaaS tools, but their bucket of unhandled workflows still grows larger every day. That is why you can't kill spreadsheets.
Debugging can be hard. That's often because what they're used for is done very fast. So, there's a business expert reasonably IT literature that has a powerful tool with a built-in IDE that doesn't take approvals or hiring requisitions or even budget and a problem's solved within a week on what otherwise would be a month (being kind).
The problem starts when whatever frankensheetvbasharepointdb is now an engrained part of a workflow, excelbizdev has disappeared, and 'maintenance' falls to nonexcelwhizz, or perhaps worse, a dev team that lacks much legacy business understanding to figure out why the particular implementation was done, screws up understanding, and creates something worse.
Spreadsheets are everywhere. Heck our local power company uses a complex spreadsheet for load prediction. It includes holidays, football events, historic weather and weather predictions. Saves them a bundle.
I'm running a game of D&D and needed an accessible character sheet that works from a phone with good support for pinch-to-zoom.
Tried fillable PDFs and a bunch of online stuff. None of it worked well. The spreadsheet fields' font sizes were all weird, and even if you manually correct them it would reset on every edit. There were some promising web-based options described as "responsive character sheet", but they tended to fall apart at large text sizes.
Instead of trying to shove an 8.5x11 paper layout into a phone, it just groups stuff into tabs that make more sense anyway. And if you were completely blind I bet it's still easy to navigate with VoiceOver.
We're using Numbers because it's what we both have, but I think Excel should work similarly.
If anyone's reading this from the Google Docs team, please take another look at Sheets' pinch-to-zoom behavior. That was the first place I ended up when I went looking for character sheet spreadsheets online, and it was the first one I ruled out because of how shitty the experience was on mobile.
I think every IT professional should at some point work for a non-IT company (where hopefully they are tech-savvy enough to be using spreadsheets). It's amazing to see what people will use it for (answer: everything).
I find with excel it takes just as much time to set up a spreadsheet to do what I want as it takes for me to do the same in R, usually a lot more time with Excel. Both pieces of software have learning curves, just to me R is the better tool for the job for working with tabular data. Excel forces you to hardcode your fomulas and ultimately adds a lot of cruft and time wasted, compared to R which is much more modular. That "min(A4, B1+C5)" is liable to break if your spreadsheet changes. R functions on the other hand are pretty well documented, and you could do anything you want in R after following a tutorial for a couple hours. Instead of having to hard code a position, you can refer to it relatively or by some unique identifier, so your calculations still work no matter how your underlying spreadsheet changes or is shuffled around (and familiar formulae like sum and min and max are there by the same names). It's way easier to do statistical tests and plot data consistently in R as well. Oh, and you can export to .csv or .xlsx from R if you'd like of course.
2. How does the UX for your R solution to the "DND Character Generation" problem compare to the screenshot from grandparent comment, for users not familiar with either R or Google Sheets?
Doesn't grandparent also know how to program? At this point we're just asking what threshold of UX either programmer cares about. I'm sure either could whip up a desirable UI in either execution.
I mean, I work with laymen that use Excel to encode multimedia content state machines and its not pretty (dreadful bespoke schema with all the caveats you can imagine) but it satisfies their need
Grandparent here. Yeah I know how to program, but the point here was to make my players a simple interactive GUI that saves its data somewhere we can all access together (e.g. the cloud). Doing that in R would be well beyond my skillset, even as an R user. Doing it with a web stack would be more appropriate, but even then it would have been a lot more work and I can't think how I'd do it without spinning up a server and setting up some http endpoints.
> That "min(A4, B1+C5)" is liable to break if your spreadsheet changes.
I don't usually have that problem. Inserting or deleting rows or columns around the cells doesn't break these formulas. Only changing what type of information a cell contains would. Does this happen often for you?
Cell references in Excel are not hard-coded; they are automatically updated if you modify the spreadsheet by deleting or inserting other cells, and unless you use the $ before the col or row they automatically increment/adjust when pasted into another cell.
And you can just name a cell or range if you want to use a variable name to refer to some data in a pivot table or formula.
At no point do you talk about UI here -- for a character sheet (or most simple things), I want to control layout to some degree, let users edit some numbers, and see others automatically update. Obviously I could have a file full of constants and a bunch of print statements at the end, but is there anything nicer / more dynamic?
> I find with excel it takes just as much time to set up a spreadsheet to do what I want as it takes for me to do the same in R, usually a lot more time with Excel.
I mean, this is I reach for Pandas over Excel, but most people would be infinitely more comfortable with spreadsheets than specialized tools. Spreadsheets also happen to be useful enough for almost everybody.
For example, if any of you play D&D online, you might be familiar with dndbeyond's character sheets. They're a fantastic way to onboard new players who might not have the inclination to spend hours with the rule books before they even start playing. It does all the calculations for you and gives you some buttons like "roll athletics" and doesn't let you add more spells than your character can have with their stats.
I recently persuaded some friends to give FATE a try and built analogous push-button character sheets with google sheets [0]. It was quick and simple. With conditional formatting, you highlight bad states (rules say you can't have more of X than Y!). With the script editor, you can add full on buttons for dice rolls and other state changes with whatever logic you want (anything you can code up!). Checkboxes are obvious but super useful. And the transparency of the calculations is helpful for teaching people the system (this stat is "min(A4, B1+C5)").
Without google sheets, it would be a serious endeavor to build a stateful, database backed, live collaborative GUI that can be added to and customized on the fly by my users. With google sheets, it was a quick fun afternoon hack. Excel/google sheets is an amazing piece of technology.
[0] Screenshot of the "app": https://raw.githubusercontent.com/imh/public_images/main/Scr...