You Suck at Excel (2015) [video]
youtube.com
youtube.com
I'm really grateful I never had to work with Excel in any job. My excel skills section of my CV reads "basic, would rather not use it". The closest I came was importing Excel files. I do however respect people who can use it well and I've seen people be really efficient in excel.
bloody hell, they seriously do that?
(for unaware users, those are exchanged between Excel in English and in Swedish. In english CTRL-F for find, CTRL-B for bold. )
For example, if you installed English Excel on system with Russian calendar/numbers/currency, the formula can look like: =COUNTIF(B2:B6;">1,5") (syntax error otherwise).
English Excel in English environment: =COUNTIF(B2:B6;">1.5")
Russian Excel in Russian environment: =СЧЁТЕСЛИ(B2:B6;">1,5")
What will happen when you enter 1.5 into English Excel? Everyone knows it is 01.май. What happens if you enter 100.5? It is a string, Excel on this machine expects different separator.
- Silent localization of decimal separator in Libre Office calc.
- Localization of error messages (much harder googling). I have known hundreds of french software engineers, absolutely no one cares for GCC errors in french.
- Google search trying to be way to smart. Good luck trying to figure out how to get English results for a word that has the same spelling in french.
- The entire keyboard layout software stack being a giant us-centric leaky abstraction at every layer.
- And the absolute worst of all: automatic translation of YouTube video titles with neither possibility to opt-out or consent, for both viewer and author.
Quite easy just not discoverable but if you remember query parameters from 20 years ago it's easy, just remove _all_ query parameters except `q` (the query itself) from the URL and append parameter `hl=en`, then click `show only results in English`.
Google was always very bad with often mixing translation and localisation.
Add, indeed, the localized formulas and you end up with sheets that are completely unportable, unsharable.
I'm certain that this is because Excel was designed in a pre-internet era. Where collaboration, if it existed at all, evolved around intranets, shared drives and company-managed computers.
If you do it for excel it even handles dates pretty well because they're in a numerical format and you can infer that a column is filled with dates because of the range.
The pre-internet conclusion is right. They try to keep backwards compatibility. Also, Excel doesn't handle big data well (1-2M rows) and neither do import libraries (at least in JS land).
> He Read The Whole Thing! [OMG SQUEEE!]
The style and tone reminded me of Douglas Coupland's book Microserfs.
https://youtube.com/playlist?list=PLtjme5if5dJWtSoUUhXMiVvpO...
Range names are nice until you need to duplicate a tab, then you end up with a combination of local and global range names, where it isn't clear which is which. And god forbid you want to move a tab to another workbook, particularly if that workbook already has range names.
He does an INDEX/MATCH without specifying the match type to exact, which forces him to sort the lookup table. Specifying the match type should be muscle memory. And after 20 years of ignoring their users, the excel team finally introduced an xlookup fonction that does that. They also introduced other functions that are useful (and long overdue): SORT, UNIQUE, TEXTJOIN (somehow TEXTSPLIT only came several years later, I guess it was a hard computer science problem)
There are still functions that would be nice to have in Excel. An interpolate function that could take an X value, an X range and Y range, and an interpolation method (linear, polynomial, cubic splines, etc) and interpolate the value.
[1]: https://spreadsheet.dev/how-to-make-a-table-in-google-sheets
But even that is fairly slow compared to any scripting done in Excel.
[1] https://learn.microsoft.com/en-us/office/dev/scripts/develop...
[2] https://learn.microsoft.com/en-us/office/dev/scripts/overvie...
[3] https://learn.microsoft.com/en-us/office/dev/scripts/develop...
And yet I assume, based on the slavish devotion to it by others, that it is actually great if you know how to use it effectively. Aside from this video, where can I learn the foundations as quickly as possible so that I can do cool things with it?
So with that here's one super easy tip and one foundation pathway for long-term learning.
The super-easy tip is to learn some basic shortcuts[1] so you can move quickly and get shit done without constantly reaching for the mouse. In particular, learn the "Ctrl - arrow" shortcut (move to the end of the contiguous data in the direction of the arrow), the "Ctrl + Home" shortcut (Move to the start/top left corner of the spreadsheet) and realise that when you're holding down shift this means you can select regions of data for cut and paste or other operations really quickly. Also learn:
1) If you're using a mac you'll need to turn off some of the exposé features or rebind them if you want decent excel keyboard shortcuts. Small price to pay imo but your opinion may differ of course.
2) If you're using anything other than an archeological version of excel you're going to have to come to terms with that stupid ribbon thing. Luckily from a keyboard shortcut pov the ribbon means you have one simple set of shortcuts to learn to access any icon on the ribbon from your keyboard so learn it. On PC just press "Alt" and your ribbon will light up with all the keyboard shortcuts for everything from the ribbon on Mac I can't remember how you do this thing and a quick scan doesn't reveal. My chops are a little rusty because I only ever really used excel seriously on windows.
OK now you won't be painfully hobbling about the app one row or column at a time and reaching for the mouse the whole time, the long-term learning. Understanding the power of excel comes down to realising you are editing a model of a graph computation, then learning and understanding a few key features which are really powerful, and will hint at other directions to explore to learn more. I'll give you some examples, but these really are the tip of an incredibly huge iceberg.
1) Autofiltering
Put yourself in a sheet where you have column headings at the top and one contiguous block of data in rows and columns beneath. Go ctrl-home to move to the top left of your data, then go shift-ctrl-end (or shift-ctrl-right and shift-ctrl-down) to go to the end of your data. All your rows and columns should now be selected. Now click on the "AutoFilter"[2] icon or if you've been paying attention to 1, use "Alt" to choose the icon of a hopper thing on your ribbon. It says "filter" next to it and the link below has a picture. This allows you to sort and filter your data in very flexible ways with a UI that's very intuitive for non-technical users. I often point UX folks at this feature when they (inevitably) come up with a pale shadow of this capability.
2) Pivot tables
With your data still selected, go to "Insert" on your ribbon and go "Pivot table", select "new worksheet". OK here you have a thing that basically does a select ... group by on your data with various aggregations, sorting, filtering and a bunch of related functionality all in a pretty simple wrapper. Play around and get familiar with this. You can do amazing things really quickly with pivot tables. Yes I know you can do all this and more in pandas but your mba colleague can do this with excel in seconds and they can't write a line of code. You may be getting a sense of why people consider excel powerful.
3) Vlookup, hlookup, sumif, countif and friends
OK that was the entry-level drug, now go find out about vlookup, and sumif. These are simple functions that look data up in a table. Typically vlookup takes a sorted table on some reference sheet, looks up some key in the leftmost column and gives you back the value of some cell in that row. Realise this adds higher-order dependencies to the graph of your computation. People use this to do amazing shit with vlookup.
Sumif is a simpler lookup. It takes a table and a predicate and sums up values matching that predicate. It is often used to look up single values where the table isn't sorted but you know you only have one of each key.
4) Index, Indirect and Address
We've gone too far to stop now. If you're writing a sheet that uses these you already know you are a bad person and don't care. these are the `eval()` of excel, allowing you to construct arbitrary references to cells or arbitrary functions as strings, dereference and evaluate them. You can then compose these into other functions. More details of this depravity can be found here[3]. It always makes my day if I am making a sheet that requires any of these functions.
[1] https://support.microsoft.com/en-gb/office/keyboard-shortcut...
[2] https://support.microsoft.com/en-us/office/use-autofilter-to...
[3] https://support.microsoft.com/en-us/office/lookup-and-refere...
I had been a big vlookup fan until I upgraded excel versions and fell in love with xlookup
Only change I’d make would be to move Index to your third category alongside Vlookup, and add the Match function there as well, making sure to advise using exact match (match type 0, which isn’t the default).
I ditched Vlookup for Index-Matching and was able to scale notebooks quite a bit further. I don’t know why this was, however. Pushing all the heavy data processing to the SQL database, by pre-computing every possible metric to be shown to users, made my spreadsheets super lightweight and responsive. Essentially only doing index-match lookups against a single (but big) data tab. The finance team loved it.
XLOOKUP is a better VLOOKUP. It was added in the last few years. Other really good new ones:
- LET for defining temporary variables inside a formula.
- LAMBDA for defining new functions
- IFS is like if-elif-else with a flat structure, solves the deeply nested IF problem
- SWITCH does what you think
- TEXTJOIN join a list of strings on a delimiter
Then there's the whole spill-arrays feature that completely changes the game. Much better than the old dynamic array formulae. You can finally treat ranges kind of as if they're dynamic-length arrays in a conventional programming language. There's MAP, FILTER, REDUCE, UNIQUE, SORTBY, HSTACK/VSTACK, etc.
There's a full list of every function here: https://support.microsoft.com/en-us/office/excel-functions-a... scroll down it and look for the ones marked with new Excel versions to see what else is new.
>4) Index, Indirect and Address
Other than INDEX, please don't, for the very reasons you say. They're like eval. When someone hands me a spreadsheet that heavily uses INDIRECT I have to spend a long time figuring out what's happening. They're also volatile, meaning they're recalculated any time you do anything, rather than when they're needed, because Excel can't statically determine their cell dependencies.
Other important features: tables (i.e. the structured-reference tables, not pivot tables), Powerquery and its associated M language, VBA if you have to deal with a lot of legacy documents.
These are actually standard shortcuts that work in all Office programs and many others, including browsers. It's amazing how many people still use the mouse to select, and spend an inordinate amount of time doing so.
Never used it in this way, but why not.
To me, conditional sums are a replacement for filters and pivot tables, because they let you have all the information in one go, without constantly clicking to select criteria.
Just select unique values for criteria and do conditional sums for each of them, and you have a visual that shows you all possible combinations and results at the same time.
It's also worth going through the function list in the documentation once, just so you know what is possible.
5) learn how to use array formula. They can be used for two use cases, either a function returns an array, though this is now deprecated with the SPILL feature in more recent version of excel. But more useful: you can do array based calculations (more powerful than SUMIF). For instance give me the number of time the value changed in a timeseries: {=SUM(1*(A1:A100<>A2:A101))}.
6) of the rare useful new features of excel, there is PowerQuery, which allows you to load data from a csv file or database. Very useful when you need to refresh that data. You can parametrize power query so that for instance the filepath of the csv file is defined in an excel range. It avoids this repeated pattern of manually opening a csv file and copy pasting the data when you need to refresh the report.
You Suck at Excel (2015) [video] - https://news.ycombinator.com/item?id=21847372 - Dec 2019 (77 comments)
You Suck at Excel with Joel Spolsky (2015) [video] - https://news.ycombinator.com/item?id=12448545 - Sept 2016 (420 comments)
Still can't believe that Chinese localisation BS.
i mean i know he disrespected the wu-tang clan, and that's just not cool, but i also want to learn his spreadsheet techniques
When you're a new hire, they basically take away your mouse and force you to learn all the keyboard shortcuts.
https://www.spin.com/2017/08/martin-shkreli-jury-selection-t...
it shouldn't need to be said, but i don't approve of him as a person, i just want to learn his spreadsheet skills
https://www.youtube.com/playlist?list=PLJsVF3gZDcuTxcdH5FmQR...
What would you recommend? Python?
You will probably want to use one of the “notebook” environments that make your Python easier to use interactively and display graphs and the like. Jupiter is the one I know but there are others too.
I would also be that guy and recommend SQL.
The resulting pipelines were always very simple, but the process of working through complex spreadsheets was always a special layer of Dante's hell that he didn't even dare writing about.
These weren't created because people sucked at excel (those people made very simple spreadsheets with a few calculated columns in) - they where made by people who where very skilled but didn't spot that they should stop.
All those bad spreadsheets cost hundreds of person hours to maintain, and ended up being finacially and emotionally expensive to untangle.
Analysts of the world: please ignore this video and continue sucking at excel.
Then you just add two more rows and one column. And 6 more rows and two more columns. And just two more formulas. And then you need just one minor thing, that can be done with a macro in five minutes.
Yes, companies such as Apple need specialized software to handle stuff, and there are solutions to handle this (eg. SAP, no matter how pain in the ass SAP is). But expecting Apple to use SAP when it was just the two Steves in a garage is stupid... and upgrading to SAP (or whatever other solution) is a thing you do, once the excell spreadsheet gets to an unmaintainable mess and not a second earlier.
I prefer combining SQL with Excel by generating a query that gets most of what I want inside the database and then importing it into excel's powerquery to further explore the individual vectors.
For so many one-off data importing jobs I just end up generating SQL in Excel after using the text import wizard to get the data into Excel.
It's kinda stupid but it just works so well most of the time. If I sense I might do it again I tend to script it in something more proper tho.
Then you can just update from excel and everything is in the right format for non programming people to use.
The thing about excel is that for large orgs its probably on every computer and almost everyone sort of knows how to use the basics of it. that gives it value.
The real value would be if there was some way I could get excel on a random machine without any privileges to refresh the data itself on a schedule.
“I’d rather use SQL though”
There’s something profoundly concise here in these sentences.
I don’t need c++ or Python to build a net present value analysis that I could show the CFO. I can build it in excel in an afternoon, and I can hand it to the least technical salesperson, and he can make changes in my model and see the results instantly then send it back to me. And then my boss can. And then someone in marketing can.
And the bonus is I don’t get a bunch of useless errors that no one understands like “Stack Overflow Error” or “Cannot process because of error 1994505 super duper data transformation canooter value fluctuation.” The worst error I get is that it crashes and I lose 10 mins of work.
Face it - Excel is a phenomenal achievement and it burns arrogant programmers like you because we can do all this and completely shut you out. That’s why it’s a “horrible tool” lol.
Already Lotus 1-2-3 for MS-DOS, more than 30 years ago, matched or exceeded almost all features provided today by MS Excel (and it had an optimized keyboard-based user interface that enabled experienced users to perform most tasks faster than in modern spreadsheet programs like Excel or LibreOffice Calc).
Excel has just provided the same features, with nothing original of any importance, but only with trivial improvements enabled later by better computers.
The fact that Excel has succeeded to replace Lotus 1-2-3 has not been caused by any technical superiority but by the integration in MS Office and by the fact that Microsoft has cheated, by ensuring that nobody else could keep up with the changes in the public Windows API and with the undocumented parts of the API.
Only someone who isn’t a heavy user of Excel could say this.. the improvements really aren’t trivial.
It is obvious that a program which may have a size of many megabytes is able to include a lot of improvements over a program whose size was limited to a few hundred kilobytes.
Nevertheless, all improvements provided by Excel are quantitative not qualitative, they do not enable any essentially new application.
For instance, there is no doubt that it is much easier to write a maintainable Excel script in Basic, than in the awkward macro language of Lotus 1-2-3.
Even so, using just the macro language of Lotus 1-2-3, it was possible to write amazingly complex applications, e.g. for the automation of the tracking in real time and of the generation of reports about the flow of partially processed products through the steps of complex technological processes in some factories (with thousands of different products and with more than one hundred manufacturing process steps through which they might have to pass, depending on the part number), also of the printing of all the documents that accompanied the manufacturing batches, and where the data was stored in a database embedded in the spreadsheets.
Today, writing such a program by using a modern database, a modern programming language and a modern computer would be very easy, but doing the same within 640 kB and with a 33 MHz CPU was not a little accomplishment. At that time, Lotus 1-2-3 was one of the most useful applications for most businesses, especially when computers and programs were much less affordable and many would not have been able to buy any other program, except perhaps some word processor.
Excel has taken all the features of Lotus 1-2-3, except the user interface, which was no longer suitable in Windows, but everything added later were just enhancements that were obvious when faster CPUs and more memory became available.
I'd be willing to bet you also probably look down on people who use Windows instead of Linux desktop. Or why they don't "burden" themselves to learn the oh so easy command line. If this is you, I get exactly the person you are and it defends my point even more.
And I didn’t mean that Excel is bad or anything, it’s extremely good at what it’s for. And like u mention, it’s much easier for someone to pick up excel and “get stuff done” than say python. My point was simply that there’s merits to one suggesting an alternative to excel related tasks. It’s not unequivocally a better choice for everyone, but it certainly can be.
And lol windows is more so just the privacy thing and msft fucking their users around. I’ve heard great things about powershell. But ironically, the command line rlly isn’t difficult to grok, and certainly not hard relative to working with ridiculously nested XLOOKUP’s
The only thing that comes even close for my current Excel spreadsheets use cases would be Rshiny. Interactive responsive analysis of various scenarios, with traceable computation for a very wide audience, is far better via Excel than even Rshiny, which hides all the code.
Starting with Excel is probably a great way to get to other tools. Especially when computations are straightforward and don't require the advanced stats libraries available in something like R.
It's not just analysis; data representation, manipulation and collaboration are essential parts as well (especially the last one). Being able to quickly import numbers, format and surface the important part and show it to someone to discuss and tweak in real time makes a world of difference.
I’d bet money you are also the person who wonders why the world uses Windows over Linux for many tasks. When you can answer why regular people choose Excel over code and why regular people choose Windows over Linux, you’ll get it, and you can make a positive change in code to bring Linux and Code more mainstream.
Generally tho idk if it’s a good idea to optimize for usability from sales dude
in matplotlib? get outta here