4 ms·

The way Excel lays out data and intermediate states visually is pretty intuitive (compared to, say, using a step-through debugger), but the formula editing pane

by giaour 4y ago

The way Excel lays out data and intermediate states visually is pretty intuitive (compared to, say, using a step-through debugger), but the formula editing pane has an atrocious UX. I would love to see Excel but with a scratch/snap-like interface for authoring formulas.

nerdponx 4y ago
The more I use Excel, the more I feel that its primary limitation is its UX for working with formulas.
- willhslade 4y ago
  Check out the new LET function.
  - rbonvall 4y ago
    LET is a great syntactic improvement, but the UI is still the same. I'd love if the formula box supported the basic things one expects from a code editor, like syntax highlighting, formatting, indentation, comments, etc.
criddell 4y ago
The formula editor is pretty bad. If Microsoft fixed it and added GitHub integration easy enough for non-programmers, I think a lot of concerns about usage of Excel could be dealt with.
Xeamek 4y ago
This is actually great idea. If microsoft can add block-building programming for minecraft edu edition, why wont they do that to their flagship tool that would also help them in their competition against Google sheets. Especially since pretty much every year we hear another story where some small Excell formula bug cost company millions of dollars. Redesining Excel formula pane should be their number 1 feature in 'todo'. ...unless they still care to add big features at all, and they aren't just in maintenance mode like most of their stuff
rahimnathwani 4y ago
> I would love to see Excel but with a scratch/snap-like interface for authoring formulas. Long/complex Excel formulae should usually be broken into smaller chunks, with intermediate results stored in separate cells. For example, if you're calculating two numbers, and then calculating their ratio, it would be better to use three formulae instead of one. That way: 1. Each formula is shorter. 2. Each formula has a single purpose which can be understood. 3. The outputs of the intermediate steps in the calculation are obvious. This applies also to complex nested IF() statements. Instead of calculating all the conditions inside the IF(), calculate them outside, and then reference the TRUE/FALSE cell values in your IF statement. When you need to debug why you're not getting the result you want, you can easily look at the intermediate calculations all at once, without needing to step through the calculation and check it in the order of calculation.
- giaour 4y ago
  Totally agree. This is exactly what I would do to debug a complex expression in a program: break out chunks into smaller expressions, assign their results to intermediate variables, then echo out or inspect those variables in a debugger. Once it clicks with users that an Excel cell is a variable, they tend to write more state out to the sheet so they can inspect it.
simulo 4y ago
Bas Jansen and Felienne Hermans created a block based language, XLBlocks. Read their paper here: https://www.researchgate.net/publication/336187404_XLBlocks_a_Block-based_Formula_Editor_for_Spreadsheet_Formulas https://www.researchgate.net/publication/336187404_XLBlocks_...

by giaour 4y ago

nerdponx 4y ago

The more I use Excel, the more I feel that its primary limitation is its UX for working with formulas.

willhslade 4y ago
Check out the new LET function.
- rbonvall 4y ago
  LET is a great syntactic improvement, but the UI is still the same. I'd love if the formula box supported the basic things one expects from a code editor, like syntax highlighting, formatting, indentation, comments, etc.

criddell 4y ago

The formula editor is pretty bad. If Microsoft fixed it and added GitHub integration easy enough for non-programmers, I think a lot of concerns about usage of Excel could be dealt with.

Xeamek 4y ago

This is actually great idea. If microsoft can add block-building programming for minecraft edu edition, why wont they do that to their flagship tool that would also help them in their competition against Google sheets. Especially since pretty much every year we hear another story where some small Excell formula bug cost company millions of dollars. Redesining Excel formula pane should be their number 1 feature in 'todo'. ...unless they still care to add big features at all, and they aren't just in maintenance mode like most of their stuff

rahimnathwani 4y ago

> I would love to see Excel but with a scratch/snap-like interface for authoring formulas. Long/complex Excel formulae should usually be broken into smaller chunks, with intermediate results stored in separate cells. For example, if you're calculating two numbers, and then calculating their ratio, it would be better to use three formulae instead of one. That way: 1. Each formula is shorter. 2. Each formula has a single purpose which can be understood. 3. The outputs of the intermediate steps in the calculation are obvious. This applies also to complex nested IF() statements. Instead of calculating all the conditions inside the IF(), calculate them outside, and then reference the TRUE/FALSE cell values in your IF statement. When you need to debug why you're not getting the result you want, you can easily look at the intermediate calculations all at once, without needing to step through the calculation and check it in the order of calculation.

giaour 4y ago
Totally agree. This is exactly what I would do to debug a complex expression in a program: break out chunks into smaller expressions, assign their results to intermediate variables, then echo out or inspect those variables in a debugger. Once it clicks with users that an Excel cell is a variable, they tend to write more state out to the sheet so they can inspect it.

simulo 4y ago

Bas Jansen and Felienne Hermans created a block based language, XLBlocks. Read their paper here: https://www.researchgate.net/publication/336187404_XLBlocks_a_Block-based_Formula_Editor_for_Spreadsheet_Formulas https://www.researchgate.net/publication/336187404_XLBlocks_...