3 ms·

Imho, the problem is Excel has so many common-law bugs and boneheaded workflows they're stuck with for historical reasons that the state of the art can't improv

by Pxtl 9y ago

Imho, the problem is Excel has so many common-law bugs and boneheaded workflows they're stuck with for historical reasons that the state of the art can't improve. The whole "add a row on the bottom and find that none of the styles apply or formulas have extended that far" is moronic. Not to mention the boneheaded behavior when you paste anything that looks like it could be data (even if it is a date, but in a slightly diffferent format) and it just gets perma-mangled. Excel is close to good, but it can never be actually good because it's got decades of workflow to protect.

viraptor 9y ago
> add a row on the bottom and find that none of the styles apply or formulas have extended that far Tables (or lists in previous versions) solve that issue nicely.
- Pxtl 9y ago
  Really? I'd heard that "Format as table" made that happen, but I tried it recently and the formulas and constraints didn't continue as I added rows to the bottom. Is there some secondary thing also called "table" that I should be using?
  - dragonwriter 9y ago
    Create Table is a different command than Format as Table. The former creates a data table, the latter format cells to provide the appearance of a table.
    - Pxtl 9y ago
      Oh. Well that's perfectly clear.
    - phonon 9y ago
      Format as Table turn the area into a Table also. "When you have a data range that is not formatted as a table, Excel will automatically convert it to a table when you select a table style." https://support.office.com/en-us/article/Format-an-Excel-table-6789619f-c889-495c-99c2-2f971c0e2370 https://support.office.com/en-us/article/Format-an-Excel-tab...
  - viraptor 9y ago
    As others said - you need to create a table instead. Terrible naming but awesome functionality. It's the best hidden secret of excel. It also has the side effect of naming the whole range so you can easily lookup() in just that scope. (Or in general reference values without an offset on the page and with proper column names)
  - phonon 9y ago
    https://support.office.com/en-us/article/Use-calculated-columns-in-an-Excel-table-873fbac6-7110-4300-8f6f-aafa2ea11ce8?ui=en-US&rs=en-US&ad=US&fromAR=1 https://support.office.com/en-us/article/Use-calculated-colu...
deleted 9y ago
[deleted]
tjalfi 9y ago
Have you seen the Joel Spolsky video You Suck at Excel [0]? He demonstrates how using tables can avoid many of the common pitfalls. [0] https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c
- mschaef 9y ago
  Data tables are a huge win where they apply... they can result in significant performance improvements in addition to all the other benefits. (A former co-worker of mine once wrote a simulation using Excel, and switching to tables give the system several orders of magnitude better performance.)

by Pxtl 9y ago

viraptor 9y ago

> add a row on the bottom and find that none of the styles apply or formulas have extended that far Tables (or lists in previous versions) solve that issue nicely.

Pxtl 9y ago
Really? I'd heard that "Format as table" made that happen, but I tried it recently and the formulas and constraints didn't continue as I added rows to the bottom. Is there some secondary thing also called "table" that I should be using?
- dragonwriter 9y ago
  Create Table is a different command than Format as Table. The former creates a data table, the latter format cells to provide the appearance of a table.
  - Pxtl 9y ago
    Oh. Well that's perfectly clear.
  - phonon 9y ago
    Format as Table turn the area into a Table also. "When you have a data range that is not formatted as a table, Excel will automatically convert it to a table when you select a table style." https://support.office.com/en-us/article/Format-an-Excel-table-6789619f-c889-495c-99c2-2f971c0e2370 https://support.office.com/en-us/article/Format-an-Excel-tab...
- viraptor 9y ago
  As others said - you need to create a table instead. Terrible naming but awesome functionality. It's the best hidden secret of excel. It also has the side effect of naming the whole range so you can easily lookup() in just that scope. (Or in general reference values without an offset on the page and with proper column names)
- phonon 9y ago
  https://support.office.com/en-us/article/Use-calculated-columns-in-an-Excel-table-873fbac6-7110-4300-8f6f-aafa2ea11ce8?ui=en-US&rs=en-US&ad=US&fromAR=1 https://support.office.com/en-us/article/Use-calculated-colu...

deleted 9y ago

[deleted]

tjalfi 9y ago

Have you seen the Joel Spolsky video You Suck at Excel [0]? He demonstrates how using tables can avoid many of the common pitfalls. [0] https://www.youtube.com/watch?v=0nbkaYsR94c https://www.youtube.com/watch?v=0nbkaYsR94c

mschaef 9y ago
Data tables are a huge win where they apply... they can result in significant performance improvements in addition to all the other benefits. (A former co-worker of mine once wrote a simulation using Excel, and switching to tables give the system several orders of magnitude better performance.)