Great youtube video from Joel Spolsky.
https://www.youtube.com/watch?v=0nbkaYsR94c
Something about knowing that a programmer, specifically one that cofounded stackoverflow made an excel video made me want to watch it. I love the attitude in the video where he basically rags on the audience the entire time. I also learned he worked on the Excel team at microsoft which was interesting.
This is just going to be some things I've learned.
The first thing is R1C1 mode vs A1 mode. Absolutely mindblowing, I always wondered how dragging a cell autofilled formulas and turning on R1C1 mode makes it very clear. The idea of there being absolute references and relative references is cool and obvious in retrospect.
The most useful thing is the paste as values button. By god that is going to fix so many things for me. I wonder why it isn't the default. Then there is paste special where you paste in data and transform it at the same time.
Excel also has the ability to name cells and ranges. This is super helpful to make self-documenting excel sheets which Joel seems to be a big fan off. He harps on it throughout.
Making things into a table is one that I knew about but never really used, I think I should start using it more. Pivot tables also falls into this bucket.
Using index and match is a neat way of doing logic in excel.
Excel also has if statements, didn't know that, I don't work with enough excel for all of it to matter but its a good thing to know I imagine.
Another useful thing is to highlight a row before entering data, this way the enter key will move you horizontally instead of the default vertical.
The best quote comes when he says that every 3 months a startup is pitching some excel functionality to him. That is probably true. I think what makes excel so magical and great is that it has such a wide set of features that everyone may use excel but they all use a different part of excel. This is why building out and trying to replace excel in a niche may be pointless. You can fulfill the niche but then as soon as they need more functionality or another part of excel, they return back to excel to get work done.
Overall a great video and I already learned some tricks!