Put Me Some Excel Knowledge

Water Cooler
#21

One cannot stress enough Numi’s first point - impeccable formatting, attention to detail, clear and transparent presentation, assumptions and inputs carefully labeled, coherent and defensible analysis that is easy to follow, output results augmented by sensitivity tables.

The importance of this, in my opinion, overshadows by far any knowledge of programming or some obscure or not-so-obscure excel built-in function. If you have never used VLOOKUP, it should take you a only a few minutes to familiarize yourself with its functionality, try a few examples and begin to use it. I know that an average student can learn this quickly from scratch - but you cannot acquire the finanical presentation skills without a lot of practice. The most common deficiency in the skillset of fresh graduates is overall sloppiness and inability to translate analysis that is inherently technical in nature into something coherent and comprehensible.

#22

Not really, your basic SELECT, FROM, JOIN, WHERE, GROUP BY. There are of course more statements (HAVING, CASE, etc) and various types of joins depending on the tables you’re working with and the data you want to query but knowing how to use these basic statements is not all that hard.

#23

Don’t forget your basic RIGHT, LEFT, and CONCATENATE functions.

Plus, there’s text-to-columns, freeze panes, remove duplicates, and goal seek.

Subtotal is something I got very fond of once, but I think there’s not a lot it can do that Pivot Tables can’t do.

#24

Also, I have love me some Ctrl+G > Special > Visible Cells Only.

This allows you to copy and paste only the visible cells (not the rows or columns that are hidden).

#25

Best book I’ve seen on the query stuff (none of the database management material) is Viescas and Hernandez’ SQL Queries for Mere Mortals. Everything you need, and not much else.

#26

Bloomberg functionality.

#27

Agreed these are useful functions, especially if you’re in banking or PE and get presented with a cluster of poorly formatted data that you need to sort through extremely quickly

#28

Yea, learning how to use services like Bloomberg and SNL (and design spreadsheets to use the data once its imported).

#29

I think vlookup and pivot tables are the most important, then SUMIF, and IF criteria is important to know, then basic formating and i would say graphs or pivot graphs because you may need to produce reports for management?

I don’t know why people still ask about macros… it’s quite an “old” technique and i don’t really know a lot of people still use it today. but quite often recruiters like to ask if you know how to write macros.

#30

This

#31

I’m betting you have no idea what a macro is.

#32

^It’s the opposite of a micro, stupid.

#33

CapIQ plugin

#34

Surprised that macros isn’t mentioned more often. There are so many things you can do using macros that cut your time spent in half.

For example if you have a list of say 1000 unique ids and need each id to repeat 10 times and then the next id to do the same it takes 2 min in macros. Can you even do it without macros?

SQL is easy and useful if people around you use it.

SAS is another language i’m hearing good things about… Those 3 should be more than enough.

#35

Numi’s post was by far the best. Problem with macro is transparency. Excel spreadsheets are often not for the creator. The more complex it is, the more code you have, the less transparent it is. And people will be more worried about behind the scenes errors. I can normally make my spreadsheets neater by combining multiple calculations into one, but for someone new to the spreadsheet its much harder to follow. Same with Code, as most can’t even read past basic loops

#36

Thanks for all the good comments. Most confirmed what I’d thought, but some (like OFFSET, MATCH, INDEX, and the text functions) aren;t things I cover that much. I’ll have to come up with a few assignments to make them do those. I’m also going to beat them up a lot more on proper formatting (and color-coding inputs vs formulas vs text, if just for debugging. Making spreadsheets whose appearance doesn’t suck seems critical.

Also good to hear that while VBA isn’t essential, it does differentiate them (and for undergrads, it’s all about differentiation).

#37

True.

Just saw his post, stll a very useful skillset to have. It helps you finish 5-6 hrs worth of work in an hour and you can chill for the rest of the day.

#38

If unique ids are in column A, cell B1 is set equal to A1, then put this in cell B2 and copy down 10000 cells or so

=IF(MOD(ROW(B1),10)=0,IF(OFFSET($A$1,ROW(B1)/10,)="","",OFFSET($A$1,ROW(B1)/10,)),IF(B1="","",B1))

I would say SAS is powerful, but it has a steep learning curve (mainly because it’s syntax is quite different from every other statistical language). I have heard that a lot of finance professors use it because it is good for handling large datasets. R is probably easier to learn and could probably accomplish what you need (as there has been a lot of development on “big data” tools that used to be SAS’s key advantage).

#39

Here is a good example of a formula that I never want to see in any spreadsheet that I’m working with.

Didn’t I hear somebody say, “The more complex it is, the more difficult it is for the user to understand?”

#40

If you are just querying a database, SQL isn’t that difficult to learn or pick up.

If you are building and maintaining databases, it still isn’t as challenging as a full programming language, but using it correctly and non-destructively can get pretty intricate.