Info about Excel

Water Cooler
#1

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.

#2

SUMIF formula, INDEX+MATCH formula, Pivot Tables, Charts, Macros

#Excel4Analysts

#3

Yeah and lots of models / practice

Also, shortcut commands.

#4

Make sure they know the “create new window” command, so they can work in multiple sheets at once.

And make sure they know how to make information flow from sheet to sheet.

#5

Equity Research

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.

#6

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.

#7

I’m a credit analyst

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.

#8

well said, ohai, as always well said

This takes time to develop these style skills, but they are worth it and in my opinion are another form of precision/transparancy.

#9

Be sure to teach them to name cells.

It’s a lot easier for someone to understand a formula that reads “= 2016_current_assets / 2016_current_liabilities” than one that reads, “= ‘2016 Balance Sheet’!A24 / ‘2016 Balance Sheet’!A43”.

#10

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.

#11

Good input. Keep em coming.

#12

Ctrl c and ctrl v and format painter lol

i usually create a raw where you copy and paste everything.

Then I use a modify tab and formulas connecting to important info in raw.

Then I use an output that has all the tables and charts.

pivot table is prolly most important. Eliminate the need for a lot of formulas.

data sort, filter, vlookup,

#13

I work at the middle office in the asset management division of a bank, and I use pretty much advanced level of excel at work. I regularly use these at work. and combinations of these.

For the top of my head:

1 way and 2 way lookups (VLOOKUP, HLOOKUP, VLOOKUP MATCH, INDEX MATCH, INDEX MATCH MATCH, Concatenated VLOOKUP)

String Manipulation: (LEN, TEXT, FIND, DATEVALUE, DATE, LEFT, RIGHT)

Arithmetic Calculations (and related): SUMIF, SUMIFS, COUNTIF, COUNTIFS, SUMPRODUCT

Logical: IF, ISNA, IFERROR, TRUE, FALSE, AND, OR.

Combinations of all of the above.

VBA is also extremely useful. I have saved innumerable hours of work using VBA. I also sometimes quickly write small snippets of code if I want to loop through many workbooks.

Disclaimer: This was self-learnt. Although I do use these regularly at work, it is mostly for projects or automation, not everyone in the team uses advanced excel.

#14

If you guys teach your students vlookup or hlookup, I hope it comes with a rusty hacksaw.

#15

Conditional formatting…

#16

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…

#17

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.

#18

Amen, brother.

And something else that these young whippersnappers need to learn (and these ideas are for more than just excel):

You can use other people’s ideas. Don’t be afraid to look at somebody else’s work and apply the best of what you see.

Just because you invented the model doesn’t mean that it’s a good one. Or that it even works. Don’t get married to your idea just because it’s yours.

All else equal, the more complex a spreadsheet is, the worse it is. As Einstein said, “Keep it as simple as possible, but no simpler.”

#19

^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.

shorter the code. the faster the processing.

#20

In an excel question this year in an interview, I was asked to do COCATENATE

I forgot how to do it (forgot the word cocatenate … knew it started with a C), so just googled it and quickly did it in less than 1min