Excel Stuff to Learn Before Your Summer Starts

I'm no Excel expert by any stretch of the imagination, but here's a brief discussion of things you might want to familiarize yourselves with before starting your Summer Analyst stint. Please feel free to add to this list -- I know I could use some Excel training too!

With that out of the way, let's Control + Page-down to the next part of this discussion...

This is kinda of a high-level overview and this is BASIC stuff, I'm not writing out a bunch of shortcuts because you can find that stuff through Google. But there's a difference between knowing the shortcuts and understanding how to move through the program efficiently.

1. Learn to move around quickly

Switching tabs (Control+Pg Up, Pg Down), switching to cell A1 (Control+Home), selecting data group (Control+Shift+Arrows) are all ways to move around the spreadsheets more efficiently. Figuring out what cells trace to which others (Alt+M+P) is also another useful way to get your speed up. Using the F2 key to change formulas and trace cells is one of the things you'll use more frequently than others -- and editing doesn't hurt either!

2. Learn to copy and paste like a boss

Alt+E+S gives you the paste special screen, and from there you can copy values (V), formulas (F), and formats (T) -- those are the modes I use most frequently. You can also transpose a data set from horizontal to vertical orientation, which is handy, and multiply or divide a data set by a certain constant (like if you have raw data in $1,000,000 form but want to change to $1.0MM for example).

3. Learn vlookup

As an intern, you might get some fun and interesting tasks like consolidating raw data and formatting it / making it more readable. Companies send data over in bizarre states with sometimes-impossible-to-understand naming and labeling conventions, and the senior guys always want to see it nice and pretty. Vlookup is a baller function that will let you manipulate this data in a quick and robust manner. Vlookup, and its rarely-used cousin, Hlookup, is extremely useful...own it.

4. CapIQ plugin

You probably won't get access to this until you start your internship, but if you have a chance to cruise through CapIQ and use the plugin, building formulas is pretty straightforward (and awesome because you can export everything you want to Excel and manipulate the data easily). You know what they say -- 90% of the job is using the CapIQ plugin (well, in reality this is far from the truth, but there's a lot of CapIQ.

5. Random useful shortcuts

Alright alright, here are some of the more frequently used shortcuts in my experience:

Alt+H+O+H/W changes height or width of a row
Control/Shift+Space Bar selects an entire row/column
Alt+I+R adds a row based on current selection
F4 repeats your last command
Alt+H+F+S changes font size
Alt+H+F+C changes font color
Alt+H+A+C/R/L aligns center/right/left

And there are a bunch of other useful thing within Alt+H.

Alt+A+G+G groups and Alt+A+H/J hides and unhides -- also very useful.

There are just a couple of things that I've found useful. I'm still very much a newbie, but other newbies out there can use this list as a good starting point.

Experienced Excel users, what can you add to this list? What speeds you up and saves a lot of time when you're going through the motions?

Thanks for reading.

Comments (54)

Apr 15, 2013

I'm offended I'm not included on this list (auto-fit column width). Alt-HOM then Alt-C to copy sheets.

Apr 15, 2013
Alt-HOI:

I'm offended I'm not included on this list (auto-fit column width). Alt-HOM then Alt-C to copy sheets.

Pssssshhh... Only noobacabras use Alt-HOI to auto fit columns (so 2000 & late). The wizards of the green sheets Alt - OCA that ho... and then Alt ESW to paste special widths. Why Alt-HOM when you can Alt-EM... extra effort.

Dirk Dirkenson:

Shut up already. Your mindless, reflexive responses to any critical thought on this are tedious. You're also probably a woman, given the name and "xoxo" signoff, so maybe the lack of judgment is to be expected.

Apr 15, 2013
Louboutins and Leverage:
Alt-HOI:

I'm offended I'm not included on this list (auto-fit column width). Alt-HOM then Alt-C to copy sheets.

Pssssshhh... Only noobacabras use Alt-HOI to auto fit columns (so 2000 & late). The wizards of the green sheets Alt - OCA that ho... and then Alt ESW to paste special widths. Why Alt-HOM when you can Alt-EM... extra effort.

Are you looking to take on any mentees? Older ladies usually like me, fwiw.

''You can fool some of the people all of the time, and those are the ones you need to concentrate on.'' -- President George W. Bush
0.5 bb

Apr 15, 2013
Louboutins and Leverage:
Alt-HOI:

I'm offended I'm not included on this list (auto-fit column width). Alt-HOM then Alt-C to copy sheets.

Pssssshhh... Only noobacabras use Alt-HOI to auto fit columns (so 2000 & late). The wizards of the green sheets Alt - OCA that ho... and then Alt ESW to paste special widths. Why Alt-HOM when you can Alt-EM... extra effort.

oh fack...

Apr 15, 2013
DonVon:

Alt+A+G+G groups and Alt+A+H/J hides and unhides -- also very useful.

Good stuff, thanks for compiling this. I also find it useful to have a printout of keyboard shortcuts pinned to the wall in front of me, it's a great reference.

I find that hiding rows/columns is an invitation to trouble. Hidden ranges are easily overlooked, and it's easy to forget to unhide, or worse, refer to a range that has hidden values, which will cause problems with sumifs etc. Grouping puts the little plus sign as a reminder, and it's easy to expand/collapse the range. The shortcut I use for grouping is simultaneous SHIFT+ALT+Right Arrow to group highlighted rows/columns. (left arrow to ungroup)

One louder.

Apr 15, 2013

Transposing will be ALT+E+S+E.

ALT+D+F+F for filter/list. Comes in very handy when managing tons of data.

One of the most commonly used shortcut for checking work is CTRL+ "[" which brings you to the cell applied in the formula in the current cell.

With regards to lookup functions, INDEX-MATCH is insanely useful for data management.

Apr 15, 2013

i'll take your vlookup and raise you an index match, superior on every level

Apr 15, 2013

WSO's COO (Chief Operating Orangutan) | My Linkedin

Apr 15, 2013

If you have multiple sheets within a workbook that are the same setup (ie different cashflow scenarios) and you want to edit them all at the same time you can ctrl+click the different sheets and edit them all at the same time.

Apr 15, 2013

For the color, size, fill, font, alignment, etc... I have the shortcuts at the very top, so I just hit alt + # instead of the longer way.

Apr 15, 2013
BTbanker:

For the color, size, fill, font, alignment, etc... I have the shortcuts at the very top, so I just hit alt + # instead of the longer way.

Beat me to it BT... I like your stylez!

xoxo

Dirk Dirkenson:

Shut up already. Your mindless, reflexive responses to any critical thought on this are tedious. You're also probably a woman, given the name and "xoxo" signoff, so maybe the lack of judgment is to be expected.

Apr 15, 2013
Louboutins and Leverage:
BTbanker:

For the color, size, fill, font, alignment, etc... I have the shortcuts at the very top, so I just hit alt + # instead of the longer way.

Beat me to it BT... I like your stylez!

xoxo

You should def hire me at your fund, so I don't have to do this stupid 2 year BB stint.

Apr 15, 2013

Index Match = best formula in Excel.

Apr 15, 2013

The most important function for life rather than just Excel... Alt+Tab. It allows you to switch programs quickly. This can be used when flipping between programs or just to hide the fact that you're on WSO all day.

Apr 15, 2013

Alt+H+O+I resizes all selected cells so that the contents are visible. Add Ctrl+A to the beginning to expand all cells

Apr 15, 2013

Thanks this is helpful. What are some tricks to get better/faster with the mouse as well?

''You can fool some of the people all of the time, and those are the ones you need to concentrate on.'' -- President George W. Bush
0.5 bb

Apr 15, 2013
Dubya:

Thanks this is helpful. What are some tricks to get better/faster with the mouse as well?

Don't. Keyboard > Mouse for everything except perhaps selecting colours. You won't do this either because you will be provided with some kind of plugin which will allow for colour formatting cycles bound to certain keys. You will never need to use the mouse.

Apr 15, 2013
Asatar:
Dubya:

Thanks this is helpful. What are some tricks to get better/faster with the mouse as well?

Don't. Keyboard > Mouse for everything except perhaps selecting colours. You won't do this either because you will be provided with some kind of plugin which will allow for colour formatting cycles bound to certain keys. You will never need to use the mouse.

Never?? You're kidding right? If not, I'm probably screwed or need to think more seriously about marketing.

''You can fool some of the people all of the time, and those are the ones you need to concentrate on.'' -- President George W. Bush
0.5 bb

Apr 15, 2013
Asatar:
Dubya:

Thanks this is helpful. What are some tricks to get better/faster with the mouse as well?

Don't. Keyboard > Mouse for everything except perhaps selecting colours. You won't do this either because you will be provided with some kind of plugin which will allow for colour formatting cycles bound to certain keys. You will never need to use the mouse.

Using the mouse is actually my favorite. Sometimes I like to right click on cells and select "copy" from the dropdown just to remember what the masses must feel when they use the green sheets.

Gotta stay in touch with your roots, Dubya! Hey, speaking of... I think you and me should grab some Forties and talk about how cool it is to own a baseball team sometime... All these sissy liberals on this site forget what it's like to have capitalized off of a family legacy built around oil. You don't have to listen to shit! That's what that means... I saw the true story of your life as depicted by Will Ferrell and it was inspiring. Like an eagle wrapped in an American flag... You're a patriot... you can do whatever you want!

You were the most powerful man in the world for 8 years... Don't let them give you shit for usin' a mouse... It's your god given right... Just like ownin' the Rangers was!

Dirk Dirkenson:

Shut up already. Your mindless, reflexive responses to any critical thought on this are tedious. You're also probably a woman, given the name and "xoxo" signoff, so maybe the lack of judgment is to be expected.

Apr 15, 2013

Ctl + R - copy formulas to cells selected the right
Clt + D - copy formulas to cells selected downward
Alt + = - auto sum cells immediately above selected cells

@Dubya - Yes, never.

Apr 15, 2013

Using the mouse isn't forbidden, just discouraged and frowned upon. You don't want to use the mouse while navigating the spreadsheet, but there will be times where you have to do something and not know the shortcut for because you normally don't use it. But 99% of the time you won't use the mouse.

Breaking into Wall Street's Excel module is good and where I learned Excel. There's also a good list of Excel shortcuts.

Apr 15, 2013

Ctrl + [ go to first cell referenced in the formula of the current cell.

Apr 15, 2013
DonVon:

5. Random useful shortcuts

Alright alright, here are some of the more frequently used shortcuts in my experience:

Alt+H+O+H/W changes height or width of a row
Control/Shift+Space Bar selects an entire row/column
Alt+I+R adds a row based on current selection
F4 repeats your last command
Alt+H+F+S changes font size
Alt+H+F+C changes font color
Alt+H+A+C/R/L aligns center/right/left

Can also use Alt-ORE for row height, and Alt-OCW for column width
I prefer Ctrl + Shift + (+) for inserting rows and columns (Ctrl + (-) will delete selected rows / columns)
For formatting, a pro tip is to drag your most often used formatting icons onto your top tool bar (for example: I have Fill Color, Font Color, Left, Right, Center, and Border). You can then simply hit "Alt" which brings up your "Home Tab options" and these icons will be numbered 1-9... You can then format things using "Alt + 6" for example... Much faster than the "Alt + H + F + C"... Game changer...

Dirk Dirkenson:

Shut up already. Your mindless, reflexive responses to any critical thought on this are tedious. You're also probably a woman, given the name and "xoxo" signoff, so maybe the lack of judgment is to be expected.

Apr 15, 2013

In conjunction with using VLOOKUP, I think IFERROR is very useful

Apr 15, 2013

thanks!

Apr 15, 2013

please don't ever use vlookup or hlookup...theyre near impossible to audit. offset/index/match/choose pick a couple and use it.

Apr 16, 2013

How do you drag a cell(s) to a new position without a mouse? Do you guys just cut and paste?

Apr 16, 2013
JPMortgage:

How do you drag a cell(s) to a new position without a mouse? Do you guys just cut and paste?

In before whoever answers seriously...

Dirk Dirkenson:

Shut up already. Your mindless, reflexive responses to any critical thought on this are tedious. You're also probably a woman, given the name and "xoxo" signoff, so maybe the lack of judgment is to be expected.

Apr 16, 2013
JPMortgage:

How do you drag a cell(s) to a new position without a mouse? Do you guys just cut and paste?

Scissors and glue.

Apr 16, 2013
JPMortgage:

How do you drag a cell(s) to a new position without a mouse? Do you guys just cut and paste?

Exactly. How are you supposed to do THIS without a mouse? Especially if you have a formula somewhere else that links to that cell. You can't just cut and paste that somewhere else or it messes up every other formula you have linked to it. I don't think it's possible.

''You can fool some of the people all of the time, and those are the ones you need to concentrate on.'' -- President George W. Bush
0.5 bb

Apr 16, 2013
Dubya:
JPMortgage:

How do you drag a cell(s) to a new position without a mouse? Do you guys just cut and paste?

Exactly. How are you supposed to do THIS without a mouse? Especially if you have a formula somewhere else that links to that cell. You can't just cut and paste that somewhere else or it messes up every other formula you have linked to it. I don't think it's possible.

This question had me so full of suspicion I had to actually go into Excel and try it. Copy pasting and dragging do exactly the same thing. This also raises the question of WHY you'd want to be dragging cells around...

Apr 18, 2013
Dubya:
JPMortgage:

How do you drag a cell(s) to a new position without a mouse? Do you guys just cut and paste?

Exactly. How are you supposed to do THIS without a mouse? Especially if you have a formula somewhere else that links to that cell. You can't just cut and paste that somewhere else or it messes up every other formula you have linked to it. I don't think it's possible.

No it doesn't mess anything up. Drag is identical to cut and paste. I really don't think there is anything the average worker does in Excel more than two to three times a day that is more efficient with a mouse. I think (though I don't know) that there is actually NOTHING that requires a mouse. I think you can run 100% of the excel functionality sans mouse.

Apr 18, 2013

You go this website: http://lmgtfy.com/?q=excel+shortcuts

I assume I'm being fucked with right?

Apr 18, 2013
Boothorbust:

You go this website: http://lmgtfy.com/?q=excel+shortcuts

I assume I'm being fucked with right?

Haha yes, I'm sorry... After you responded to the first one I had to see how far I could take it... Don't be maddd at me :)

xoxo

Dirk Dirkenson:

Shut up already. Your mindless, reflexive responses to any critical thought on this are tedious. You're also probably a woman, given the name and "xoxo" signoff, so maybe the lack of judgment is to be expected.

    • 1
Apr 25, 2013
Comment