Excel’s personal.xls

There’s a special file in Excel called personal.xls which is opened automatically every time Excel opens. This makes it a great place to put your Excel macros as they're then accessible to any open workbook.

Unfortunately, a personal.xls workbook is not created until you actually do it yourself so you may not have one. The simplest way to create one is to record a simple macro because then Excel does it for you.

To do this, open Excel and choose Tools > Macro > Record New Macro and type a macro name. From the Store Macro In dropdown list choose Personal macro workbook and click Ok. Record a step or two—it doesn’t have to be an actual macro but it just has to be something and click the Stop Recording button or choose Tool > Macro > Stop Recording. Once you do this your personal.xls macro workbook will be created - ridiculously simple in fact.

If you're prompted to save the file when you close Excel answer Yes to do so. Excel will save it in a location that ensures it will be opened automatically every time you open Excel. In future, store all macros that you want to be accessible to all workbooks in this file and you won't ever have to load them specially.