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.
Looks like the yoga button is a thing of the past :(
> After June 11, 2023, data types by Wolfram will no longer be supported and can't be refreshed. However, Bing, Power Query, and Organization data types will still be supported.
It's the learn TLA+ person :) Awesome to hear he is exploring Excel. Is that the manual version of crunching models for formal consistency?
Management science is all about modeling stuff in Excel and using solvers. Imagine if somehow we could meld the robustness of TLA+ with the immediacy of an in-built macro-lang, ubiquitously installed .exe that is the spreadsheet.
Future sprint planning days: everyone prototypes in the spreadsheet! No one estimates until the rules and formulae make sense.
XKCD 1053 is relevant here. You and I knew it, but there are going to be lots of people who learn about it for the first time today, and that's a net positive.
HN still accepts new accounts. It's not an exclusive club for old people.
Even beyond that, stories have been repeating here since the beginning. Not everyone has the same life experiences, and not everyone checks HN at the same time.
I didn’t know about named ranges, and think it’s pretty cool. Of course, when I thought of places where I could apply them I realized that by the time I use enough Excel to use named ranges, I should probably switch to a real programming language.
Just an HN formatting note... To have * characters taken literally instead of italicizing the text in between, prefix each one with a backslash, like this:
Instead of writing the formula =A1\*B1, you can do =Width\*Height
which formats as:
Instead of writing the formula =A1*B1, you can do =Width*Height
Naming ranges in Excel is pretty darn easy, at least if you're doing full columns or rows - you select the range you want named (including by clicking the row/column header), then click into the field just to the left of the formula button that shows the selected cell. Just edit the name right there, it'll apply that name to the selected range.
Related, if that area shows a little dropdown arrow at the right side by clicking the dropdown you get a list of the named ranges on the current sheet and can choose a name to select that range of cells.
Edit: Editing ranges is a little trickier, for that go to the Formulas tab and look for Name Manager (it's the main icon in one of the tab bar sections).
Excel has also finally added sum() and other functions that allow the spreadsheet to do the hard work instead of making you use a calculator to add all the numbers up.
>A elderly guy - maybe in his 60s - was writing his book of poems on his computer and brought in a floppy disk because he wanted some advice on printing. We managed to find a plug in floppy drive but there was only an Excel file on the disk. I opened the file and he had written his poetry book in Excel cells, with widened columns and rows, complete with spaces to center text and indent paragraphs etc. When one cell got full of text he moved to the next. New poems were started a couple of columns over. I remember he also asked how to change the size of the font for the initial letter of each verse. He must have been using Excel 2003 or something because when he saw the ribbon, which was new to Excel 2007 he said it might not work properly because he used Excel. I tried explaining he should use MS Word. He said "oh I got a disk with that on." He pulled out another floppy and there was a file called houseke~.doc. I feared the worst. He had a Word table over several pages where he kept his home accounts, all beautifully typed in by hand, decimal points all lined up (hell I can't even do that now), not a calculation in sight - they were all done by a calculator and hand-entered.
I long for the days of all-in-one word processor/spreadsheet/database apps.
Eons ago I owned the long-forgotten Cambridge (formerly Sinclair) Z88, their 1987 entry into the laptop market. Its main software was called Pipedream, and as I remember did everything in what was essentially a spreadsheet, with all three application types available in the same file. The software was available for DOS as well.
decimal points all lined up (hell I can't even do that now)
In Word there are different types of tab stops, notably Left, Center, Right and Decimal.
If you turn on the Ruler (View tab, Ruler checkbox) you'll see a little bold "L" at the top left where the side and top rulers meet - that's not an L, that's an indicator for the kind of tab stop that will be created when you click on the top ruler. You can click on that tab stop type indicator to switch between tab stop and margin types - or if you double-click on the top ruler to set a tab stop it should show you the tab stop dialog that also allows choosing a tab stop type for each defined stop (along with things like setting a tab stop leader for things like a dotted line . . . . . . . . . . . . . . across to a page number in a table of contents).
Meh, this is clearly fabricated. I was following along with someone using Excel as a word processor up until they were also using Word as a spreadsheet.
My partner and I are in the process of recording an Excel course because I walked past her desk and saw her doing her accounts in Excel … with a Casio calculator. She was reading a value, doing it on the calculator, and typing it back in.
She’s smart. She’s 43. She was a medical copywriter. But Excel: no clue whatsoever.
We just recorded a lesson where I showed her VLOOKUP and she almost cried with joy. “Oh my poor accountant…”, she said. Fun moment.
Lol, literally came across this situation circa ~2012. I was an intern at a big but old school corporation. Someone asked me to help someone on a PM team with Excel, come to find out she was running some accounting numbers and was literally doing this. Reading numbers, putting them into a handheld calculator, and typing them back out. I had the pleasure of being the first person to ever show her sum(). Talk about blowing someone's mind.
Anecdotally I believe this sort of thing is terrifyingly common - in fact I wonder about the person-millennia of effort wasted globally as office workers across the globe huff and puff using Excel as a glorified typewriter.
He’s conflating it with LET and LAMBDA, which are new.
I worked for a firm that did extremely expensive training course for Corporate Finance back in the 90s. We taught people how to use named ranges. There’s probably still good money in that business.
In 2022, there's probably a new generation working at those clients who have never seen the advanced features in Excel. You should put some feelers out to see if there's any demand.
The structure of that initial part isn't great given the headline, but I don't think its intending to claim that that's really new, just fairly unknown. And then talks about about the new stuff.
"Instead of writing the formula =A1B1, you can do =WidthHeight like you should have been able to 30 years ago."
Not sure what this dude is talking about. Range names have been in Excel for decades.