I'll never forget a past job where they used a lot of Excel in ways I did not know was possible.
First of all they had an invoicing system in Excel, that pulled in data using VBS, into Excel templates, and at the press of a button in the UI generated invoices from these templates.
And the craziest part was their server inventory system made in Excel, where they had drawn all the rack cabinets, you could click on each, to drill down and show all the servers in that rack. Also a ton of VBS, you could even get monitoring status of each rack.
Excel has been OP for a long time, long before its Python capabilities.
My best Excel trick, which reveals how little I know, and yet Early [0] doesn't use it (or maybe doesn't need it, but that's hard to believe):
1. You can drag down the bottom of the formula bar/field and make it multi-line
2. You can insert arbitrary[*] newlines in an Excel formula
Combining those, you can turn the absurd default format of single-line-of-code functions into something readable and manageable. Here's a simple one from a spreadsheet I have open:
And just think of highly nested functions. Once you know it, writing single-line functions of any complexity is absurd, as absurd as writing 'real' code that way.
> You can drag down the bottom of the formula bar/field and make it multi-line
For folks on LibreOffice (currently v24.2):
* There's an downward-pointing "expand" triangle to the far-right of the formula input line.
* That button toggles the formula input area between 1-line vs 6-lines with scrolling.
* Newlines can inserted by shift-enter.
* If there are additional formula lines lines outside the viewable line(s), then a dashed line on the relevant border will be shown. (Plus the regular scrollbar, in expanded mode.)
It's interesting that the challenges are not business or accounting centred, as is the expectation when using Excel. If this is now general problem solving, are we watching language-specific competitive programming through the lens of a more broadly accessible platform like MS Excel?
It used to be financial modeling but they realized they’d get more attention with the esports audience this way.
It’s gone quite far now - one of the many challenges was a mock terrain map where you’d calculate distances to hike while considering the weight of your pack. Even the way they walk through the tunnel is done for show.
Excel is a general purpose computing environment and has been for quite some time.
When I was in the air force we had a complete aircraft maintenance planning and performance management system entirely in Excel. It can connect to remote workbooks on a shared drive/SharePoint too, so the higher headquarters would tie into our dashboard for their own operational readiness tracking.
It was a total shit show of undocumented pseudo APIs with zero change management or version control but it worked somehow.
Glad to see not only our financial infrastructure relies on wealth management agents’ skills at writing formulas, but our army also relies on our general commanders’ skills in Excel.
Funnily Excel is the tool of adults born in 1980; The next generation will only know Canva, so I guess we’ll have great infographics about battle fronts.
I could do half-screen nested array formulas when Excel was before the ribbon (and screen resolutions were smaller), out of necessity and because I could. It was in quite demanding uni home calculations and then mostly when working as intern in IB. But then having a life is also important...
The only thing I still enjoy is that any data smaller than 1M rows is sliced and diced almost without thinking. I am sometimes really grateful that MS did not break the shortcuts, while almost breaking the product overall. The muscle memory works perfectly.
I watched the walkthrough video of the solutions and it's genuinely impressive. These aren't just "use VLOOKUP fast" challenges - they're algorithmic puzzles where Excel is the constraint.
What struck me is how similar it is to code golf or competitive programming, just with a different medium. The winner uses array formulas, INDEX/MATCH combinations, and nested functions in ways that most Excel power users would never think of.
The real insight though: Excel is probably the most widely-deployed functional programming environment in the world. Most "business users" are doing functional composition daily without realizing it.
Makes me wonder if we should be teaching programming concepts through Excel first, then moving to traditional languages. The immediate visual feedback is unmatched.
I think you should. But my own experience when learning programming was there were few ways of learning programming that seemed properly tested or pipelined to actually teach programming. You had to hodgepodge your own materials if you were like me and doing self learning, from half a dozen books and online courses and workshops. I felt like programming needs a Montessori - someone who deeply understands human learning and makes it easy for multiple personalities to learn at their own pace. IMO.
Can anyone find the actual challenge files? Not that I would be competitive at all, but the description of last year's World of Warcraft themed one is interesting, and I want to walk through it.
What a garbage way to gate visibility to the sport. You are already looking at a niche audience who would be interested in the idea, and you hope to collect some emails for marketing as well?
Readit News
I'm pretty good with Excel, my main tool at the job for over 20 years. I understand how he did it, but it's just really humbling...
I still think quality of what you do with Excel (idea) is more important than how you do it (skill).
First of all they had an invoicing system in Excel, that pulled in data using VBS, into Excel templates, and at the press of a button in the UI generated invoices from these templates.
And the craziest part was their server inventory system made in Excel, where they had drawn all the rack cabinets, you could click on each, to drill down and show all the servers in that rack. Also a ton of VBS, you could even get monitoring status of each rack.
Excel has been OP for a long time, long before its Python capabilities.
1. You can drag down the bottom of the formula bar/field and make it multi-line
2. You can insert arbitrary[*] newlines in an Excel formula
Combining those, you can turn the absurd default format of single-line-of-code functions into something readable and manageable. Here's a simple one from a spreadsheet I have open:
And just think of highly nested functions. Once you know it, writing single-line functions of any complexity is absurd, as absurd as writing 'real' code that way.[0] Early shows how it was done: https://news.ycombinator.com/item?id=46340638
[*] I think you can do it anywhere but I haven't tested anything crazy; mostly I just use them between expressions.
For folks on LibreOffice (currently v24.2):
* There's an downward-pointing "expand" triangle to the far-right of the formula input line.
* That button toggles the formula input area between 1-line vs 6-lines with scrolling.
* Newlines can inserted by shift-enter.
* If there are additional formula lines lines outside the viewable line(s), then a dashed line on the relevant border will be shown. (Plus the regular scrollbar, in expanded mode.)
Perfect style guide format does consume time, but pressing Alt+Enter a few times would seem to reduce errors at essentially no cost.
The end result - as you see - is quite readable.
=LET(
k))groupAvg, )I enjoy the idea, and love watching it grow.
It’s gone quite far now - one of the many challenges was a mock terrain map where you’d calculate distances to hike while considering the weight of your pack. Even the way they walk through the tunnel is done for show.
Huh, interesting. I thought I’d been working remote so long that offices had done this to make people more excited for work.
When I was in the air force we had a complete aircraft maintenance planning and performance management system entirely in Excel. It can connect to remote workbooks on a shared drive/SharePoint too, so the higher headquarters would tie into our dashboard for their own operational readiness tracking.
It was a total shit show of undocumented pseudo APIs with zero change management or version control but it worked somehow.
Funnily Excel is the tool of adults born in 1980; The next generation will only know Canva, so I guess we’ll have great infographics about battle fronts.
The only thing I still enjoy is that any data smaller than 1M rows is sliced and diced almost without thinking. I am sometimes really grateful that MS did not break the shortcuts, while almost breaking the product overall. The muscle memory works perfectly.
What struck me is how similar it is to code golf or competitive programming, just with a different medium. The winner uses array formulas, INDEX/MATCH combinations, and nested functions in ways that most Excel power users would never think of.
The real insight though: Excel is probably the most widely-deployed functional programming environment in the world. Most "business users" are doing functional composition daily without realizing it.
Makes me wonder if we should be teaching programming concepts through Excel first, then moving to traditional languages. The immediate visual feedback is unmatched.
You're supposed to do a $0 checkout for some reason and then download them
https://www.youtube.com/watch?v=xubbVvKbUfY
https://youtu.be/ICp2-EUKQAI
Deleted Comment