7 ms·

but it’s somehow underused. Often people don’t even realise they should be creating tables at all. Power query and friends are black magic at that point. It

but it’s somehow underused. Often people don’t even realise they should be creating tables at all. Power query and friends are black magic at that point. It sounds slightly absurd but advanced Excel training is something I think many people should do. At $oldfinancejob the guys who ran the client professional development business clearly picked up on this and ran a very nice ‘Excel for financial modelling’ course as a free intro kind of thing - after using Excel for decades there were plenty of things I didn’t know about and now use. Excel is an incredibly deep product.

tda 4y ago
At my former company I first naivly tried to get more (non software) technical experts to use python and databases. That was quite an uphil battle, and we quickly pivoted to teach them how to make better use of excel. First and foremost: use tables. That easily leads to clearly deliniating input data, computations and output. Then we proceded to provide template sheets that use powerquery to fetch shared input data from centralised API's. This way we could let the end user do all the work, even though it might be a bit more messy/error prone. For well extablished workflows I could then take the excel program as a spec, and build a e.g. a webservice from that. Ss all the exceptions etc are already dealt with by the end-users, you can basically reverse engineer the spec from the excel sheet. As long as you can guide them to making legible excel sheets, it saves so much misunderstandings vs writing specs from scratch and building from that.
- MrsPeaches 4y ago
  > I could then take the excel program as a spec, and build a e.g. a webservice from that That sounds like a potentially great approach for consultancy business/product discovery.
  - lappet 4y ago
    you can already do this with Google Sheets. I wonder if Office 365 has similar capabilities.
    - Closi 4y ago
      It does, via Microsoft Flow
- ethbr0 4y ago
  Excel excels as an exploratory / rapid prototyping tool. Excel fails as an app development and execution platform, and specifically one integrated into a core business process. Everyone who's worked in enterprise long enough has seen both. It'd be great if there was an enforceable "modern mode" Excel flag that kept people from going nuts with macros and programmability, while retaining all its strengths.
- keithalewis 4y ago
  Bingo! Excel gives you a front row seat to the problems the business is trying to solve. It is usually not the best tool, but what they put together tells you what they need for a better solution. I push things into C++ and iterate until they are satisfied the numbers are right. Nobody wants to pay for Excel add-ins, but when they need the same numbers showing up in their production systems, they will write a bigger check for a platform independent library their IT team can just link to and call. I wrote this to make that easy: https://GitHub.com https://GitHub.com
feoren 4y ago
> they should be creating tables Tables are a wart on Excel. They don't fit the established idioms at all. There's a very long list of common & simple things that either break or get very clunky with tables, including: - Multi-row headers - Merged-cell headers - Headers with the same name (sometimes useful) - Formulas that cross rows (e.g. iteratively refer to the previous row) - Different table sections (e.g. a table-width merged row with one header) - Merged rows The benefit of tables is ... what? Slightly simpler formulas when all operands are in the same row? More automatic (and annoying) formatting? Almost everything people try to do with Tables is actually easier without them, and if it's not, you really just want a database.
- tda 4y ago
  Merged cells and multi row headers make data processing very difficult. These are best avoided in any sheet doing any computation. Only for reporting they are useful, especially when auto-generated as part if a pivot table. But I have never seen a legitimate use of merged cells in a computation. Row referencing formulas work fine in tables, but there might be better ways to achieve your goals if you need that a lot. Other benefits are input data type checking, auto "freeze panes" for header row, much easier plot and pivot tables, niver formatting, summary rows if needed. Best is of course referencing columns by name
- jen729w 4y ago
  Oh hard disagree, friend. I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in. I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.
  - xupybd 4y ago
    Have you tried XLOOKUP? I like it more than index match.
    - 4y ago
lenkite 4y ago
If your data is <10k rows, Excel is great. After that lots of stuff stops working. Stick to R or Python data science libraries for real work.
- MikusR 4y ago
  Which stuff exactly?
  - lenkite 4y ago
    filtering drop-down 10k list limit is the most obvious one. There is also the hard traditional 1,048,576 limit which many hobbyist Covid tracking folks ran into.
    - lostlogin 4y ago
      Hobbyists and the UK government. They somehow cooked up a spreadsheet that make them hit the limits much earlier. https://www.bbc.com/news/technology-54423988.amp https://www.bbc.com/news/technology-54423988.amp
      - Closi 4y ago
        This was a massively outdated version of the software - it’s not entirely surprising that if you use c10-year old software there are more limits. Excel has grown hugely since then.
        lostlogin 4y ago
        That’s completely alarming and much worse.
        lenkite 4y ago
        Excel limits haven't gone away: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 https://support.microsoft.com/en-us/office/excel-specificati...
Foobar8568 4y ago
These days I am working with data analysts who are using Excel, they were mind blown to discover power query...

tda 4y ago

At my former company I first naivly tried to get more (non software) technical experts to use python and databases. That was quite an uphil battle, and we quickly pivoted to teach them how to make better use of excel. First and foremost: use tables. That easily leads to clearly deliniating input data, computations and output. Then we proceded to provide template sheets that use powerquery to fetch shared input data from centralised API's. This way we could let the end user do all the work, even though it might be a bit more messy/error prone. For well extablished workflows I could then take the excel program as a spec, and build a e.g. a webservice from that. Ss all the exceptions etc are already dealt with by the end-users, you can basically reverse engineer the spec from the excel sheet. As long as you can guide them to making legible excel sheets, it saves so much misunderstandings vs writing specs from scratch and building from that.

MrsPeaches 4y ago
> I could then take the excel program as a spec, and build a e.g. a webservice from that That sounds like a potentially great approach for consultancy business/product discovery.
- lappet 4y ago
  you can already do this with Google Sheets. I wonder if Office 365 has similar capabilities.
  - Closi 4y ago
    It does, via Microsoft Flow
ethbr0 4y ago
Excel excels as an exploratory / rapid prototyping tool. Excel fails as an app development and execution platform, and specifically one integrated into a core business process. Everyone who's worked in enterprise long enough has seen both. It'd be great if there was an enforceable "modern mode" Excel flag that kept people from going nuts with macros and programmability, while retaining all its strengths.
keithalewis 4y ago
Bingo! Excel gives you a front row seat to the problems the business is trying to solve. It is usually not the best tool, but what they put together tells you what they need for a better solution. I push things into C++ and iterate until they are satisfied the numbers are right. Nobody wants to pay for Excel add-ins, but when they need the same numbers showing up in their production systems, they will write a bigger check for a platform independent library their IT team can just link to and call. I wrote this to make that easy: https://GitHub.com https://GitHub.com

feoren 4y ago

> they should be creating tables Tables are a wart on Excel. They don't fit the established idioms at all. There's a very long list of common & simple things that either break or get very clunky with tables, including: - Multi-row headers - Merged-cell headers - Headers with the same name (sometimes useful) - Formulas that cross rows (e.g. iteratively refer to the previous row) - Different table sections (e.g. a table-width merged row with one header) - Merged rows The benefit of tables is ... what? Slightly simpler formulas when all operands are in the same row? More automatic (and annoying) formatting? Almost everything people try to do with Tables is actually easier without them, and if it's not, you really just want a database.

tda 4y ago
Merged cells and multi row headers make data processing very difficult. These are best avoided in any sheet doing any computation. Only for reporting they are useful, especially when auto-generated as part if a pivot table. But I have never seen a legitimate use of merged cells in a computation. Row referencing formulas work fine in tables, but there might be better ways to achieve your goals if you need that a lot. Other benefits are input data type checking, auto "freeze panes" for header row, much easier plot and pivot tables, niver formatting, summary rows if needed. Best is of course referencing columns by name
jen729w 4y ago
Oh hard disagree, friend. I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in. I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.
- xupybd 4y ago
  Have you tried XLOOKUP? I like it more than index match.
  - 4y ago

lenkite 4y ago

If your data is <10k rows, Excel is great. After that lots of stuff stops working. Stick to R or Python data science libraries for real work.

MikusR 4y ago
Which stuff exactly?
- lenkite 4y ago
  filtering drop-down 10k list limit is the most obvious one. There is also the hard traditional 1,048,576 limit which many hobbyist Covid tracking folks ran into.
  - lostlogin 4y ago
    Hobbyists and the UK government. They somehow cooked up a spreadsheet that make them hit the limits much earlier. https://www.bbc.com/news/technology-54423988.amp https://www.bbc.com/news/technology-54423988.amp
    - Closi 4y ago
      This was a massively outdated version of the software - it’s not entirely surprising that if you use c10-year old software there are more limits. Excel has grown hugely since then.
      - lostlogin 4y ago
        That’s completely alarming and much worse.
      - lenkite 4y ago
        Excel limits haven't gone away: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 https://support.microsoft.com/en-us/office/excel-specificati...

Foobar8568 4y ago

These days I am working with data analysts who are using Excel, they were mind blown to discover power query...