Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Use Spreadsheets Everywhere

simplethread.com

Post reply on HN
Print view View original

21–30 of 123 posts

Re: Use Spreadsheets Everywhere

#21

Post by pico303 »

ghaff wrote: post #13
spicybright wrote: post #6

Dang, what software engineers hate spreadsheets? I've never worked anywhere that didn't use them for project planning and other such stuff.

The post is probably something of a straw man. I think the "hate" is mostly about spreadsheets being used as effectively hard to audit spaghetti code for for tasks that would be better coded in some Python or R. I've probably never been a real spreadsheet power user (though I've had some pretty big ones). But they're hard to beat for any sort of semi-structured tracking. I sometimes wonder if spreadsheets as we know…

It is a straw man. Software engineers don't hate spreadsheets when they're used as spreadsheets, like this article describes. They hate the "spreadsheet as a database," particularly when they're asked to load those spreadsheets into a proper database or other system periodically. Spreadsheets are so easy to use for tabular data, and since technical and non-technical people alike can easily use them, it's a tempting data transmission protocol. But most non-engineering types aren't disciplined about the layout, or understand the intricacies of interpreting the data (e.g. putting labels into numeric columns, adding accidental spaces to the end or beginning of labels), leaving the engineers to constantly rewrite sometimes complex scripts to load new data in this month's "flavor" of spreadsheet.

Re: Use Spreadsheets Everywhere

#22

Post by CivBase »

spicybright wrote: post #6

Dang, what software engineers hate spreadsheets? I've never worked anywhere that didn't use them for project planning and other such stuff.

Software engineers who have had to deal with spreadsheets being used inappropriately. Spreadsheets are an extremely powerful tool, but they have limits. Their accessibility is part of what makes them so ubiquitous, but it also means people get comfortable and start using them beyond their limits.

Spreadsheets (Excel ones in particular) are great and presenting tabular data and they're mediocre at most other things including crunching numbers, taking notes, storing computational data, providing user interfaces, and much more. It's often okay to use a sub-optimal tool, but you have to draw the line somewhere.

A recent HN post[0] highlighted an example where Excel was being used to keep track of large-scale contact tracing data for covid in the UK. Excel is a mediocre database. It has hard limits on cell sizes and row/column counts among other things. They ran into one of those limits and lost track of 16K positive cases because of it.

From my own experience, I've had to deal with repositories containing tens of thousands of Excel spreadsheets. They were used to capture verification data. Excel files are large and difficult to parse with scripts, which was bad enough. But the worst part was that Excel doesn't really have a syntax or schema, so users editing the spreadsheets would frequently create changes to the table layouts which would have to be accounted for as edge cases in scripts. I'd be lucky to even recover data from half the files using automation. I even encountered one Excel workbook with over 300 tabs!

Every tool has limits and it can be frustrating working with popular tools when users fail to recognize those limits. I love working with Python, but I'd never try to write a kernel with it. Likewise, there is a time and place for spreadsheets.

[0] https://timharford.com/2021/07/the-tyranny-of-spreadsheets/

Re: Use Spreadsheets Everywhere

#23

Post by redler »

laGrenouille wrote: post #16

I agree with a lot of the points raised here. I think many of the problems with spreadsheets are due to the software rather than the users. As mentioned in the article, its hard to slowly iterate from a small manageable spreadsheet to an larger software solution. For example, Excel would be a lot more usable and maintainable for me if there was a way to make a special "data sheet" in which data types are forced to be…

I'm not sure about type enforcement, but there is the concept of "named ranges" you can apply to columns. So instead of C1:C99 you can refer to PRICES.

Re: Use Spreadsheets Everywhere

#24

Post by dreamer7 »

flerovium wrote: post #7

It's the interface: a grid of editable cells is incredibly easy to use and as almost as information-dense as possible. It's interesting to see non-excel/google sheets products use the same interface to good effect. Examples don't come to mind but I'm sure there are some.

This is a valid point! Anyone who has built an admin dashboard knows that the UX of being able to edit any field in any row on an Excel is hard to replicate without additional edit buttons and dialogs popping up.

Re: Use Spreadsheets Everywhere

#25

Post by magwa101 »

I always have the challenge of balancing reusable cells vs duplication. If I go too far into reusable cells (ie. functions) my spreadsheet becomes to complicated. Spreadsheets are not quite given to programming, duplicated data gives transparency and can be the best approach. I love spreadsheets.

Re: Use Spreadsheets Everywhere

#26

Post by redler »

linker3000 wrote: post #20

If you have had to fill multiple, spreadsheet-based, customers' InfoSec questionnaire about your SaaS product, including the need for tailored, multi-paragraph answers (ie: you can't copy/paste from stock responses), you might disagree with the raw headline.

And when you have an answer that exceeds the height of the tiny cell, you can't turn on "Wrap Text Automatically" so you can see the content -- because they've locked any changes to the sheet structure.

Re: Use Spreadsheets Everywhere

#27

Post by Ajedi32 »

laGrenouille wrote: post #16

I agree with a lot of the points raised here. I think many of the problems with spreadsheets are due to the software rather than the users. As mentioned in the article, its hard to slowly iterate from a small manageable spreadsheet to an larger software solution. For example, Excel would be a lot more usable and maintainable for me if there was a way to make a special "data sheet" in which data types are forced to be…

I've had similar thoughts in the past. Databases are very machine-friendly, but too static and inflexible to match the usability of spreadsheets. Spreadsheets are extremely user-friendly, but too inconsistent and unconstrained to be efficient for programmatic access.

It seems like there should be a way to combine the two. Maybe a minimal set of optional constraints (like a separation between data and code) like you proposed would be a good starting point. Make tables a first-class citizen backed by an embedded SQLite database (or something similar); let users write real SQL to query tables in formulas, maybe update the file format a bit to make it easier for programs to parse and access concurrently. Could be an interesting project...

Re: Use Spreadsheets Everywhere

#28

Post by CivBase »

zwieback wrote: post #5

Article raises some valid points but how do you catch the point where the spreadsheet gets out of control? Maybe MS could build in a warning system and then suggest some potential solutions. Minor quibble: it's an Apple III in the ad, not an Apple ][ as the post says. I remember running Visicalc on my Apple ][ but it wasn't until Excel appeared that I realized how seductive spreadsheets can be.

Clippy: I see you're trying to create a database. Would you like to use Access instead?

I'm not sure this is really a problem Microsoft can solve as an Excel feature. I think the better approach would be for MS to identify common ways in which people misuse spreadsheets and create new tools to accommodate those use cases. With how commonly Excel is misused as a database, I think there's a market for an alternative to Access which is more accessible (no pun intended). Something with Excel-like spreadsheets/tabs to represent tables and Excel-like formulas for computed columns would be pretty neat.

Post reply on HN