4 ms·

Excel is fine until you have to work with nested data structure/relationship.

x38iq84n 7y ago
Or until a member on the team has an older version of Excel that causes compatibility problems for the rest of the team. Or until a spreadsheet is edited in Excel that uses language other than English - formula functions will also take nationalised names. Or until multiple people make changes to a spreadsheet, but there is no version control in Excel.
- simonh 7y ago
  Version control is a big one for financial institutions, because there's no way to verify what the state of a spreadsheet was when a particular analysis was done. One place I worked at created a Python library called the DAG (Directed Acyclic Graph) for this. It enabled an easy way to translate a chain of Excel cell functions into Python code, which can then be version controlled, diffed, code reviewed, etc.
kfk 7y ago
And this could be the true Excel killer. If we figure out a paradigm for nested structures (or simple sql relationships really) that is as easy to work with as Excel we would have a very strong case for moving users away from spreadsheets. Every time data is interrelated in Excel it becomes a hot, very manual, mess.
- jsmith99 7y ago
  Excel allows creating primary/foreign key relationships between tables using the Data tab, but you can only use these relationships to produce pivot tables, not in normal formulas.
  - collyw 7y ago
    But buy the time you get to understand how to do things like this in Excel, why not just use the proper tool for the job - a database. (I haven't used it in many years but MS Access was a pretty efficient way to get an application together).
  - froindt 7y ago
    Admittedly not great, but you can complete that join, output it as a table, and then apply formulas as desired if you use Power Query. Power Query solves many cases where a macro was previously required, but relatively few people are aware of it.
Scarbutt 7y ago
I think this is why the invented Access.

Or until a member on the team has an older version of Excel that causes compatibility problems for the rest of the team. Or until a spreadsheet is edited in Excel that uses language other than English - formula functions will also take nationalised names. Or until multiple people make changes to a spreadsheet, but there is no version control in Excel.

simonh 7y ago
Version control is a big one for financial institutions, because there's no way to verify what the state of a spreadsheet was when a particular analysis was done. One place I worked at created a Python library called the DAG (Directed Acyclic Graph) for this. It enabled an easy way to translate a chain of Excel cell functions into Python code, which can then be version controlled, diffed, code reviewed, etc.

kfk 7y ago

And this could be the true Excel killer. If we figure out a paradigm for nested structures (or simple sql relationships really) that is as easy to work with as Excel we would have a very strong case for moving users away from spreadsheets. Every time data is interrelated in Excel it becomes a hot, very manual, mess.

jsmith99 7y ago
Excel allows creating primary/foreign key relationships between tables using the Data tab, but you can only use these relationships to produce pivot tables, not in normal formulas.
- collyw 7y ago
  But buy the time you get to understand how to do things like this in Excel, why not just use the proper tool for the job - a database. (I haven't used it in many years but MS Access was a pretty efficient way to get an application together).
- froindt 7y ago
  Admittedly not great, but you can complete that join, output it as a table, and then apply formulas as desired if you use Power Query. Power Query solves many cases where a macro was previously required, but relatively few people are aware of it.

Scarbutt 7y ago

I think this is why the invented Access.