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.
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.
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)
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.
i'll take your vlookup and raise you an index match, superior on every level
great excel post: http://www.wallstreetoasis.com/blog/stop-using-your-mouse
also http://www.wallstreetoasis.com/excel-keyboard-shortcuts
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.
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.
xoxo
Index Match = best formula in Excel.
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.
Alt+H+O+I resizes all selected cells so that the contents are visible. Add Ctrl+A to the beginning to expand all cells
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.
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!
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.
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.
Ctrl + [ go to first cell referenced in the formula of the current cell.
In conjunction with using VLOOKUP, I think IFERROR is very useful
thanks!
please don't ever use vlookup or hlookup...theyre near impossible to audit. offset/index/match/choose pick a couple and use it.
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...
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...
But how do you add comments to the cells then?
xoxo
xoxo
You go this website: http://lmgtfy.com/?q=excel+shortcuts
I assume I'm being fucked with right?
Numquam quasi odio officiis rerum sit illo autem veniam. Omnis neque sit dolores atque consectetur. Aut enim sit sit. Doloremque qui minima voluptatum dolore autem dicta nam in.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Ipsam fugiat consectetur dolores voluptatem corrupti. Odio aliquam consequuntur in possimus et sed.
Impedit consequuntur qui earum sunt voluptatem consequatur ut. Rerum beatae distinctio excepturi. Qui numquam nihil amet alias nobis numquam velit ut.
Dolor sed possimus voluptatum odio iste. Voluptas cupiditate mollitia nulla quia sint. Voluptatem dolor architecto iusto dolorum. Facere doloremque distinctio illum autem vel voluptas labore molestiae.