7 ms·
The example of "why spreadsheets bad" made me wonder - why don't spreadsheets have a visual indicator for cells with a formula rather than a static value?
by lukasb 3y ago
The example of "why spreadsheets bad" made me wonder - why don't spreadsheets have a visual indicator for cells with a formula rather than a static value?
- galaxyLogic 3y agoThat would be the most obvious improvement and makes we wonder why it's not there after all these years. A 2nd improvement I would go for would be to allow the cell-formula to be written in JavaScript.
- threePointFive 3y agoYou've been able to write custom formulas in VBA in Excel for a while now. The same functionality exists in Google Sheets with the much nicer macro language of JavaScript. https://developers.google.com/apps-script/guides/sheets/functions https://developers.google.com/apps-script/guides/sheets/func...
- kevingadd 3y agoIirc Excel has a dependency visualizer that will highlight formulas. It's called Trace dependents
- wslh 3y agoI think the spreadsheet metaphor is right but the spreadsheet UX/UIs has not aged well. Why Microsoft would do important changes into one of its cash cows and suffer a new wave of bugs and incompatibilities?
- timeagain 3y agoIt’s true that a new change can intriduce more bugs, but I have a suspicion that the excel team is working with brains and a test harness that most software projects don’t have the time or money for.
- wslh 3y agoThe problem is: if you were the chief product manager of Excel you would risk yourself? New tests are required for new features and it's a difficult execution that money doesn't buy by itself. Is this a significant change to risk your job? Big companies have all the money to modernize their systems yet they know the execution is difficult if not impossible to achieve in time. The classical story about running an older operating system and/or application that works instead on venturing on modernizing it.
- jamses 3y agoCtrl+` will show the underlying formula, or you could use conditional formatting to apply whatever style you want to static values =NOT(ISNUMBER(FIND("=",FORMULATEXT(A1)))), or you could write a VBA macro to do that and more (e.g. find all the formula that have been zeroised, etc).
- n_plus_1_acc 3y agoWhy not ISFORMULA
- analog31 3y agoI did exactly that with a VBA macro, that indicated whether something was a formula, a constant, or empty. I would just put it next to my column of calculations. It was not what I would call "clean code" but sure made it easier to follow my thought process and find bugs. The macro was the first thing that came to mind. Your formula is cleaner.
- Tomte 3y agoAnd that shortcut is very annoying to type on a German keyboard layout.
- qsi 3y agoYou could make it a matter of style discipline. In my non-trivial spreadsheets I typically have cell formatting conventions, e.g. light yellow background is input, light green is formula, etc. You do have do it manually but it's worth the effort in my case.
- binarymax 3y agoExcel does do a good job of showing inconsistent cells in columns, it shows a green corner. But I do like the idea of different kinds of cells having a different style entirely. You can style them yourself but a default theme would be a cool feature.
- layer8 3y agoWhich cells or columns are computed and which are input values is often immaterial when viewing the data, and could as well be the other way around. Having a forced visual indication would create an emphasis or distinction that is distracting with respect to using and interpreting the displayed data. When needed, a visual indication can easily be created with conditional formatting, or with the Show Formulas command (Ctrl+`), as noted by the sibling. Know your tools is the answer here.
- galaxyLogic 3y ago> visual indication would create an emphasis or distinction that is distracting I don't agree with that. It's like saying in JavaScript the "const" -keyword is distracting because it creates a distinction between values that are constant and values that change during running the program. The spreadsheet-app might have a "publish-mode" where the distinction between inputs and formulas is hidden but in general it helps that user can easily distinguish between between inputs and procedures. Distinction does not imply distraction. What is often distracting is the need to spend mental effort to make a distinction, to try to understand a spreadsheet. You might as well say that gaslighting in general reduces distraction, because there are fewer distinctions you can make in the gas-light.
- layer8 3y agoYou’re arguing from a programmer’s perspective. Most spreadsheet users don’t think that way. The data and what it says is in the front of their mind, and is what primarily interests them about the spreadsheet, not the “coding” of the spreadsheet. The formulas are merely a means to get the data displayed they want to see.
- dspillett 3y ago> It's like saying in JavaScript the "const" -keyword is distracting Though like you can optionally use const (for varying degres of optional depending on your local coding style rules), you can optionally highlight formulas using conditional formatting based on the ISFORMULA() function. It does seem like something that makes sense to be a built-in though.