What is a Personal Workbook?A personal workbook is a file that:
- Is a hidden file (or at least it should be…).
- Opens automatically when Excel opens.
- Remains active while Excel is open.
- Is titled Personal.xlsb.
- Allows you to store things for future use (typically Macros).
- Is your “bag o’ tricks”
How is the Personal Workbook used?Typically, the Personal Workbook stores macros that you’ll use on any workbook or worksheet. Usually when you create a macro, the macro is tied to that specific workbook. However, there are times when you need to create a macro that would apply to any of the workbooks you create. Instead of copying and pasting that macro in to every workbook you create (tedious, repetitive, error-prone, inefficient…), create the macro once and store it in your Personal Workbook (tidy, one-off, efficient…). Some examples of macros that you might want to use in various workbooks might include, but are not even remotely limited to:
- Print Headers and/or Footers
- Special Formatting
- Sorting Worksheets
- Toggling between A1 and R1C1 Reference Style
- Anything else you can imagine…
Finding Your Personal WorkbookAmong others, there are two ways to determine if you have a Personal Workbook:
- You can look for it using the Visual Basic Editor.
- You can look for it via View/Unhide
Finding the Personal Workbook via View/UnhideTo find your personal workbook using the Visual Basic Editor, you’ll need to do the following (in this order):
- Select the “Developer” tab from the ribbon.
- Select the icon labeled “View Code”.
Finding the Personal Workbook via View/UnhideTo find the personal workbook via View/Unhide, you’ll need to select the “View” tab from the ribbon then select the icon labeled “Unhide”. This will open a dialogue box that lists the worksheets that are hidden within the workbook. If you see “Personal.XLSB” listed, you’re ready to go! Press the Cancel button to continue.
Creating Your Personal WorkbookIf you don’t have a Personal Workbook, you can create one by recording a simple macro. To record a Macro to your Personal Workbook, click on the Macro Recorder button in the lower left-hand side of your screen.
The Record Macro dialogue box will appear. Because this macro’s only purpose is to create a Personal Workbook, the name is meaningless. I’d leave the name as Macro1.VERY IMPORTANT: Be sure to store the macro in the Personal Workbook. This is very important because Excel will be forced to create the Personal Workbook just to be able to store the macro you’re about to record.
At this point, you can perform just about any keystroke/ribbon command on any cell and the macro recorder will write that activity in a module in the Personal Workbook. You could, for example, type “1234” in cell A1 then hit enter.
Then click on the Macro Record Button on the lower left-side of the screen to stop recoding the macro.
Then, close Excel. A dialogue box will appear asking if you want to save the file, presumably Book1, and you can select “Don’t Save”. This file is not needed as it’s sole purpose was to be the medium for recording the macro to be written to the Personal Workbook.
Another dialogue box will appear asking if you want to save the Personal.xlsb file and you should select ” Save”. If you don’t select Save, the Personal Workbook will not be saved and you’ll have to re-try to create your Personal Workbook.At this point, you can begin loading your Personal Workbook with the macros that you want to use on any open workbook (after re-opening Excel and unhiding your Personal Workbook). As a “Saving Lives With Excel” tip, after creating macros in my Personal Workbook, I load them on to my Quick Access Toolbar (QAT) so that they’ll be available and visible while Excel is open. Click here to go to the “Tips and Tricks” page and instructions for using the Quick Access Toolbar (QAT) and how to assign the macros in your Personal Workbook to icons in your QAT. Click here to download the PDF instructions for creating your Personal Workbook >>