Moderator note (Andy) This was originally posted on 7/9/12
As summer has been moving along, I begin to notice more and more the inefficiencies out interns have with using Excel properly. Because of this, I have created a brief tutorial on some basic functions of Excel which we use every day. I have attached a spreadsheet with various functions already created within it. Please view the formulas in the cells highlighted and the other notes in the spreadsheet.
As you get better at this, you should not need to use a mouse to navigate through the model at all. Here are some quick tips to help with not using a mouse.
To navigate to the end of a column or row, press control and left or right, down or up. This will shoot you to the end of the data. You may have to press twice or more depending on if there is empty data in between the beginning and end. To highlight this data, press control + shift and left or right, down or up.
Shift + space bar highlights entire row
Control + space bar highlights entire column
… After doing either of those, press Control and + to add a column (or row).
Use Alt+tab to move between different programs (word, internet, excel, etc.)
In order to use the toolbar at the top, press ALT, which will then highlight all the functions at the top. The more you familiarize yourself with these letters, (ex: Alt + H + D + R, which deletes row in Excel 2007), the quicker they will be.
To enter a cell to adjust a formula or just to view which cells are in use, press F2.
Use the F4 function to create absolutes on formulas within cells. Based on how many times you press, you will receive varying functions, ex: $C$2, $C2, C$2. View the Code section of the DATA tab within my spreadsheet to see further. The $ keeps the cell from moving when copying formulas down or across.
When you are in a cell that is pulling data from another tab or another section within the tab you are in, press Control + [ to jump to that cell. You can use F5 + enter to return back to your original tab and cell.
Vlookups, Pivots, and various other stuff is shown in the spreadsheet. Practice understanding what is happening in the formulas and practice recreating some of these sections until you have it down. If anyone has anything to add or if I should go more in-depth in one of these areas, just let me know. Enjoy monkeys!
15% Off wall st. prep Financial Modeling Training Click Here
| Attachment | Size |
|---|---|
| ExcelTips.xls 38.5 KB | 38.5 KB |
You have interns that seriously don't know this shit? WOWWwwwwwww. Thanks for this though (+1). I think the kiddies might also benefit from knowing their way around creating data tables if they don't already. I use those on a daily basis... I'd be interested in seeing some advanced excel techniques/shortcuts you might be familiar with?
Collectively, no they don't. They know simple functions such as sum and average but most of this stuff they are not 100% comfortable with.
I will have to think about some advance functions I can write a post about. Thanks
Not all of us were jerking off to Excel in college.....some of us were getting laid...
Yeah, I guess I just wanted to be sure I set myself up for a successful career more than I wanted to go out, party, meet girls and get laid. We all place different levels of importance on things, it's cool brah.
.
Helpful stuff=silver banana for you! Will give this a look to freshen up later.
CTRL + Page up/down to toggle between sheets.
IF(Current Balance>5mn,"Baller Matt","BO")
THIS.
it'd be nice to include the function or short cuts to remove circular reference for those folks in IBD :)
great spreadsheet TS! props!
Circ refs are the worst things in the world. They're almost always done by accident anyways, they freeze your computer, and they open the Help tab. Nothing pisses me off like a circ ref.
Very good thread, OP. Vlookups are essential
Actually there are lots of good reasons to intentionally circ ref, especially in financial models, usually because the relationship between debt and interest and income itself is circular (see http://www.wallst-training.com/WST_Circular_Reference.pdf).
SOLUTION The best way around this is to "set calculations to manual and limit the iterations."
What that means is Excel will only recalc when you hit refresh (F9) and it 'iterates' a set number of times - this enables the numbers to reach steady state levels.
To do this (a little different depending on 03/07/Mac) go to Options/Calculation and click 'Manual' for recalculation, and limit iterations to something like 1,000. Remember now to hit F9 lots, basically any time you enter in a new formula or edit a formula/assumption, because otherwise you might have old numbers in your sheet.
Good analysts will wear out their F2 and F9 keys - and also literally pry out the F1 (help) key with a screwdriver, because it only gets in the way of F2.
Some models also build in a 'circuit breaker' (zero's out the trouble cells when toggled).
Circular references are very necessary in modeling and at least in my world were hardly ever accidental.
Shit I need to get on your level.
+1 sb for you
Alt + T + O --> calculation --> Alt I + space + tab + 1000 --> enter
+1 SB to you for saving some interns a few hours during the summer - one thing I'd note though, and maybe this is just showing off - is that you could simplify your nested IFs on the first tab with a MATCH function
=MATCH($C4,$G$5:$G$8,1) in cell E4 and dragged down Works just as well as that big nasty IF
or if your outputs aren't going to be 1-4, use a LOOKUP (doing it oldschool...) =LOOKUP($C4,$G$5:$G$8,$F$5:$F$8)
Also could work as a VLOOKUP in this case, but you'd need to switch the order of the columns (ehh...)
Good stuff. Learn new things every day.
Nice match function, but I just don't understand how it knows to put the 1, 2, 3, 4?
Thanks a lot.
Thanks for the helpful post! I knew some of the keyboard shortcuts, but not all. F4 was particularly helpful, as was Control + the space bar.
Thanks, this is great!
The ALT key functionality is one of the greatest efficiency producers in excel. Its also one of the reasons excel for mac sucks, because they forgot to include that one little detail.
A few tips: -Whenever you are performing a task that seems like its taking longer than it should on excel, know that there is a formula for it. Google it or ask around, it will save you hours. -Like the title says, dont use the mouse! You will be much more efficient with keyboard shortcuts -The ALT key example is misleading - a quicker way to delete rows Control + - (minus sign)
-A few of my favorite ALT shortcuts include: ALT + W + A (To arrange windows) ---> by far the best one! ALT + W + F (To freeze panes) ALT + H + H + N (To remove cell color) ALT + H + B + N (To remove cell boarders) ALT + H + B (To add bottom boarder) ALT + G + G + A (To group cells)
-Other shortcuts: -F2 (To enter into a cell) -F4 (To redo last function / absolute) -Control + 9 (To hide a row) -Control + 0 (To hide columns -Control + - (To delete rows/columns - must be highlighted first) -Control + space (to highlight a column) -Shift + space (to highlight a row)
Hope this helps
Dyslexic much?
For bidirectionality, hiding and unhiding columns/rows can also be done as follows: ALT + O + C/R + H ALT + O + C/R + U
Shift + F10: pulls up context menu in excel, love this one.
Another favorite, not sure how many of you need this though: alt + e + s + v= paste special values. You can do any of the other paste special commands, the v is just for values. It's a little long, but once you master it, it's a huge time saver.
Great post OP.
Alt + M + D - trace dependents Alt + M + P - trace precedents Alt + M + A + A - remove arrows Ctrl + Shift + - - remove borders
Shortcut threads (including comments) are always helpful!!
This one needs a bit of 'mousing', but helps tremendously when modelling and testing/debugging model
Use F9 when highlighting a portion of formula to find out its result. (don't forget to press ESC afterwards, instead of Enter. So the result won't be pasted as value and the formula is kept intact)
Say, if you have a formula "=(B5B6)+B7", highlighting the portion (B5B6) and pressing F9 will give you the result of that calculation.
This especially works great when testing complex formula. For example, taking an example from valuation GURU's spreadsheet below, highlighting "C11$G$8,$F$8))))
good job
people who don't know this cannot claim to "know" excel
Another that I use all the time is to add shortcuts you use regularly to the quick access toolbar. I put in the borders, and other formatting functions I use the most so that they are ALT + 1, ALT + 2, etc.
Customizable VBA function for the Data example for those who are interested...code indents not showing up for some reason...
Function ChooseCode(Balance As Variant) As VariantSelect Case IsNumeric(Balance) Case True Select Case Balance Case Is 1000000: ChooseCode = 4 End Select Case False ChooseCode = "N/A" End Select
End Function
Nice - though I would warn monkeys here, that VBA functions execute WAYYYYYY slower than even a convoluted native function. Array formulas can also be your friend - though not in the case.
This is very helpful. Thanks
Also one of my absolute favorites:
Hit F5 (go to) Hit Alt+S (special) Hit O, then X, then G, then E (unchecks a few boxes) Hit Enter
You now have selected all numbers which are constants (hard codes). These should be blue. All others should be black.
This way you can quickly format sheets to show the hardcodes.
You can also tell that if something is blue but not highlighted, something might be wrong.
Indispensable when proofing your sheets.
Great thread. Thanks everyone for the input.
Good thread. Honestly, this is something that any intern should have a grasp on before starting the internship.
http://www.macabacus.com/macros also very helpful. Free and customizable and you can use it on PCs where you are not allowed to install software.
ALT F S ALT F S ALT F S
I probably use Paste Special - ALT + ES more than any other function. Major time saver
I enjoy ctrl + alt + down arrow.
In conjunction with the ability to highlight entire rows/columns, using CTRL+D and CTRL+R to calculate formulas across rows and columns is a pretty helpful trick as well.
Ctrl + ~
Not really a shortcut but awesome nonetheless
The TTS shortcut plugin is a life saver. It makes number formatting and center alignments across multiple cells a breeze.
CTRL + F6 Toggles between open workbooks (Alt+Tab doesn't always work if you have multiple windows) Alt + F4 Close the active window (Closes that pesky F1 help window. TTS plugin disables F1)
Where do you get the plugin?
+1 for TTS, anyone know where i can get a copy for excel 2010? always have to run in compatibility mode f's my shit up.
Shout out to Valuation Guru for creating this for interns. I was horrible with Excel when I was an intern. Didn't really learn it till I started using it at my FT job.
Cell C24 on the Random Formulas tab is wrong. Easy fix though.
These shortcuts are all over the web but here's some I use...
Ctrl + C for the selection in excel, then in Powerpoint, Alt+E+S then hit paste link as excel worksheet object. It's the best way to paste to PPT IMO if you want the PPT and excel to link up.
Ctrl + Alt + - Zoom out
Ctrl + Alt + = Zoom in
ALT + = auto sums cells
CTRL + : inserts today’s date
=CELL("filename") formula to insert filepath
SHIFT + F2 edit or insert comment
ALT + SPACEBAR + C close window
CTRL + Page Up Previous sheet CTRL + Page Down Next sheet
SHIFT + SPACE Select the whole row
CTRL + SPACE Select the whole column
CTRL + 9 Hide row CTRL + SHIFT + 9 Unhide row
CTRL + 0 Hide column CTRL + SHIFT + 0 Unhide column
ALT + DS Sort
CTRL + F1 collapse the ribbon for excel 2007
CTRL + F4 close workbook
fav of mine: Alt + E + S + L F2 F4. perfect for pasting links across or intra sheets while maintaining its integrity.
also highlight your range, F11, looks nice, Alt + E + L bye bye sheet
Alt + H + O + R, rename sheet.
I have to use CTRL + SHFT + L often in my job. It applies the data filter. Then you can use ALT + down arrow when at the top of a column to show filter options.
Holy cow! I needed this!! thanks everyone.
Great thread
this thread is like excel tips for 6th graders.
the first thing a summer analyst should do is remove the F1 key from their keyboard. instant street cred. (seriously.)
also, take the time to slow down, ignore the mouse, and learn all the hot keys and short cuts. you're going to be much slower at the very beginning when you need to look at your printed sheet of shortcut keys (these can be found all over the web), but in short order you'll be cranking in excel. if you take Training the Street, practice the spreadsheet formatting exercise they give you...it really helps
Just sent this thread to the intern in my office, unfortunately I think most of this will be new to him... yikes.
Wtf....the OP got 11 bananas for posting this? Shows how many undergrads there are on this forum, since I'm pretty sure every banker knows these shortcuts already.
Also, do people seriously use alt h d r to delete a row?
Don't hate on the undergrads. Also, I use that delete row combination. Do you know something faster/better?
ctrl minus
I think another formula that is good to know depending on the types of models you are creating is
index(xx:xx,match(xx,xx:xx,0),match(xx,xx:xx,0)) Just need to make sure your have unique identifier tags.
Who the hell is downvoting everyone?
Some tips: - don't use nested IFs. Don't use the Switch function (which you can recreate in VBA). Use a vlookup. - use named ranges. Make them dynamic: if there are a few, type it directly in the formula manager, if there are a lot, create a 2 column array (actually, a "dictionary" - key/value) and pickup the range in VBA to create the named ranges (range name left, range target as INDIRECT, right). - use sumproduct, double negative, named ranges as bitmasks for lookups involving more than 2 dimensions (particularly when vertical and horizontal filters are required). - hardcore: replace your VBA with Python scripts. You will save time and make better code. Use an add in (DataNitro is decent). - generally spend some time learning to program. It will inform the quality of your models. Always take the time to do things properly first; shortcuts now will cost you 10-50x the time in the future; output quality is a major issue in consulting and banking as time is scarce, and having too many mistakes in models can get you fired (not for that, but because nobody will want to farm work out to you, so you will do little work).
Apologies if this was already mentioned in the thread... but I find a lot of people don't know about [Windows key] + [left, right, up] for snapping windows to the left side, right side, or maximizing, when using Windows 7.
Alt + F4 = Save your work after hours of not saving it
If anyone actually did that, they deserve it
Alt + Shift + Print screen => Print preview so you make sure you don't print 10 columns into 3 pages.
Question: Does anyone use the "Boost" add-in that Wall Street Prep uses in the real world? I'm afraid after getting used to a lot of their short cuts I won't have them once I start a job in modeling...
Very helpful... thanks.
I think the thing I was happiest about during my summer gig was the fact that I really didn't need to use any of this shit. Just seems rather annoying, to be honest. Got to the point where I started pulling R windows to do simple computations instead of a spreadsheet.
That reminds me when I worked in MBB some kid wrote a macro that disabled F1 and made the right hand side Alt to behave as the mouse's right click. Sorta useless these days but you can get the point when keyboards were 101-key, serial ports, non-customizable. It was pure magic, especially in Powerpoint.
Great post, all in all. I do like shortcuts, and I also like the workings of MATCH, INDIRECT, INDEX, etc, but what really rocked my world back in the day was COUNTIF and SUMIF, which allowed me to make PivotTable-like shit much more flexible. Invest some time into learning that if you don't know what they're good for. And those were extended into COUNTIFS and SUMIFS in 2007, making life even easier.
Now what is REALLY awesome are array formulas (Control+Shift+Enter formulas). That is just infinite, especially when you realize that the "bidimensionality" we impose our models comes from the fact that we don't know those exist, or when we do, we don't give proper thinking to the possibility of making our models 3D or even 4D. When you stop to digress about the sort of crap that those formulas could help you get done, it's Excel epiphany.
OH FUCK, we're so nerdy. In my next life, I hope the only models I see are those who will give me massages.
do bankers really do so much repetitive formatting that this is necessary?
also lol at the idea of using python to manipulate excel financial models. even using array formulas will make your sheets hard to audit and use for anyone else not on your level...
I use the SUMIFS formula on a daily basis. It's awesome when you have a huge data set and need to create dynamic analyses from it. Named ranges are also key.
Two words which will change your life: think-cell
If you know, you know.
Does anyone have the Excel file?
.
Reviving an old although potentially relevant thread as SA season approaches.
How different is Excel on Mac and PC? If I have a Mac, is it even worth trying to learn some of this stuff for use on PC during an SA gig?
Executing VBA code is verrryyyy different.
I had a couple spreadsheets to finish. Came across this page and in 20 minutes I went from being an excel rookie to the All Star in about 20 minutes. Thanks OP
How can I execute the shortcut CTRL + [ on an international keyboard (Italian in particular)? "[" can be inserted only using "Alt Gr" button, so the shortcut doesn't perform at all. Furthermore, is it CTRL + [ or CTRL + Shift + [ ? As on the web I could read various opinions. Many thanks.
Mouse free?! (Originally Posted: 08/28/2011)
1st year analyst. Half the office is out on vacation and can't find any help right now. Looking to learn all the keyboard shortcuts outside of excel.
Outside of Mozilla Firefox, I can't find a plug in for Internet Explorer that will display the shortcuts for navigating between the desktop, different programs, outlook, etc.
Any help/suggestions would be much appreciated!
Between tabs: CTRL TAB
Between open programs: ALT TAB, then tab to scroll if more than 2 open
Between open program and desktop: Win + M
Re-open: Win + Shift + M
Have you looked here?
http://support.microsoft.com/kb/126449
the barrel goes into your mouth, and get your big toe around the trigger
How many of you can use excel without a mouse? (Originally Posted: 03/20/2012)
Just curious. I cannot.
ctrl c alt esv what else do u need to know???
What an excellent thread topic choice OP. I too am incredibly confident that this will NOT turn into a total pissing contest in which users try to show who has the biggest e-dick. Thank you for starting it!
Pretty much everyone who who works fulltime in a front/mid office position on here is probably godly at excel.
I can. Do I win something? . If so you better have a lot of prizes for every other Tom, Dick and Harry who can.
I'm gonna come right out and admit needing a mouse.
I'm just so excited my office is overstaffed that I can parcel out this work to the new Analysts while I put in some quality facetime with my boss, clients and counterparties.
Douche comment but I now have a strong sense of smug self-satisfaction washing over me as I type this out...
alt e s f
lol you know how I know you're not in banking?
learning BC I'm starting an FT analyst stint this summer. Its tough but I'll be happy when I'm smooth at it. Are you learning on a laptop or desktop?
both. laptop at home, desktop at school
I need the mouse for some things, but generally speaking, I can do most of my work without a mouse.
i can. holla!
Oh look OP is a raging Phaggot
negged
I had a VP who once told me: "Every time you touch the mouse costs somebody money."
Temporibus est atque dolores ab molestiae ea quod. Aut totam repellat incidunt sunt eius corrupti fugiat. Odit est est optio voluptatibus labore. Dolorem assumenda et minima et ea veritatis modi.
Voluptatibus sit possimus reprehenderit porro molestias sint minus. Quisquam sapiente ipsam id et et distinctio.
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...
Dolores odio voluptatem velit id architecto voluptatem tenetur. Corrupti nostrum accusantium sed laboriosam totam ut necessitatibus ut. Veritatis sit est animi. Necessitatibus architecto voluptatem officia provident. Doloribus asperiores labore rem.
Ipsam laborum necessitatibus et officiis quas est sint asperiores. Incidunt qui aut voluptates tempore qui. Temporibus dolore animi non commodi quia optio suscipit. Molestiae esse non eaque inventore. Laborum dolorum libero distinctio soluta voluptas natus.
Quaerat velit deleniti necessitatibus ab ex voluptas dolorum. Qui recusandae consectetur recusandae qui deserunt. Quas in ut possimus quasi magni quo. Inventore enim voluptates eum deserunt doloribus. Velit non rerum consequatur sint illum. Consectetur sint et quo odit hic sint nobis. Placeat voluptates a nesciunt iste quis ut ab provident. Rem id consequatur accusantium provident sit sunt.
Id quam nemo velit molestiae. Repellendus et rerum enim atque. Illum quia labore fugiat.
Saepe ut ea quisquam temporibus ea ut ipsa possimus. Qui accusamus nobis eum libero. Et molestias dignissimos voluptas aliquid quod. Ut et dolor et. Autem explicabo doloremque quis qui fugiat. Molestiae quisquam esse deleniti asperiores.
Beatae nemo qui magnam qui. Temporibus iste excepturi quaerat optio ea unde velit cupiditate. Et dolor similique aut inventore.
Sit dignissimos eum voluptates et debitis molestiae aliquam. Reiciendis perspiciatis sint ducimus. Id sunt tempore animi quia blanditiis ipsum quasi. Omnis voluptatem earum est ut in omnis. Porro quia neque possimus et enim. Et similique eum sit enim. Reprehenderit nostrum amet non aperiam.
Quibusdam dolore et et et excepturi. Inventore nisi fugit dolor. Inventore totam quod quas. Quidem suscipit accusamus vitae facere.
Eos et asperiores aut repellat ab vel. Sit fugit adipisci eligendi tempora et voluptatem quasi. Sit vitae eos provident. Et sint nisi voluptatem. Eveniet minus amet veritatis. Doloremque ipsa quisquam accusantium libero accusamus dolore mollitia.
Nesciunt vero cupiditate ex ratione. Non excepturi temporibus ut ut. Atque doloremque cupiditate quo voluptas. Aut aut repellendus nulla doloremque eveniet numquam dicta. Quo aliquam veniam quidem. Iste omnis qui quia laboriosam. Est non porro temporibus nisi similique.
Molestiae et sed fugiat. Quasi voluptas ut odit tenetur. Incidunt sit explicabo saepe eaque tenetur. Ratione ipsum neque eligendi illum sit et aut odit. Sapiente nisi qui eveniet voluptas corrupti velit asperiores.
Aut et qui tenetur molestiae aspernatur voluptas. Doloribus deserunt qui maiores eos libero sint sunt. Officiis eaque impedit quis praesentium enim.
Minus quia eum pariatur odio odit eum quis optio. Consequatur a ut earum sit ducimus quo. Sit id omnis sunt quos quam aut dolor. Veritatis illo beatae placeat eum molestiae eius. Laboriosam aut reiciendis numquam rem vero nisi.
Dolores necessitatibus officia mollitia aut nisi sit quam. Pariatur est perferendis voluptatem molestiae. Dolor sunt esse non occaecati nemo nisi nisi. Sequi veritatis non exercitationem quae ut alias et id. Numquam aut animi accusamus quas itaque sit perspiciatis. Et repellendus omnis assumenda eos sunt.
Tempora voluptatem possimus aut. Voluptas earum non iure rerum quos est aut. Dolorum labore velit omnis minus et quia. Vel aut in quasi.