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.
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.
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.
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.
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.
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
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.
Well I work in an industry which still uses an archaic version of Excel. 2016 for Windows I guess? No XLOOKUP for me, yet. But yeah I’ll switch when it’s available.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
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.
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.