7 ms·

I've gotten a long way in life never using Excel except to type things in cells for my idiot former company which used it as a word processor, which made me hat

I've gotten a long way in life never using Excel except to type things in cells for my idiot former company which used it as a word processor, which made me hate it with all my heart. 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?

grigri907 3y ago
Not sure about learning from scratch, but ExcelJet.net is consistently the cleanest, clearest resource I've found to date.
seanhunter 3y ago
I used to work in the securities division of Goldman. Traders are generally amazing at excel and one way to completely lose their respect is to suck at excel. I've more than once seen a trader look on in horror as a programmer fumbles around in some excel model the trader has asked for help with. At one point I had to turn some genuinely insane excel models (think 10mins to load the sheet, 10-15mins to recalc) into code (my version of that model ticked >1000x per second and had more functionality than the original), so I learned to get good at excel and learned the kinds of things people who are amazing with it are able to do and how powerful it is. 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-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f https://support.microsoft.com/en-gb/office/keyboard-shortcut... [2] https://support.microsoft.com/en-us/office/use-autofilter-to-filter-your-data-7d87d63e-ebd0-424b-8106-e2ab61133d92 https://support.microsoft.com/en-us/office/use-autofilter-to... [3] https://support.microsoft.com/en-us/office/lookup-and-reference-functions-reference-8aa21a3a-b56a-4055-8257-3ec89df2b23e https://support.microsoft.com/en-us/office/lookup-and-refere...
- smashy9 3y ago
  very nice I had been a big vlookup fan until I upgraded excel versions and fell in love with xlookup
  - seanhunter 3y ago
    Absolutely. And of course when you start playing with xlookup you learn that "you cannot change part of an array". If you know, you know.
  - andelink 3y ago
    Is xlookup new? I’m not familiar with it. I used excel quite a bit back in 2015/2016
    - _dain_ 3y ago
      Yeah it's new. Excel is a whole different animal now than in 2016. LET, LAMBDA, and spill arrays completely change how you do things.
- andelink 3y ago
  I sadly had to go deep in excel for a year or so early in my career. This is a good overview. 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.
  - cm2187 3y ago
    [deprecated]! ignore the advice above and use XLOOKUP.
- 3y ago

grigri907 3y ago

Not sure about learning from scratch, but ExcelJet.net is consistently the cleanest, clearest resource I've found to date.

seanhunter 3y ago

I used to work in the securities division of Goldman. Traders are generally amazing at excel and one way to completely lose their respect is to suck at excel. I've more than once seen a trader look on in horror as a programmer fumbles around in some excel model the trader has asked for help with. At one point I had to turn some genuinely insane excel models (think 10mins to load the sheet, 10-15mins to recalc) into code (my version of that model ticked >1000x per second and had more functionality than the original), so I learned to get good at excel and learned the kinds of things people who are amazing with it are able to do and how powerful it is. 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-shortcuts-in-excel-1798d9d5-842a-42b8-9c99-9b7213f0040f https://support.microsoft.com/en-gb/office/keyboard-shortcut... [2] https://support.microsoft.com/en-us/office/use-autofilter-to-filter-your-data-7d87d63e-ebd0-424b-8106-e2ab61133d92 https://support.microsoft.com/en-us/office/use-autofilter-to... [3] https://support.microsoft.com/en-us/office/lookup-and-reference-functions-reference-8aa21a3a-b56a-4055-8257-3ec89df2b23e https://support.microsoft.com/en-us/office/lookup-and-refere...

smashy9 3y ago
very nice I had been a big vlookup fan until I upgraded excel versions and fell in love with xlookup
- seanhunter 3y ago
  Absolutely. And of course when you start playing with xlookup you learn that "you cannot change part of an array". If you know, you know.
- andelink 3y ago
  Is xlookup new? I’m not familiar with it. I used excel quite a bit back in 2015/2016
  - _dain_ 3y ago
    Yeah it's new. Excel is a whole different animal now than in 2016. LET, LAMBDA, and spill arrays completely change how you do things.
andelink 3y ago
I sadly had to go deep in excel for a year or so early in my career. This is a good overview. 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.
- cm2187 3y ago
  [deprecated]! ignore the advice above and use XLOOKUP.
3y ago