1. 23

Frequently, I have made web apps, only to realize it could have been a spreadsheet.

Now, I’m trying to wrap my head around how to perform SQL-like queries, regex string manipulation, and similar operations — and feeling baffled by The Spreadsheet Way™, which feels quite foreign coming from my programming background.

I have had trouble finding the answers to the kinds of questions I have in the documentation and tutorials I’ve found. (I’m using LibreOffice Calc, but there’s probably a lot of overlap with Excel and Google Sheets.)

What resources helped you get an intuition for spreadsheets, and use them effectively?

Thanks!

  1. 24

    You Suck at Excel by Joel Spolsky is a classic. There’s a line in it about how every few months there’s a new startup pitching the Excel pivot table feature.

    1. 3

      Thank you for this; I had heard about it in passing a year or two ago, but never actually looked it up. Looking forward to watching it this evening.

      1. 3

        This is so good! Thank you. I ran across this a while ago, but never got more than a few minutes into it; watched the whole thing and took notes.

      2. 8

        I think a key question is also, “who is this for?” If you are doing something for yourself and realize it could have been a spreadsheet, fine, lesson learned. But many of the apps I’ve made for customers have been incredibly small and “could have been a spreadsheet” but then they would have a lot more options, a lot more confusion, and a local thing they could easily screw up. For them, a small web app was better.

        1. 3

          I would second this. I’ve seen messes of spreadsheets where you have hugely complex input and transformations come with a set of instructions and sequence of which buttons to press to guide the calculation in its proper flow. You wouldn’t want to subject that to any user. Also, data validation is typically completely missing, so it’s hard to ensure the user didn’t mess up the “input sheet”.

        2. 6

          Here’s how I started with Excel:

          1. Do some of the official “training spreadsheets”, specifically for formulas
          2. Walk through the top ribbon of commands, looking up what every command does
          3. Read through all the keyboard shortcuts
          4. Come up with a few research questions I wanted to answer, like “what cities are bigger than what countries”
          5. …That’s basically it.

          This didn’t give me a feel for The Spreadsheet Way™, which only came with some use, but it gave me all the tools to make spreadsheets useful to me, which then got me started.

          1. 6

            I’d like to give your anther option. There’s a few services now that try to reimagine spreadsheets. If you don’t need someone else to look at the result in Excel, have a look at Grist for example. You can try it out online and use the hosted version or spawn it locally with one docker command.

            The shareable file itself is an sqlite database. On the interface side you get actual structured tables rather than amorphous blob of cells, you can use python in formulas, create dashboard-like views from the data, and many other things. (https://getgrist.com)

            It’s perfect for people like me who miss MS Access, but for modern times. (and are too cheap for PowerBI)

            1. 4

              As for me, I’ve never gotten really good at using spreadsheets. Their mutability makes me kinda anxious to be honest 😅

              1. 4

                I went in the other direction, starting with spreadsheets as one of my gateways to learning programming.

                I remember learning AWK later, and thinking it was like a liquid spreadsheet poured through the formulas.

                Spreadsheets made sense to me as a grid of self-referential data. A way to automatically do the kind of paper calculations we were doing in school.

                We already were asked to, by hand, do stuff like

                 342
                -173
                ————
                (= upper - lower)
                

                so going to an app that could make that kind of stuff more automatically wasn’t a big leap. Learning the Σ button before learning to manually make or edit formulas.

                Now, that’s just the super basics but… I don’t recommend going much further than that. It’s not very scalable and can strand you in an unexportable data swamp. If you wanna use SQL and regex you’re already better served by most other programming languages, I’d guess.

                1. 3

                  I think of a spreadsheet as a way to represent your data. Each data token is the intersection of a variable (usually a column in the spreadsheet) and an observation (usually a row in the spreadsheet). The intersection of variable and observation becomes a cell.

                  When you want to interact with your data, you can think of most operations either working with variables/columns or within an observation/row. Most spreadsheets implement this with cell-level formulas, but you can think about those formulas as either column- or row-wise operations.

                  Some advanced formulas (vlookup comes to mind) can work on whole columns instead of only cells.

                  As far as programming goes, you can think of a spreadsheet as a matrix, where you can do column, row, or cell operations are appropriate.

                  1. 3

                    Google Sheets has SQL support with the QUERY formula, I use it pretty frequently.

                    1. 3

                      Posting also another look at spreadsheets, through a lens of a build system: https://www.microsoft.com/en-us/research/uploads/prod/2020/04/build-systems-jfp.pdf. This is a longer, extended version of the original “Build Systems à la Carte”.