Put Me Some Excel Knowledge

Water Cooler
#1

I’m a b-school professor. And since it’s valued in the “Real” world, I try to give them as much Excel work as they can stand (and a fiar bit more). For me, it’s not just an academic exercise - I want them to do well on interviews so that they get good jobs and will be happy and successful enough that they buy me good booze down the road - that way, incentives are aligned, and everyone’s happy.

Since a lot of folks here work in the very industry my students are trying to get into, I thought I poll the collective wisdom (whatever that amounts to) of the group. So please answer me these two questions:

  1. What half-dozen Excel skills are required so that the student wouldn’t look like a schmo on an interview?

  2. What Excel skills would really separate them from the pack?

Keep in mind that these are finance undergrads looking for their first jobs.

#2

I know that Vlookup/Hlookup can come up in interviews in many discplines. Also knowledge of IF statements if they’re starting from zero. I think everyone should at a minimum learn all the sumifs, averageifs, countifs, etc. And depending on the data they’d be working with, pivot tables can also be useful. And people should all realize formatting is 50% of the work since a lot of spreadsheets are used by others or in reports – so emphasis on readability, conditional formatting, etc I’m sure would help.

#3

I know most people would say Pivot Tables are a “must-know”. Personally, in my job, I don’t see a lot of use for them. Ditto Vlookup and Hlookup.

IF statements are crucial, and I also use SUMIF a lot, as well.

Overall, I don’t know if there are any functions that they need to learn. They’ll probably learn most of them on the job. It’s more a function of speed. Little things like using F2, opening a new window so you can work in multiple tabs at once, etc. are what (IMHO) would really make a person better at Excel.

EDIT - rawraw beat me to it. Yes–formatting is very important, and so is “understandability”. If you have a perfectly put-together, dynamic, complex spreadsheet that I don’t understand, it’s useless to me.

#4

Knowing basics like lookups, IF statements, data validation and pivot tables is an absolute must.

Knowing how to quickly format cells, edit and generate tables and charts is key too. They need to be comfortable “ALT-ing” into nearly everything instead of clicking around. Linking Excel to the other applications like Powerpoint and Access is semi-important too.

Edit - nearly forgot using F4 for absolute and relative references is very important as well. Don’t want to be the joker putting in dollar signs one by one.

#5

^yah i agree. Pivot tables are highly overrated.

How about for a final project you can have students build out operating and functional DCF, Comp, valuation models?

#6
  1. I’d probably include:
  • Formatting cells (currency, date, justification (left, center, right), bold, italics)
  • Using built-in functions (especially SUM and AVERAGE)
  • Using absolute and relative cell references (to facilitate copying and pasting)
  • Basic graphing (including moving graphs to separate tabs)
  1. I’m less sure about this one, but I’d probably include:
  • Knowing how to use VLOOKUP and HLOOKUP
  • Using pivot tables
  • Naming cells and ranges of cells
  • Using macros
#7

Pivot tables are only included because every single HR monkey will ask you if you know how to do them. Lookups and pivot tables are the only uses Excel has according to them.

#8

Best practices, such as putting hard coded inputs in blue bold, formatting, setting the god damn print area properly.

sumproduct, all the “ifs”, lookups etc, but those are all just excel skills.

I wish as a student I would have had someone teach me a basic model. Getting from revenue to free cash flow, and doing basic npv or irr would have been adequate.

You can also teach more statistical finance, such as looking at a series of returns and calculating beta, demonstrating CAPM, etc. I got most of my excel skills from a class that taught statistics and portfolio theory because it forced you to get good (efficient) at manipulating big data sets.

#9

Arial 10

Black for formulas, blue for text inputs

Understand Pivot Tables and V/H Lookups for the HR Monkeys

Graphs/Charts

Quick Keys like F2, F4, Alt, CTRL, etc.

#10

I usually don’t ask about Excel in interviews because I assume that any smart, computer-literate person should be able to figure out what they need to. I would generally consider someone proficient if they are able to use the following properly:

  1. if / iferror

  2. vlookup/hlookup

  3. match (combined with lookups properly so that you’re not hard coding what columns stuff is in)

  4. offset (combined with match)

  5. named ranges

I include if statements in almost everything I do. I consider knowing them and regularly using them a must.

How to structure excel documents is very important and not often emphasized in most courses. A lot of people have different preferences on how to do this. I’m not sure there is a right way or a wrong way, but there certainly are ways that take a lot more time each update and ways that take less time. The general idea is to spend some time setting up the documents initially so that it is as little work as possible to update going forward. Finance types are always updating excel documents.

I prefer to have a (or at least a) sheet of constants, which I will often name. I will then have a small number of sheets that I use to import data from. This reduces the amount of time spent in updating documents. I then do all my calculations on sheets completely separate from the import sheets. The calculation sheets are all set up to look up information from the import sheets. The excel formulas are all general enough so that it is very easy to make adjustments. They typically will automatically adjust based on the number of columns and number of rows in the import sheets.

The limiting factor is usually that higher-ups don’t have the time to understand some of the more complicated excel documents I’ve created. If senior people can’t follow every chain in the logic of why a number is produced (so they can explain it to their bosses), they can get upset. I will usually go out of my way to ensure that a document for such people can be easily understood (which might involve removing a lot of the more efficient stuff discussed above). This is also why I stopped using VBA in excel.

Of course, if everyone understood R, then what is complicated in excel becomes simple.

Edit: S2000 Magician is absolutely right about absolute and relative cell references. I just consider them so integral to excel that I had forgotten them. I also don’t consider pivot tables that important. I only have one document that uses them and they’re pretty easy to figure out if you have to need them.

#11

There must be some kind of 5 hour Excel tutorial somewhere where people can apply all this stuff. But I agree with jmh that students should learn programming first. This teaches them to think logically and make efficient Excel worksheets with good flow.

#12

For a bit of context - in my student-managed fund, they build full 3-statement pro-forma DCF models with lots of nested IFs, data tables, conditional formatting and other bells and whistles. My question is more for my advanced class, where they do a lot of little models.

Keep those comments coming. I’ll probably share this with my colleagues. A lot of it is things I already do, but they’ll probably give more credence to it if they hear it from industry folks. A prophet gettting no credit in his own land and all that.

#13

I would echo what everyone has said and would emphasize having a basic knowledge of VBA and SQL. It has really been what has set me apart from the pack. They should understand how to open the VBA editor, insert a module, difference between public and private sub routines, inserting a userform and associated tools like combo boxes, labels, check boxes, text boxes. They should know how to open an ADO connection and ADO recordset as well as how to connect to a database on SQL server or Access using some driver (e.g. sqloledb or microsoft jet database engine). In additon to Excel, learning database theory and exploring normalized databases and how they’re joined, what determines their primary keys and foreign keys will serve them in writing customized queries and doing things other analysts can’t do. Something simple to start off would be at least knowing how to use microsoft query in the data tab on Excel.

#14

Well, I have worked in IB, ER, PE, and HF which probably represent a large cross-section of what your finance students are trying to get into. Here are the first things that came to mind:

* Impeccable formatting and attention to detail

* Using Excel without a mouse

* Building sensitivity tables and DCF from scratch

* Fully linked three-statement models

* VLOOKUP, HLOOKUP, etc.

* SUMIF, COUNTIF, etc.

* Trouble-shooting circular references

* Pivot tables

* Conditional formatting

Some people mentioned VBA, SQL, etc. but I have never used these and only once had to build a macro because someone dared me to. What is mentioned above represents 95% (or more) of what your students will be expected to know for the job. That is to say, little more and nothing less.

#15

I echo the formatting comments. Teach them how to use colors effectively. A white canvass with numbers strewn about can be just as difficult to read and understand as rainbow of unorganized highlighted numbers.

I’ve used pivottables a couple times, but I don’t see the allure.

Maybe you shoudl teach your students efficient ways (and when) to google excel tips. I think JDV once said something like, “I can get Excel to do anything I need it to do. The question you should be asking is whether or not I know why I’m doing it.”

#16

I agree with Numi that being able to program in VBA is not likely something they’ll need to GET a job…but it will certainly differentiate them. It helps automate many tasks that are manual if you only know formulas and other Excel functions. Knowing VBA can save you A LOT of time on many things.

#17

The point is that Excel skills are pretty trivial to someone who knows more advanced programming. If you ask “what Excel skills should I learn”, I don’t even know how to answer that question. As far as hiring is concerned, I would never ask someone if they can use Excel if they know how to program - it’s a safe assumption that they can learn it quickly. Different jobs have different requirements of course, but this is just feedback from my point on view.

#18

Basic things that I think are good to know and feel confident about (both the formula and samples of how to use it)

* AVERAGE and STDEV (.P and .S if you want), CORREL

* VLOOKUP and HLOOKUP

* SUMPRODUCT

* OFFSET (I use this one A LOT, often for selecting subsets of data to send to aggregating functions like AVERAGE and STDEV).

* If you are more quanty, NORMSDIST and the inverse + SOLVER, otherwise not.

* MATCH and INDEX are also pretty useful, though not essential. Students should know they exist, what the ‘gotchas’ are, go through an example or two, but I wouldn’t begrudge someone who couldn’t remember off of the top of their head how the syntax worked. In practice, I tend to use MATCH with OFFSET more than INDEX.

* SLOPE and INTERCEPT are useful when you want to fit a line without running through an entire regression package.

* I would thow in Pivot Tables, but with some context

Pivot tables are interesting. People get caught up in the pivoting aspect of pivot tables, which to me is a techno gee-whiz factor that distracts from the fact that tables are used for cross-tabulations where at least one variable is categorical (nominal or ordinal). I’ve never pivoted a pivot table, but I have had pivot tables come up now and then because I need a cross-tabluation.

More important than learning how to do it (which one can learn in a few minutes) is learning what types of problems call for it. However, once you’re teaching that, you might as well go through with the how.

In practice, I use pivot tables to identify means and standard deviations for data which might have seasonal features, or to distinguish between industry groups or other categories. Pivot tables don’t auto-update nicely, which is one reason that I sometimes try to mimick their functionality with SUMIF and COUNTIF statements, but if I really need the standard deviation, there often isn’t much alternatives.

I second JMH’s point on how to organize data and parameters to make sheets easy to work with. I use blue cells for user-defined parameters and then color a number of cells in light orange for important outputs. It’s less pretty than blue bold, true, but I find they’re a lot easier to spot on a page, and that’s often the priority for me.

I will sometimes have the first sheet be a set of instructions for how to use the rest of the sheet.

I will also tend to have a sheet (or maybe more) solely dedicated to raw input data. I tend to have lots of tables of price or return streams, so I keep one sheet for just the raw data, and everything else just copies off of or references that sheet elsewhere. This won’t work for interlinked 3-statement company models, but it works for the kind of asset allocation and backtesting work I often do.

Creating tables from a formula or worksheet where you have one or two input variables across rows and columns; again, not somethign to have memorized, but a funtionality they should know is there and know that they did it at least once.

How to name variables might be worth teaching. I do that less than I think I should, but I think it’s good for people to know about.

Conditional formatting is useful too, though Excel keeps changing how they do it with every update.

Good Charting techniques would go a long way to being useful (when to use which type of chart, in a Tufte like approach)

How to write a macro formula is useful, but not essential (e.g. write your own sharpe ratio funciton given an input of asset levels over time)

I wish I knew about EOMONTH a long time ago. It would have made many sheets easier. Not essential, but good to know about.

#19

I need to learn almost everything in this thread, thanks for the posts, keep em coming!

#20

this should be a sticky