Excel: Tools, Add-Ins, and Macros

Included here is the start of a much needed compilation of Excel add-ins, macros, and tools.

For the one person who'll read this, please add your most frequented excel shortcuts, and own collection/links of Excel Add-Ins/Macros/Tools in the comments down below. That way when any new WSO users search for "Excel Tips" they can find and bookmark this.

Boost Excel

Features:

Connecting routine tasks in Excel to custom shortcuts; Adding the Excel 2003 menu to the Excel 2007 and 2010 ribbon; Easily break up complicated formulas and trace precedent and dependent cells; Automatically color cells based on custom settings.

Download Link: http://goo.gl/fzxLC

Ex Stock

Features:

Allows you to download data from the Stock Exchange into the Excel file with one click. Required just insert a symbol (YHOO, MSFT, AAPL, etc)

Contains the following:
- financial statement - balance sheet - cash flow
- price history - daily summary
- basic technical indicators (SMA, MACD, RSI)

Key statistics:
- Valuation Measures - Financial Highlights - Trading Information

Download Link: http://goo.gl/RMoYB

SmartShortcuts

Features:

Designed by one of our very own WSO users, it fits you with an array of custom shortcuts to let you avoid that mid-life carpal tunnel.

Download Link: http://www.smartshortcuts.com/

Email Permulator

Features:

Really need to email that VP? His email just completely impossible to find? Download the Excel Spreadsheet below, type in his full name and website extension, then download the Rapportive plugin for gmail. After you do that, paste the results you get from the Excel Spreadsheet one by one into any gmail full compose box (but don't send anything), and Rapportive will scan it and tell you if it's a functional address.

Spread Download Link: http://goo.gl/D6v1T
Rapportive Download: http://rapportive.com/

1. What Shortcuts do you use the most?
2. What Add-Ins smoothen it out the work?
3. Handy Spreads to keep on file?
4. Macro Download Links?


Recommended Reading

 

[quote=808]+1. I only use firm-issued macros. VBA is on my to-learn list this summer. My contribution to this thread: http://finance.groups.yahoo.com/group/smf_addin/[/quote]

This. I cannot stress enough how great this addin is. Easily dump everything under the sun into customizeable templates. Nothing beats going to the Q or K as a source, but if you need lots of information in excel for a competitive set and fast, there is nothing like this tool. Highly recommend.

 

I do believe it pulls live data, and the only updates for it, are updates in terms of functionality of the add-in. I'm not 100% positive though. Explore their website a bit!

jasper90:
Hi, sorry for being such a noob, but how often do you need to gather data from the fed? And what sort of data do you typically use? I know my summer internship MD mentioned I will be updating spreadsheets with up to date data, and I am just wondering if this add-in would help in some cases.
 
GED or Bust:
kraziazi:
Thanks! To be honest, a very simple shortcut is one that I use a lot: Merge & Center: Alt + h - m - c (letters pressed consecutively).

Much better off with Center across selection.

Agreed. There is almost never a valid reason to Merge Cells. It is not a best practice. Centering across selection is much better as the aesthetic result is identical, but in function it does not disrupt computation or navigation.

xoxo

[quote=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.[/quote]
 

Hey, don't give up. Your version can be better. ExStock sometimes gets confused and delivers me 2010 data instead of 2012. Use the ExStock format and equations to finish it up.

airball:
Gosh dangit..I have been working on building something like Exstock for a long time now (using data queries & macros) and here it is already built. Gonna have to run some tests but looks like I've wasted a lot of time.
 

Obviously i haven't seen what you were working in but i think you have not wasted any time at all. I think you've probably learned a huge amount. Don't be shy, either: make sure people know what did/ can do!

 

Not sure whether these are common knowledge, but I use these on a daily basis and they're great if you hate using the mouse. You'll notice that almost all of these are just the sequences from Excel 2003.

Insert new sheet: Shift+F11 Rename Sheet: Alt - H - O - R Auto adjust column width: Alt - O - C - A Auto Filter: Alt - D - F - F Freeze Pane: Alt - W - F - F Split Window: Alt - W - S

Will post again if I remember some more. Let me know if these are helpful.

Move along, nothing to see here.
 
CAinPE:

Not sure whether these are common knowledge, but I use these on a daily basis and they're great if you hate using the mouse. You'll notice that almost all of these are just the sequences from Excel 2003.

Insert new sheet: Shift+F11
Rename Sheet: Alt - H - O - R
Auto adjust column width: Alt - O - C - A
Auto Filter: Alt - D - F - F
Freeze Pane: Alt - W - F - F
Split Window: Alt - W - S

Will post again if I remember some more. Let me know if these are helpful.

Excel 07:

Font: Alt HFF Font Size:Alt HFS Fill Color: Alt HH (M, for rgb codes) Font Color: Alt HFC Filter: Alt HSF Cond Formatting: Alt HLR Data Validation: Alt AVV Column width: Alt HOW Column autowidth: Alt HOI Delete Column/Row: Alt HDC/HDR Insert Sheet: Alt HIS Copy Formulae in A1 across: Select A1, highlight cells to the right -> F2, control + enter Page Break View: Alt WI Normal Page View: Alt WL

Word 07: Highlight: Alt HI Insert/Delete Rows: Shift F10, I/DR Table width/height: Alt JLW/H or Alt JDW/H (depending on if table or drawing tool) Arrange middle: Alt JLAAC or Alt JDAAC Turn on/off snap to grid: Alt JLAAG, Alt G Align Left/Center/Right: Alt HAL/C/R Justify: Alt HAJ Align top/mid/bot: Alt HAT/M/B See Final Copy, no comments: Alt RTD, 2nd selection

Outlook/mail: Paste w/o formatting: Alt HVS, bottom selection

There are others I will think of later.

 

I use almost all of the above, thanks for the additions! Some shorter versions are below:

Delete Column/Row in Excel: Ctrl -/+

Paste without formatting in Word/Outlook: Menu Key - T (I'm using Office 2010, not sure if this is available for older versions also. Extremely handy tool, though)

Justify: Ctrl - J.

Move along, nothing to see here.
 
michael.scoffield:

Two of my most commonly used shortcuts are ones I added myself to the Quick Access Toolbar --
Format Painter
Paste Values

You can access any shortcut from Quick Access w/ Alt + #.

Also, I do this a lot -- replicate spreadsheet w/ CTRL + drag tab.

awesome username!

xoxo

[quote=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.[/quote]
 

Facilis voluptas modi nemo eum quisquam qui. Consequatur omnis maiores a. Aut sit quisquam velit sit. Aut vel sapiente eligendi voluptas accusantium explicabo. Omnis voluptates corporis necessitatibus rerum ut ut doloremque.

Dolor ad vel corporis minima. Ducimus non blanditiis ea officia laboriosam ullam voluptas blanditiis. Odit accusamus pariatur voluptatibus tenetur commodi veniam incidunt. Similique aut occaecati consectetur et. Quasi quod iure tempore nemo qui aliquam rerum. Voluptatem quam aut est similique et.

Voluptas laboriosam repellendus cupiditate illo eos aspernatur laudantium. Voluptas impedit aut in minus aspernatur et enim. Et praesentium ex quia nesciunt dolores nihil aspernatur. Eos fuga cumque repudiandae corporis. Illo eligendi repudiandae quae velit laboriosam sequi rerum quis.

Aut qui quas earum excepturi ea culpa. Ab aut quod quia nam ea esse et. Omnis quidem similique et non. Ex est quasi quaerat asperiores. Eaque a quibusdam eligendi qui.

 

Aperiam rerum non saepe quo pariatur provident consequatur. Non impedit quia mollitia et atque nihil perspiciatis.

Molestiae est ullam expedita labore. Aliquam quis repellendus rem et nam porro illum omnis. Sequi eligendi error at qui qui accusantium quibusdam.

Commodi maxime est cum consequatur modi voluptas. Sed voluptatem id ducimus quaerat. Et eaque magnam nihil similique veniam. Fugiat nesciunt debitis iste nulla.

 

Corrupti libero perspiciatis ipsam esse expedita consequuntur. Est accusamus enim optio hic reiciendis dolorum est repellendus. Et voluptatum non sunt velit.

Suscipit dolore molestias autem quasi error. Sunt impedit odit et ea debitis architecto error qui. Corrupti repellendus enim cumque quos aliquam perferendis aut non. Quis harum eos enim illo aut.

Quia eos a culpa voluptates. Nam in similique rem unde et qui architecto. Sint fugiat officia ut accusamus. Qui sunt sunt cupiditate ut expedita. Impedit iusto est corporis non ullam doloremque adipisci.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”