Excel Tip: Create a Hidden Workbook to Save All Your Macros, Scripts, and Formulas

If you’re like me, you use MS Excel quite a bit.  I love Excel.  I personally think that it is the greatest piece of software ever created.  In my position with my company I get a chance to use it quite a bit, and often find myself reusing the same bit of VBA script or the same formula for many different projects.  I get really tired of rewriting the same script, recording the same macro, or trying to remember exactly how I built that last formula before I can use it again.  If this ever happens to you, you will understand the frustration.

Well, a couple of years ago I stumbled upon a great tip that has saved me considerable time and frustration, and has even made me look like a hero several times.  The magic lies in a small, somewhat hidden folder within the Microsoft Office installation folder called “XLStart”. 

“What’s so magical about this folder,” you may ask.

Well, that’s what I am about to show you.

First, let’s focus on those often used macros, scripts, and formulas that you have saved in many other Excel workbooks, text files, Post-It Notes, and other divers sources.  Round all of the little nuggets up and save them to one work book.  Make sure that your macros and scripts do not make references to specifically named sheets in the original source.  If they do, I have found that it is best to either change them to the generic “Sheet#” naming that Excel uses by default.  You can also have the script prompt you for the desired sheet name if you like.

Second, try to find a way to organize all of the formulas and macros in a way that you will be able to easily find them.  I find it very useful to put the name of the formula and/or a small explanation of it just above the cell you are saving the formula in.  You should also have some sample data for the formula to use, just so you can remember exactly what it does, just in case you don’t use it for a long time.

Next, save the workbook you have just populated to the “XLStart” folder.  If you used the default installation, the folder should be located using the following path:
C:\Program Files\Microsoft Office\OFFICE11\XLSTART
Just remember to name this something beside Book1.  I named mine “MacroBook”, just to make things obvious for me.

The last and my favorite part of this is to hide the workbook so that it doesn’t get in your way every time you open Excel. To do this, just go to the menu bar and click “Window” and the “Hide” and select the workbook you saved.  Now, close Excel.  It will ask if you want to save the changes you made to your workbook.  Click “Yes”. 

Now, every time you open Excel, this new workbook with your great macros and formulas will be opened in the background and ready for use.  To use a macro, simple click on the macro button like you would to run a macro as if you just made it in your current workbook.  The macro selection box will pop up and you will see all of the macros listed for all open workbooks.  They will show up in the format WorkbookName.xls!Macroname.  Simply select it an run it.  To use your formulas, simply go to the toolbar, click “Window” and then “Unhide”, select your hidden workbook and it will appear.  Then, simply copy and paste the formula you would like to use into the workbook you are working on.  When done, simply re-hide the book you had hidden.

This may seem like a long way to go save some simple formulas, but trust me, if you have any complicated formulas or macros that you find yourself re-creating very often, you will quickly come to love this little tip.  So, give it a try and let me know how you like it.