Creating a Personal Workbook to Increase Your Efficiency

Creating a Personal Workbook is definitely something you should have in your Excel “bag of tricks”. It will definitely increase your productivity! Here’s what a personal workbook is and also how you can set one up:

What is a Personal Workbook?

A personal workbook is a file that:

1. Opens automatically when Excel opens.
2. Is hidden (or at least should be…).
3. Is titled Personal.xlsb.
4. Remains active while Excel is open.
5. Allows you to store things for future use (typically Macros).

Finding Your Personal Workbook

Among others, there are two ways to determine if you have a Personal Workbook:

1. You can look for it using the Visual Basic Editor.
2. You can look for it via View/Unhide

Finding the Personal Workbook via View/Unhide

To find your personal workbook using the Visual Basic Editor, you’ll need to do the following (in this order):

1. Select the “Developer” tab from the ribbon.
2. Select the icon labeled “View Code”.

This will open the Visual Basic Editor.

In the VBAProject box, look for a file titled Personal.XLSB in parenthesis.  If you see it there, you’re ready to go!

Finding the Personal Workbook via View/Unhide

To 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 Workbook

If 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.

Download this as a printable PDF here >>