Excel: Tools, Add-Ins, and Macros

Lambie's picture
Rank: Gorilla | banana points 544

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

Hi Anonymous Monkey, upload your resume and land a job

Members that upload a resume get 2.3x the number of interview invites through the Talent Oasis. Learn more.

Comments (49)

Apr 22, 2013

+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/

    • 1
Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Apr 22, 2013

Ah, Thank you!

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/[/...

Apr 25, 2013
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/[/...

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.

Best Response
Apr 22, 2013

Federal Reserve Economic Database add-in: http://research.stlouisfed.org/fred-addin/

    • 3
Apr 23, 2013

Thank you! I see your a VC? Interesting, don't see many of you guys around here.

Edit: Wow! I just opened this baby up and so far this is the best add-in so far. Definitely useful.

mb666:

Federal Reserve Economic Database add-in: http://research.stlouisfed.org/fred-addin/[/quote]

Apr 24, 2013
mb666:

Federal Reserve Economic Database add-in: http://research.stlouisfed.org/fred-addin/[/quote]

Thanks brother, really useful.

"Do you like Huey Lewis and the News?"

Apr 24, 2013

Looks like you've helped everyone out! +1 SB

mb666:

Federal Reserve Economic Database add-in: http://research.stlouisfed.org/fred-addin/[/quote]

Apr 23, 2013

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.

Apr 23, 2013

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.

Apr 23, 2013

btw, +1 for these links! I will definitely be trying out that email permulator. I know there are email checker sites, but this is a very convenient tool

Apr 23, 2013

Thank you, and your welcome!

Here's a link to a guide on how to effectively use the excel tool: http://www.distilled.net/blog/miscellaneous/find-a...

jasper90:

btw, +1 for these links! I will definitely be trying out that email permulator. I know there are email checker sites, but this is a very convenient tool

    • 1
Apr 24, 2013

Thanks! To be honest, a very simple shortcut is one that I use a lot: Merge & Center: Alt + h - m - c (letters pressed consecutively).

Apr 24, 2013
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.

    • 3
Apr 24, 2013
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. Don't merge cells.

Apr 24, 2013
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

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
    • 1
Apr 24, 2013
Louboutins and Leverage:
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

One of the first things I did with my new computer and excel was create a macro for center across selections so that I do not have to merge (alt h m c)

Apr 25, 2013
hockey1316:
Louboutins and Leverage:
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

One of the first things I did with my new computer and excel was create a macro for center across selections so that I do not have to merge (alt h m c)

Can't really think of a a situation where you have to merge... Why were you merging to begin with? That's my point... As far as a macro it isn't really that much of a time save... Ctrl + 1 + A + CC + Enter is not that difficult to do... also how often are you doing it? If you do it more than once just use F4... Not really "macro worthy."

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
Louboutins and Leverage:
hockey1316:
Louboutins and Leverage:
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

One of the first things I did with my new computer and excel was create a macro for center across selections so that I do not have to merge (alt h m c)

Can't really think of a a situation where you have to merge... Why were you merging to begin with? That's my point... As far as a macro it isn't really that much of a time save... Ctrl + 1 + A + CC + Enter is not that difficult to do... also how often are you doing it? If you do it more than once just use F4... Not really "macro worthy."

xoxo

Use it alot b/c im consulting and my boss is in love with merge/center and I hate it so this is our compromise.

Apr 25, 2013
hockey1316:

Use it alot b/c im consulting and my boss is in love with merge/center and I hate it so this is our compromise.

Your boss is wrong about merge/center...

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
Louboutins and Leverage:
hockey1316:

Use it alot b/c im consulting and my boss is in love with merge/center and I hate it so this is our compromise.

Your boss is wrong about merge/center...

xoxo

Merge/center is uncompromising, if you delete cells etc you have to deal with the mess of unmerged cells, etc.

Center across selection is by far the more flexible option.

Learn More

7,548 questions across 469 investment banks. The WSO Investment Banking Interview Prep Course has everything you'll ever need to start your career on Wall Street. Technical, Behavioral and Networking Courses + 2 Bonus Modules. Learn more.

Apr 24, 2013

Lambie and mb666. You are my heroes.

Apr 24, 2013

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.

Apr 24, 2013

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.

May 8, 2013

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!

Apr 24, 2013

What is the advantage of using SmartShortcuts over something like Boost or Macabacus?

Apr 24, 2013

If you are lucky enough to have your own Bloomberg Terminal, get really good at using their Excel functions. It's defnitely a useful skill to have.

Apr 24, 2013

Quality stuff Lambie and mb666. Thanks guys!

Make opportunities. Not excuses.

    • 1
Apr 24, 2013

Very useful!! Thanks Monkeys!!

The dragon dozes off in the spirit which is its dwelling.

Apr 24, 2013

Thanks. That's really useful!

Apr 24, 2013

useful

Apr 25, 2013

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.

Apr 26, 2013
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.

Apr 26, 2013

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.

Apr 25, 2013
Apr 26, 2013

most of the macros i use are already there and it's just pressing a button

add ins are a bit more: Thomson Reuters, Bloomberg, Momentus, Data Stream and a few more i cant remember

I eat success for breakfast...with skim milk

Apr 27, 2013

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.

Apr 29, 2013
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

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 29, 2013

Thank you, very useful

My finance blog: AdviceAboutFinance.com

Twitter @samleefinance

Apr 29, 2013

There is an extensive Excle shortcut document (perfect for a print-out in your cube) that we give away free to any new WSO users (or if you are old user, it is part of our 6 free financial modeling training you can sign up for here: http://www.wallstreetoasis.com/forums/special-anno...)

Apr 29, 2013

excel vba is so popular among traders coz its extremely convenient. so, learn it if u wanna do trading.

Apr 29, 2013
Apr 30, 2013

Thanks it will be really useful!

May 3, 2013

subscribed

May 27, 2013

know of any FREE add ins or programs that can verify a list of emails are real from an excel workbook?
I checked out some program called advanced email verifier, but I couldnt get it to work

Apr 8, 2016

Google "Manville Link Finder" I found this to be useful for finding links in your workbooks that aren't in formulas or name manger. For example if they're in conditional formatting.

May 25, 2017

Hey, I do all my excel compare, merge, update and other with a tool called Synkronizer. It's awesome!

May 25, 2017
May 25, 2017
Oct 1, 2017