Hacker News Reader

Comment by andylynch | Hacker News Reader

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.

View on HN

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.

> 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.

you can already do this with Google Sheets. I wonder if Office 365 has similar capabilities.

It does, via Microsoft Flow

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.

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

> 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.

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

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.

Have you tried XLOOKUP?

I like it more than index match.

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.

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

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.

Which stuff exactly?

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.

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

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.

That’s completely alarming and much worse.

Excel limits haven't gone away: https://support.microsoft.com/en-us/office/excel-specificati...

Of course there are still limits, the article was talking about the 2003 limit of 64k lines which has now been increased to 1m.

You can’t exceed these limits without being heavily alerted/warned by the application too.

Every spreadsheet application and database has these sort of limits anyway, eg:

* Google Sheets limits total cells, although can only handle a tiny fraction of what excel can.

* Postgres limits columns to 1600 (much less than excel)

* Mongo limits document size

i'm using a 35k row excel daily without any hiccups. Sorting, Vlookup, running formulas for all rows

Reply on news.ycombinator.com