I’m on the curriculum committee at my school (yeah, I probably ticked off the wrong person or was in the head when they assigned committees). I’m trying to make sure our treatment of Excel lines up with what’s actually used in practice. I have my priors, but I assume I often get stuff wrong. So I’m polling various groups of industry folk - alumni who work in corporate or investment fields, etc… I do this every couple of years, and I almost always learn something.
I figured I’d add y’all into the mix - tell me what YOU think are the 3-5 things a new (or soon to be new) undergrad should know cold in excel if they want to not suck.
edited: also, what’s your role? I’ve found that sometimes what people think is important is colored by what their position is.
Depends on the sector (much like it depends on the job). My sector has lots of data, so we build lots of automated dashboards to screen and rank. Knowing index(match(match, sumifs, countifs, averageifs can automate the majority of the financial analysis people need to know. Also knowing basics like no hard coding make a big difference.
But starting out, I teach new people how to solve problems in excel through the use of unique keys. With just a sumif and uniquely generated keys, someone can make their own sumifs. Once they have this knowledge, you can build very robust dashboards in Excel doing calculations they don’t have pre-built.
One thing that I think is universally important but rarely seems to be taught is formatting. Using colors to identify formulas, inputs, etc. This is universal and unless people go through an investment banking training, they never seem to pick it up.
I work with derivatives. In my opinion, most of the comments here focus on the wrong things. Excel is really a mild form of programming. What distinguishes a good user from a mediocre one is not knowledge of specific formulas or functions, but the logical and efficient flow of the final product. A good complex spreadsheet must be transparent, computationally efficient, and should be immediately readable by another proficient user. Broad knowledge of formulas certainly helps achieve this goal, but it is not the goal itself.
People also tend to mistake complexity for good design. For instance, many quantitatively proficient users who are new to Excel tend to write custom VBA functions to accomplish functions that could be done with Excel built in tools. This approach tends to make the spreadsheet opaque (since another user must read the code to know what it does), and inefficient (Excel’s standard formulas are more optimized and stable than VBA). So, in this case, what might be mistaken by non-quantitatively inclined people for technical wizardry is actually just bad form.
When I studied at what is probably the world’s best university computer science program, assignments were graded on an equally weighted basis using two criteria: 1) Functionality, and 2) Style. Functionality measures whether the program accomplishes its stated goals. Style measures what I have attempted to describe in the two preceding paragraphs. Style is important because programming tends to be collaborative and portable. Good Style also makes it easier for the developer to spot errors, and to make extensions in the future. Functionality is an objective measurement based on the assignment’s objectives. However, Style is subjective and its measurements is based on the discretion of the (hopefully very skilled) grader.
Good Style is not something that can fully be measured using a syllabus or check box. You just know it when you see it - like pron.
Vlookup, pivot tables, simple macros, hot keys (specifically alt+e+s+v, ctrl+d, ctrl+r, ctrl +pg up/dwn, alt+=, and a few others), various kinds of charts, sumif formula, if formula… take away their mouse and they’ll learn the hot keys very quickly.
I agree with Ohai. The rule I tell my coworkers is create the spreadsheet in a way that if you leave and someone else looks at it a year from now, they can quickly see how it works and what needs to be updated. For me, I normally color code sheets by type (data, calculations, output, etc) and have a sheet devoted to instructions and key elements.
Color coding and conditional formatting are extremely useful. All these BSD kids think they are The Sh*t after learning how to write a few formulas, but the simple tools are sometimes the best for making something presentable…
I teach them because they’re often expected to know them, but make sure they know how to do anything lookups could do with INDEX and MATCH. And I tell them when asked on an interview whether they know them to work in the idea that they prefer INDEX and MATCH in a lot of circumstances.
^actually its the same thing for programming. i used to do SAS. i always went through someone’s programming to see what it was doing to the data sheets. plus they already took care of the syntax, you just gotta change the labels.