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?
Just an idea but how would you advise someone learning programming/a new programming language? Probably you'd say: build something with it. So same goes for excel. Try to hook up a spreadsheet to some database, have it update automatically, have drop down lists that populate automatically from the database (e.g. have a "country" drop down and automatically populate a "region" drop down based on the choice of country) and so on. You need some BASIC and SQL for this but not much
Oh wow. Different poster same problem. Your comment made me realize that it's the "but I don't wanna!" attitude that I already know how to push through when it comes to language learning, just disguised differently. I'm still not sure that I wanna, tho...
- INDEX/MATCH. For all given purposes, the last parameter in MATCH() is always 0 unless you want to find the nearest match
- 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
The MS documentation for Excel is a pretty good resource to learn from these days, with text descriptions for just about every function, and videos for most too. This page [1] is an especially great resource. It's got a "top-10" list, which is a good place to start, as it covers the majority of things you'll see in a normal business document.
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.
For excel/spreadsheets etc chat gpt or google gemeni are a lot more useful than for normal programming as a lot of stuff about spread sheets is explained in easy steps for non technical people to implement so that has become a treasure trove of information for llm models.
For a best practices introduction I would recommend a course geared toward investment bankers. Some things that should be covered are:
* 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.
All solid advice. I learned many of these by: (1) reverse engineering other people's great sheets (formulas and VBA), and (2) watching highly skilled Excel users.
All the spreadsheets have map/reduce stuff! It's the programming you are used to but you type into a box and reference variables by sheet location instead of by name.
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.
Try stepping into a management role for a while, ideally one in which you have lots of dealings with less technical parts of a business. Even if it's not for you, at least you'll have gained some insights into that side of things, and more importantly - lots of real world exposure to spreadsheets!
