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

81–90 of 123 posts

Re: Use Spreadsheets Everywhere

#81

Post by Vaslo »

People understate the power of spreadsheets. You are literally using a visible programming language. When I have to write a algorithm to read from a file or a spreadsheet, I can model it very easily on the spreadsheet to get all my i's, j's, and k's right, and I can watch it occur incrementally line by line in a spreadsheet. When I was buying my house, I was able to easily line by line show my wife where all the cash was going. No print statement, variables, or anything I'd need in Python or C++. I could do it in 5 mins and it was as good as anything those languages could have done.

All that said, I can see issues with errors that are hidden by spreadsheets but those can be handled with good spreadsheet design (i.e. NEVER EVER hard code anything into a cell) and they simply just need a way to do GIT type version control natively. We recently got the ability to have multiple people work in the same spreadsheet at a time if on MS Teams, but there's more work to do there.

Re: Use Spreadsheets Everywhere

#82

Post by bitwize »

One thing I'm surprised hasn't taken off is something like Framework. Framework was a hybrid word processor/spreadsheet/database/graphics/communications program based around the unifying concept of a frame, all scriptable in a Lisp-like programming language from which every frame -- down to individual spreadsheet cells which counted as frames -- was directly addressable. Frames could even serve as sources of input or sinks for output to/from external programs or remote machines.

Framework was pretty much Emacs for the office, and such was its power that unlike contemporary spreadsheets and word processors, which were usually positioned as productivity tools for generating business documents and reports, Framework was explicitly marketed as a decision making tool for executives. It did well in markets such as Europe where Lotus hadn't taken over, but it just about died when the Windows era started.

Re: Use Spreadsheets Everywhere

#83

Post by jjtheblunt »

tomnipotent wrote: post #46
LegitGandalf wrote: post #39

Earlier quoted context omitted.

Tables are probably the most overlooked feature of Excel. Why use tables? * Each column is uniquely named - no more wondering if you are referencing the right cell, no more thinking about "to $ or not to $" * The table's rows and columns are reliably discovered by pivot tables - no more wondering if the entire dataset is referenced by the pivot * New columns that are formulas are automatically applied to every row *…

Most people have no clue these features exist, or that Excel even has advanced data modeling that supports all sorts of goodies including strict types and joins/merges/appends. It's not as accessible as just basic sheets, but I've had no problem crunching millions of rows of data in Excel with sub-second response times.

isn't excel limited to just over a million rows?

Re: Use Spreadsheets Everywhere

#84

Post by dragonwriter »

jbverschoor wrote: post #71
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…

Data tables: https://youtu.be/0nbkaYsR94c?t=2268 Although no validation/types

Excel has, IIRC, had data validation longer than it has had tables (even if you count the time before Excel 2007 when tables were called lists),and its had both (with the same inclusion) for >20 years.

Re: Use Spreadsheets Everywhere

#85

Post by asdff »

antipaul wrote: post #80
asdff wrote: post #69

Earlier quoted context omitted.

There is so much excel voodoo involved to do things with that software. I still believe that the learning curve of excel is no harder than the learning curve of doing the same exact thing in R or python, plus you'd end up having the data and the formulas in different places which brings loads of benefits (for instance, git). People are just familiar with excel because thats what they used to make a chart in science c…

I dunno, any programming seems a different beast than a spreadsheet tool. For python, just think of installing it or setting up virtual environments. Like the original article says, “spreadsheets are the original low code”

Python is preinstalled on macs at least, that's a decent chunk of personal computers. You have to pay for excel. And you don't need to bother with virtual environments to fiddle with a flat file.

Re: Use Spreadsheets Everywhere

#86

Post by marto1 »

Vaslo wrote: post #81

People understate the power of spreadsheets. You are literally using a visible programming language. When I have to write a algorithm to read from a file or a spreadsheet, I can model it very easily on the spreadsheet to get all my i's, j's, and k's right, and I can watch it occur incrementally line by line in a spreadsheet. When I was buying my house, I was able to easily line by line show my wife where all the cash…

I'm actually the other way around. While visualizing algorithms might be easily done in a spreadsheet, coming up with algorithms is very hard for me and gets me confused easily. Same goes for diagrams, when I lookup a protocol I quickly go for a well commented implementation and RFC only for variables and descriptions.

Maybe it's just me.

Re: Use Spreadsheets Everywhere

#87

Post by etskinner »

Vaslo wrote: post #81

People understate the power of spreadsheets. You are literally using a visible programming language. When I have to write a algorithm to read from a file or a spreadsheet, I can model it very easily on the spreadsheet to get all my i's, j's, and k's right, and I can watch it occur incrementally line by line in a spreadsheet. When I was buying my house, I was able to easily line by line show my wife where all the cash…

> (i.e. NEVER EVER hard code anything into a cell)

Define 'hard code'...

Is "=MONTH(A1)+2" no good when I'm trying to write a row of every other month?

Is it bad to say '=IF(A1="USA",TRUE,FALSE)' when I'm trying to see what customers belong to our USA office?

I have this problem with coding too. In my mind, it's okay to hard code things, since you can always debug and abstract them later. Excel's "Evaluate Formula" and "Trace Precedents" are both pretty handy tools.

Re: Use Spreadsheets Everywhere

#88

Post by mmckelvy »

I think a lot of the "problems" with using large scale spreadsheets can be somewhat ameliorated by moving the underlying data to a proper database and then using the spreadsheet as a GUI / interactive analytical tool. Now that you can make external API calls in Excel[1], this actually seems doable.

[1] https://docs.microsoft.com/en-us/office/dev/scripts/develop/...

Re: Use Spreadsheets Everywhere

#89

Post by castillar76 »

There's a real back-and-forth struggle for me with spreadsheets. I find people very frequently reach for them for things that they do really well: data slicing and dicing, ordering and sorting, formulae that cross-reference cells, and so forth. However, the contents of those spreadsheets are often (for me) not numeric, but text, and working with text that's longer than a few words in Excel is still a huge PITA, even after all these years, because Excel still thinks of the contents of cells as numbers first.

Consider the output of your average audit. You'll have tables of findings, each of which needs a due date, a risk rating, a description of the problem, a description of the solution, auditor notes, customer comments, responsible party assignments, and so forth. (Yes, those would eventually go well in a tool like Jira, but that's for later--this is coming out of an audit visit.)

From a data standpoint, putting those in a spreadsheet makes sense: you can now order the findings by date or risk rating, hide ones that don't apply, cross-reference findings between visits, and so forth. However, from a text perspective, it's awful: the descriptions might run to multiple paragraphs, comments and instructions need more complicated formatting than just "bold or italic", some fields should be constrained on content while others need to be free-form, and so forth. All of those things work much better in a Word table than in Excel cells, but putting the content in Word utterly removes the ability to data-manipulate. So you wind up either creating some Frankenstein hybrid solution or with crushing one perspective to satisfy the other.

If Microsoft wants a win for Excel, making it an order of magnitude easier to deal with free-form text in cells would be an enormous step forward.

Re: Use Spreadsheets Everywhere

#90

Post by tomnipotent »

jjtheblunt wrote: post #83
tomnipotent wrote: post #46

Earlier quoted context omitted.

Most people have no clue these features exist, or that Excel even has advanced data modeling that supports all sorts of goodies including strict types and joins/merges/appends. It's not as accessible as just basic sheets, but I've had no problem crunching millions of rows of data in Excel with sub-second response times.

isn't excel limited to just over a million rows?

Excel 2013 and later has a columnar database capable of handling millions of rows, but not through the standard sheets interface so it loses a lot of the utility people are used to.
Post reply on HN