One of my biggest weakness as a developer is that I can barely use excel. It's really embarrassing, especially since I've moved to a financial firm.
Do you have any recommendations for becoming semi competent with it?
- understanding that formulas can return arrays, not just single elements (easier in more recent versions of Excel which have made this more consistent for every formula), so you can e.g. AVERAGE(IF(A1:A100>100,A1:A100,FALSE)) get the average of the values between A1:A100 which are greater than 100. the FALSE parameter can be omitted there but I left it in for clarity. Interestingly this means AVERAGEIF() is just syntactic sugar, so I prefer to avoid it. it makes it easier to, say, change to MEDIAN(IF(...)) later, since MEDIANIF() doesn't exist
- if you combine the first two bullets above, you'll enter the fifth dimension
- don't ever hardcode a value if you can refer to it somewhere else. want to use INDEX(MATCH()) and AVERAGE to, say, take some average value over some time period? put the start and end dates into their own separate cells with no formulas, and then refer to those cells in your formula. if you later need to change the time period, you won't have to modify all your formulas, just those values
- LET() is strange at first but super powerful. most people still don't use it
- Separate data from presentation. This point can't be stressed enough. I care about it so much I'm literally building an Excel competitor to enforce this. If possible, separate raw data, data transformation and data presentation.
- most people know you can name cell ranges and refer to them in your formulas. most people don't know you can also name formulas and refer to them elsewhere. your "average value over time" calc doesn't even need to be in a cell anywhere, it can just exist as a defined value in a named range. now named ranges are hard to see (only visible if you open the name manager), harder to debug (you basically get just a #VALUE error most of the time, forcing you to copy-and-paste the named range into a cell to debug it) and they get copied to other workbooks when you copy-and-paste across workbooks, which makes them super messy.... but for short formulas they can be pretty nifty
- LAMBDA() is even more recent than LET() and basically makes named ranges more useful. even fewer people use it
That's how I do it and it works great. I've gained a new appreciation for Excel.
After those 10, they break them down by category, and have one for Financial Functions, which is going to be useful for you. Similarly, the Logical, Math & Trig, and Stats functions, will all be useful for looking at a Finance firm's spreadsheets.
[1] https://support.microsoft.com/en-gb/office/excel-functions-b...
Link from Reddit post: https://www.reddit.com/r/excel/comments/14zj5vz/you_suck_at_...
* Avoiding hardcoding numbers, making input cells a certain color, etc.
* Knowing the all powerful F4 key that alternates between A1, $A$1, $A1, and A$1 (and knowing what each of these mean)
* Inserting blank lines above and below a summed range and including those rows inside of the sum formula (prevents formula from breaking if you move rows around)
Other than that you can do a lot of productive things with a combination of index/match and dynamic named ranges via offset formula.
I find I learn best from (good) books because they actually explain things coherently, and you can leaf through them to discover features and things that look interesting.