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