Hacker News Reader

Comment by galfarragem | Hacker News Reader

I don't understand why spreadsheets are so fervourously bashed: most times they do the job and in a fast and inclusive way.

The spreadsheet is certainly one of the best tools ever invented.

[0] Joel's Spolsky must-watch talk on Excel: https://www.youtube.com/watch?v=0nbkaYsR94c

View on HN

Excel doesn't have a compeling, standard way of doing version control that I know of, and most Excel files mix freely data and code in the same sheet, which makes debugging pretty hard. Excel files go from assets to liabilities when they start boxing over their own weight, which happens pretty fast...

Makes me wonder if there's a market for an Excel clone whose files are easily machine- and human-readable, and version-control friendly.

Google Sheets

Have you tried using Google Sheets? I have. You can't even cut and insert a bunch of rows. That's a primitive functionality that is very much needed that was available in Excel circa 1988 (I'm exaggerating, but it's been in Excel for at least 2 decades).

While sheets is nowhere near my idea of ergonomic, I've figured out this one every time I needed it. And, despite working for Google, I'm nowhere near a regular user.

Maybe your browser, or some extension, got in your way?

Sheets is way less powerful than Excel. Losing a lot of the keyboard shortcuts also makes it way less useful for power users. It also doesn't do well at generating macros and its chart generation capabilities are much less flexible.

Google Sheets is the main reason I gave up on Google Docs and bought an Office 365 subscription for myself a few years back. It's just nowhere near as good as Excel. Frankly none of the Google Docs offerings holds a candle to Microsoft Office[1], so the latter is well worth the money to me.

[1] I am tactfully not counting GMail as part of GDocs here because, honestly, Outlook is the one tool that wouldn't come off well in that comparison, and reason #1 is that Outlook's search is terrible; reason #2 is that Outlook doesn't perform at all well in the face of the modern "I want to keep all my mail so that I can search for and find relevant messages whenever I need them", which can be key when dealing with tricksy customers/partners/co-workers, and is handy even in mundane day to day use.

"Frankly none of the Google Docs offerings holds a candle to Microsoft Office"

Word and Excel are great for individual productivity. If you use documents and spreadsheets as tools for collaboration, though, Google Docs is much much better overall, even though it's missing many features to which power users have become accustomed.

Sure, you can save a file to OneDrive and have multiple people working on it at the same time. But:

- In my experience, simultaneous editing via OneDrive (whether using the browser or the desktop apps) is more laggy and buggy than with Google Docs

- The commenting functionality is missing lots of features that are essential to my desired collaboration workflow:

i) Ability to give someone access to add comments and suggest changes, but not to accept changes or edit directly

ii) Comment authors are identified, and only they can edit comments they have written

iii) Comments can be received and replied to by email.

The way commenting works in Google Docs encourages collaboration in a way that the simple comments in Word doesn't. If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.

So Google is "much much better overall" for multiple users, as long as you can dumb what you're working on down enough to fit Docs significant limitations. That's totally fair, but isn't a trade off everyone can make.

Yes, there's a trade-off. Note, I didn't say Google Docs is better for every use case. I said it's better "If you use documents and spreadsheets as tools for collaboration".

The key difference I'm talking about is between:

A. Writing a document, and sending a version of that document to one or more other people, so that they can do something with it. (This might involve sending you back feedback or suggestions.)

B. Sharing a link to a 'living' document, with which people can interact in different ways (comment, participate in comment threads, add change suggestions, accept/decline changes).

In my experience, B is much harder to achieve, and unlikely to happen organically, if you use Microsoft Office.

Has anyone here witnessed an organisation that uses Microsoft Office, and where significant progress is made on people's thinking, through their online collaboration on docs/sheets?

> If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.

I have (for a previous company), and it still hasn't proven worth it at other companies, including my current employer. We use Office 365 exclusively: it's not perfect but, as I say, Excel is streets ahead of Google Sheets in every other way, and that matters for our use cases.

We use Google Sheets as a team for its collaboration functionality. However it still has a lot of room to improve with its visual customization for charts and formatting and such.

I realize some people don't care about how beautiful a spreadsheet looks, but in presentations or sharing complex information, it matters. And formatting anything complex with charts and such is a major headache in Sheets (like my latest struggle to get annotations to properly appear, or move a single peak data label so it isn't cut off from displaying).

The thing about Google Docs is that I haven't seen any changes or updates to get toward feature-parity since I was using it in high school a decade and a half ago. I assume it has been exiled to Google's island of misfit toys that they can't quite take out back and shoot just yet.

I hope not. Google has fairly recently made the Docs API available.

Everyone commenting: Google Sheets is not an exact copy of Excel, but it's the closest (I know of) that exists.

For plain-old Excel with version control, save your Excel files in Dropbox. MSFT might also offer a cloud version with version control but I have no experience with it.

Are Google Sheets "easily machine-readable"? I've tried to add support for it to my application, and I could not get it to work at all. Maybe it's technically possible but it's sure not easy. You need to use Google's proprietary APIs, and the documentation and support are terrible.

I've pulled some data from a sheet into a python notebook by copy-pasting some code I've found. Sure, these are proprietary APIs, but how else would you talk to a proprietary web service?

Disclaimer: despite working for Google, I don't know that much about the suite.

If you are using XLSX files (Excel 2007 and later) you can use an Excel diff driver with Git for not terrible version control - http://programmaticallyspeaking.com/git-diffing-excel-files....

Awesome, I never knew you could change the diff drivers in git. Such a well-thought-out tool

Google Sheets lets you download the workbook as an Excel file which can be automatically saved to a Google Drive folder. Hourly (?), daily, weekly, monthly snapshots of a shared workbook can all be easily scripted and stored for later retrieval. It eliminates any confusion over which is the latest workbook because the Sheets version is always the most up-to-date.

O365

I met a guy at a wedding reception last year, where we were talking tech, and I was lamenting similar issues (we use fairly large multi-worksheet spreadsheets for configuration). He mentioned that his company makes some tooling that might help with this:

https://www.htilabs.com/

We ended up going a slightly different route, but thought this might be worth sharing.

(I am not affiliated in any way and have nothing to gain by this.)

All of that is forgivable because Excel has done something very few other tools have ever done; which is allow 'non-programmers' to effectively program their computers. Excel is empowering software that lets normal users solve novel problems. With excel, a non-technical small business owner can create software(a spreadsheet) that automates the solution to a problem nobody has ever had before. Solutions to programs the creators of excel never even considered.

A lot of software is like single-use kitchen gadgets. So called "unitaskers". It has one purpose envisioned by it's inventors, and you'll be lucky if you ever figure out something else it's good at. Sometimes they're pretty slick, but still limited. My mother has a device that skins, slices and cores an apple all at once. With that device, you can process enough apples to make a pie in a minute or two... but the chances of you ever finding anything else that tool can do are slim. Maybe you could use it to slice curly french fries out of a potato, but that's about it. It saves your time if you're operating in the narrow problem space envisioned by the inventor, otherwise it doesn't have much of anything to offer you.

Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.

In my experience, oftentime Excel is also used out of desperation from not being able to install proper tools, it's not necessarily the only programming solution the users know, it's the only one IT let them have (in the case of technical users, e.g. engineers).

Restrictive IT policies are the root cause of some of the gnarliest Excel sheets I've seen.

Out of curiosity, what industry are you in?

Consultant, so I see lots of different customers, mainly industry.

Many Excel sheets are a form of shadow IT, no proper way to get the app they need in a reasonable time, so people jury rig something with what they have.

I suspect I see more dysfunction than average still...

I work in industry I've seen some of the same things. The IT policies at my work do tend to be overly restrictive but I think it is somewhat understandable there is maybe two thousand people that work at the plant I'm at. 10 to 15 years ago someone made the decision to outsource IT support (before this every department had IT specialists) there is just no way external support can scale to the level its even feasible to think about giving everyone on site admin access or similar. So we've ended up in enterprise like situation with creeping bureaucracy where everything is super locked down and if you need some software installed submit a ticket get a manger to sign off on it (and be prepared to argue with them about why it's necessary to get this software installed) and maybe 1 or 2 weeks later ticket will get actioned (if you are lucky and it doesn't end up sitting for a month in purgatory somewhere).

Excel is part of standard environment so it is present on every fresh installation here so there is a tendency to reach for it. The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing. The other issue is "I know I have excel installed and I know Bob has excel installed - who knows what else Bob has installed already so if I want to collaborate with Bob I better just use excel."

Personally (I work as an Engineer - the non software kind) I think excel is fine for rapidly prototyping something and as a first pass solution for when you need to quickly answer something but once you've reached the stage a spreadsheet is needed to run process it should be rewritten in something more robust - preferably before it reaches that stage.

> The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing.

Yes, one of the major problems for which Excel is a popular solution (and perhaps the biggest in enterprise environments) is IT service request friction.

If you really want to displace Excel, you don't need alternative software (I mean you do, but not anything novel), you need IT to not be an isolated distant mystery group which can only be invoked by time consuming, arcane rituals to which they give unreliable and untimely responses.

Most businesses using excel don't even have an IT department. I'm talking about the LONG tail of small business where the owner is using excel and nobody, except perhaps the size of their wallet, can tell them some other piece of software is off limits.

There's a saying: do one thing well . Software doesn't weight or take up space. Then there's high level programming where you sew together high level modules.

> There's a saying: do one thing well.

The problem is that not everyone has the same needs, and there aren't enough programmers to write special purpose software for each person on the planet. Making software customizable is a way to give users a powerful tool that can be adapted to their needs.

> Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.

Exactly. If we view personal computing more like a "medium for literacy," then it becomes clear that computing power is wildly imbalanced. This is especially egregious when we consider the original goals of personal computing. Spreadsheets, Hypercard, and things like them were a totally different direction for computing that has largely since been abandoned in favor of market-dictated "wants" and shrink-wrapped "solutions." The upshot is that the people who should be most in the know -- computer people, developers, etc -- are stuck in a rigid world of epistemic closure (where, for example, Unix is more or less the "End of History")

I have yet to find a better definition of programming than "telling a computer what to do." When put this way, a lot of things that the trade-school approach consider "programming" are really only a subset -- and perhaps the most regressive and uninteresting to boot.

What common scenario do you envision where version control is useful?

A file that is edited by more than one person, of which at least one doesn't quite now what he does? It's pretty hard to see what changed from one version to another, and a destroyed formula can go undetected for a while...

And obviously, merge would be nice if multiple people can legitimately edit concurrently.

In spreadsheets (excel) and notebooks (ipython/rstudio), having the data bundled with the code is a feature. It provides reproducibility.

> a destroyed formula can go undetected for a while...

That is a problem in methodology, not with the tools. There are many solutions that do not require abandoning spreadsheets.

For the destroyed formula example, you need tests. Simple example to do that with a checklist: if you are doing a SUM(), require that the employee ticks a box saying "all the number were highlighted when clicking on the SUM formula".

For the out-of-sync version, you need a central repository and another box "I retrieved the latest version from the xx repository, and this version was: ... "

Then require that to be printed and signed (accountability), and you'll see mistake disappear.

Data bundled with the code is good, but code mixed with the data is a whole different thing. Notebooks are better than Excel in that regard I think.

As for expecting people to religiously and accurately observe procedures, I work with human beings, you seem luckier...

After they received training when they learn that this step is important and not just a formality, and why it is done, I found that people do observe procedures, especially when they have to sign the checklist they filled themselves.

Human beings observe procedures when they understand it's part of the job and they are held accountable.

Is it any different with code? Developers don’t always stick to the procedures either.

I have ‘unit tests’ in my excel files on the last tab. Eg, the sum of all lines in the Data tab must be the same as the sum of the annual revenues in the dashboard tab.

With a bit of conditional formatting it’s easy to see if a test is failing as well.

I like programming for lots of things, but ask me to do a business case or some one-off analysis and I’d use Excel in most cases.

Version control is extremely useful in software development because code modification by multiple authors is a primary use-case that happens multiple times throughout the day.

Maybe your experience is different, I haven't seen a use-case where multiple authors are changing excel macros or formulas that often, or even often enough to where this is an issue. In fact I think adding version control would make things very very confusing for most people.

Aside from Excel's built-in "Track Changes" features (which stores the revision log within the file), the "Spreadsheet Compare" tool that ships with Excel is a decent diff engine that can be paired with any VCS. Sharepoint is another "official" solution.

Unstructured use of Excel is a completely orthogonal problem.

IIRC, Excel XLSX files are just ZIP archives with a bunch of XML inside. Therefore, if you can come up with a way for a version control system to transparently look inside ZIP archives, it ought to be possible to version-control XLSX files using the same tools you'd use to version-control any text files, no?

(Though this only solves half the problem, the other half being that even if you can wrangle Git or the like to version files inside a ZIP, Git is far from being a tool that could be considered appropriately usable to dump on the Excel audience...)

It isn't as simple as that. There are numerous equivalent representations of the same underlying content (same data, same styling, same charts, etc) that have wildly different XML representations. You need a content-aware diffing tool.

FWIW we've explored that idea in the past, using git textconv to diff spreadsheets https://www.npmjs.com/package/j#using-j-for-diffing-spreadsh... -- it seems nice on paper, but quickly becomes hairy

> mix freely data and code in the same sheet

where is this unnecessary lisp hate coming from? /s

And like any tool they can be abused - I really like Excel but I've seen it used for things that were unwise.

I've spent months reverse engineering a vast, complex, costing application that had evolved within a spreadsheet and needed (for very good reasons) to be turned into a proper application. The really entertaining part was trying to convince people that their spreadsheet didn't actually do what they thought it did....

Edit: It was costing a complex heavy industrial process so it had logic that was basically physics right through to finance.

I think the solution should be to have better excel reverse engineering tools. I have seen plenty of crazy complex spreadsheets that had hit a wall and were hard to refactor but the solution would never have happened if they hadn’t had excel enabling regular users to create it.

I don’t think Excel is ever bashed for its primary use case. It’s when someone starts building complex, multi user applications things start to go wrong. It almost always becomes an entangled mess. And good luck to anyone taking over when the creator leaves. I’ve been both the creator and maintainer of such Excel applications, and I do not miss it.

One thing that frequently comes to my mind - rather than trying to convince people to move to a general-purpose programming language (and ignoring all the reasons that people turn to Excel in the first place), is it possible to turn an Excel-style spreadsheet into a general-purpose programming environment that is powerful enough to do that sort of stuff?

People really underrate Excel as a programming environment, honestly. Yes, it's crippled and leads people to produce massive gross un-debuggable hellsheets ... but there's reasons (beyond just "it was the only usable software that could be run on office computers") that end users with a problem to solve keep turning to it despite those flaws. The combination of reactive programming, data-first visibility, no hidden state, decent approximations to structured programming by way of click-drag-and-copy-paste, and being able to reference variables and values without needing to name them has some kind of magic to it.

There's quite a bit of interesting research I've seen from Microsoft on how to take something like Excel and turn it into a non-crippled programming environment - spreadsheet-defined functions (including recursion, lambdas, and higher-order functions natively in the spreadsheet environment, without having to drop into VBA!), dynamic arrays, an alternate computation-first textual view that exists simultaneously with the data-first spreadsheet view, first-class complex data structures ...

Between HyperCard, Jupyter/Mathematica notebooks, and Excel, there's a lot of common features that seem to point to a vision of a truly useful end-user-accessible programming tool, something that would let people write real, useful programs intuitively and quickly enough to be worth building them themselves for whatever they needed them for, without forcing them into condescendingly-simplified drag-and-drop "visual programming" codeless clunkfests.

Excel is a general-purpose programming environment, it has a full programming language inside it (VBA) with the ability to call out to the operating system and load functionality through DLLs.

No, the way to tame Excel is to make it less general, to impose principles of structured programming on it. Separate code and data and presentation. Put some of the data in a database. Impose types on it, so Excel doesn't interpret genomes as dates etc. Make the control and data flow visible. Split it into pieces with defined responsibilities.

Excel is really a "two dimensional assembler": you put an operation in each cell, and they can read any other cell, and it all executes together.

In my first job out of Uni I wrote some VBA to make requests to the "online diary" to pull out some of the information into some template Excel files we had to create.

Some poor soul is probably still maintaining that.

>Separate code and data and presentation.

This is the big one.

> VBA

And now javascript.

https://docs.microsoft.com/en-us/office/dev/add-ins/quicksta...

After switching from technical to a product role, I have seen a complete change of perception towards excel/google-sheets. I still observe the passive hate towards excel by developer companions, but for me, it's a goto tool, the same as it was the case with Python, the goto language.

I like to call it a fundamental or building block technology that enables you to develop things on top of it, my favorite being a finite-state-machine we modeled in a sheet. Won't be surprised if we end up talking about Google sheets being one of my favorite products.

Because lots of people have lived through a spreadsheet that had grown too large and too important, and the original author might be gone, and reverse engineering a spreadsheet that's hundreds of megabytes in size and has a dependency graph that's longer than a page quickly becomes... burdensome.

because the one you got emailed last year is out of date vs the one your boss got emailed last month which is also out of date from the finance team's version

Not only that, but Excel can tether back to a traditional rdbms for pull/push data. It's often the preferred way for PMs to interact with, for example, VSTS/TFS. There is absolutely a LOT that can be done with Excel and other spreadsheets, and it shouldn't be underestimated.

I'd rather not work with them more than basic/classic usage of a more well formed data dump. I use spreadsheets for monthly finances, some historical information that fits well, and a few other use cases. I'm not big on VBA and have little desire to work in the space.

I have, however, seen some amazing usage of Excel and other spreadsheets for everything from charting and pivot data, to multi-user applications tethering to other data stores.

As usable data starts getting larger, the trade off between Excel and databases will get smaller.

Agreed. The bashing does not really come from the end users / business people who benefit from the spreadsheet but those that are either competing against it (engineers) or having to take on risk through misuse (compliance).

As other comments have noted, it gives rise to the new problem of the proliferation of stale, incomplete, or otherwise incorrect data.

Google Sheets, on the other hand, is truly compelling, and a lt of the work I've been doing lately has been using Sheets and Apps Script to build really powerful "apps" on the super-cheap. This is the stuff we used to do with VB years ago, and it would take weeks. Now I can get at least a PoC of a fully multi-user system in front of a client in a day, and they don't pay anything to host it.

> inclusive way..

How is a spreadsheet “inclusive?” What does that mean?

It means that non-programmers can do "programming".

The quotes seem unnecessary. Programming isn't just an expert activity. Many applications offer macro recording and other facilities to create programs without scripting. Dataflow programming is often used in graphics and media applications. Spreadsheets are one very successful implementation of dataflow programming.

What Excel doesn't let you do (without heroic effort in support toolchains) is software engineering...

It also allows domain experts to program. So while they may not be expert programmers, they are experts in the problem area and will come up with expert solutions.

I would wager that 10 times (perhaps 50 times or more) as many people can make a simple Excel worksheet as could program the same functionality in a mainstream, general purpose "programming" language.

Reply on news.ycombinator.com