"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.
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.
That sounds like a potentially great approach for consultancy business/product discovery.
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.
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
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.
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.
I like it more than index match.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
They somehow cooked up a spreadsheet that make them hit the limits much earlier.
Excel has grown hugely since then.
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
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.
There's other, more useful data types, like cities and ZIP codes and stocks, I just listed the yoga one because it's the funniest.
> 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.
https://support.microsoft.com/en-us/office/what-linked-data-...
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.
Instead of writing the formula =A1\*B1, you can do =Width\*Height
Instead of writing the formula =A1*B1, you can do =Width*Height
MS Excel 2.0 (1987) could do the same, but entering names for ranges was not as easy as in Multiplan. Look in the menu for Formula | Names…
[1] https://usermanual.wiki/Manual/MicrosoftMultiplanmanual.3880...
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).
>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.
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.
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).
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.
Forgive the spruik but while we’re here: https://www.learnwithlucy.rocks/courses/excel?coupon=earlych...
It’s probably not for anyone reading this thread but it might be for your partner or kids. And it definitely works, because Lucy can use Excel now.
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.