Understanding the Personal Workbook Filename

by Allen Wyatt
(last updated December 12, 2020)

1

Shauna understands that she can store macros in her personal workbook so they are accessible anytime she's using Excel. In doing some research, it is unclear to her exactly how this workbook is saved. She has seen references to both Personal.xlsm and Personal.xlsb, so she wonders what the actual name is for the personal workbook and what to do if she finds both the XLSM and XLSB versions on her system.

The personal workbook is, most often, referred to as the "personal macro workbook" because it is used to store macros that you want available at all times in Excel. Macros are only available to use if they are stored in a workbook that is currently open. Thus, if you store a macro in your personal macro workbook, it will always be available because the workbook is always opened, automatically, whenever you start Excel.

This may seem confusing, as you don't normally see the personal macro workbook when you start Excel. That is because it is hidden, by default, so that it doesn't interfere with whatever work you want to do in your other workbooks. The fact that it is hidden is, in fact, the key to discovering if you have a personal macro workbook available on your system. Follow these steps:

  1. Start Excel, if it is not already started.
  2. Display the View tab of the ribbon.
  3. Take a look at the Window group. If the Unhide tool is "grayed out" (not accessible), then you don't have a personal macro workbook on the system. (See Figure 1.)
  4. Figure 1. The Unhide tool is not accessible, so there is no personal macro workbook on this system.

  5. If the Unhide tool is available, click it. Excel displays the Unhide dialog box. (See Figure 2.)
  6. Figure 2. The Unhide dialog box.

  7. If you see the PERSONAL.XLSB file in the dialog box, then the personal macro workbook is available on your system.
  8. Click Cancel to dismiss the Unhide dialog box.

As you may have gathered from the above instructions, it is entirely possible that you don't have a personal macro workbook on your system. This is normal; it is only created if you record a macro (or write one from scratch) and specifically tell Excel to store the macro in the Personal Macro Workbook. (See Figure 3.)

Figure 3. Specifying where to save a macro.

On all modern Excel versions (Excel 2007 and later), when you create a personal macro workbook, Excel does so using the XLSB format. Where the personal macro workbook is stored depends on the version of Excel you are using. The short story is that it is stored in the XLStart folder. (Anything in this folder is automatically loaded when you start Excel, thus the reasoning of putting the personal macro workbook there.) For most of the more modern versions of Excel, the path to the XLStart folder is this:

C:\Users\"your name"\AppData\Roaming\Microsoft\Excel\XLSTART\

In this path, "your name" is a placeholder for your actual username. Thus, if my username on the system is JDoe, then this would be the path:

C:\Users\JDoe\AppData\Roaming\Microsoft\Excel\XLSTART\

You should note that the AppData folder is hidden, by default. This means that in order to access the folder, you'll need to enable the display of hidden files on your Windows system. You can do that by following this tip, over on the WindowsTips site:

https://windows.tips.net/T011964

If you look in the folder using File Explorer, you'll see the PERSONAL.XLSB workbook, provided one exists on your system. If you see other workbooks in the folder, those workbooks are opened, automatically, when Excel starts (as already mentioned). This leads to an interesting situation. If you see both PERSONAL.XLSB and PERSONAL.XLSM workbooks in the folder, then Excel, when starting, treats PERSONAL.XLSB as your personal macro workbook and opens PERSONAL.XLSM as a regular workbook. Even if you delete PERSONAL.XLSB (so that only PERSONAL.XLSM is in XLStart), Excel will open PERSONAL.XLSM as a regular workbook and consider that you don't have a personal macro workbook available.

The only way that you would see a PERSONAL.XLSM formatted workbook in the XLStart folder is if someone specifically saved a workbook using that name and moved it to the folder.

Tellingly, Excel balks at saving a personal macro workbook in the XLSM format. Assuming you have a personal macro workbook on your system, follow these steps to see what I mean:

  1. Start Excel, if it is not already started.
  2. Display the View tab of the ribbon.
  3. Click the Unhide tool. Excel displays the Unhide dialog box.
  4. Click on the PERSONAL.XLSB workbook to select it. (It may be selected by default.)
  5. Click OK. The Unhide dialog box disappears and the PERSONAL.XLSB workbook is visible.
  6. Press F12. Excel displays the Save As dialog box.
  7. Using the Save As Type drop-down list, choose Excel Macro-Enabled Workbook (*.xlsm).
  8. Click Save. Excel saves the workbook in the XLStart folder.
  9. With the PERSONAL.XLSM workbook still active, display the View tab of the ribbon and click the Hide tool. Excel hides the PERSONAL.XLSM workbook.
  10. Start to exit Excel.

At this point, you'll see a familiar question: "Do you want to save the changes in the Personal Macro Workbook?" (You've no doubt seen this question before upon exiting Excel. It appears any time you make changes that affect the personal macro workbook.) If you click the Save button, you'll see a notice that you may have never seen before. (See Figure 4.)

Figure 4. You can't save the personal macro workbook in this format.

This notice is displayed because you are trying to save, for real, the personal macro workbook in the XLSM format, and Excel doesn't like that; it expects it to be in the XLSB format. Click OK a few more times, and you'll eventually get to a new notice. (See Figure 5.)

Figure 5. A new idea for saving in the XLSM format.

If you do, indeed, save the personal macro workbook in a different folder, exit Excel, move it to the XLStart folder, delete PERSONAL.XLSB (so that PERSONAL.XLSM is the only file there), and restart Excel, then (and only then) will you be able to use PERSONAL.XLSM as your personal macro workbook.

Why would you go through all these steps? There is no reason, really. Excel wants to use PERSONAL.XLSB, and it will use PERSONAL.XLSM only if it absolutely has to. Plus, if you go through all the steps so that PERSONAL.XLSM is the only workbook in the XLStart folder, then when you start Excel, the program starts as an "empty shell," without any workbook loaded. It does this because it is actually loading PERSONAL.XLSM, but since it is hidden (as any good personal macro workbook should be), it doesn't display.

The bottom line, to answer Shauna's question, is that the only real personal macro workbook is PERSONAL.XLSB. If you do have a PERSONAL.XLSM on your system, it is there as a fluke or someone went to a lot of trouble to create it. You should move it somewhere else, out of the XLStart folder, and restart Excel. Everything should work normally, and you'll be able to use Excel as you expect, using the files that Excel expects.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13810) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Squaring Table Cells

Inserting a table is fast and easy in Word. You may want to make sure that the cells in the table are as square as ...

Discover More

Putting Bold Words in an Index

There are several ways you can create an index in Word, but the first step is always to figure out what should go in the ...

Discover More

Converting Strings to Numbers

When working with data in a macro, there are two broad categories you can manipulate: numbers and text. Sometimes you ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Determining an Integer Value

When creating macros, you often need to process numbers in various ways. VBA allows you to convert a numeric value to an ...

Discover More

Putting an X in a Clicked Cell

Need to click on a cell and have it replaced with an "X"? Macros make it easy to do, as illustrated in this tip.

Discover More

Swapping Two Numbers

When programming macros, variables are used extensively. At some point you might want to exchange the values held in two ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 0?

2020-12-13 18:26:05

Bob Williams

A note about the Workbook_Open routine, which you can put in the ThisWorkbook module of the Personal Macro Workbook (PMW), might be helpful. My effort to do so showed that no code could go in there that assumes that the workbook you are trying to open is already open! E.g., no Named Ranges or built-in variables like ActiveCell or ActiveWindow can be used. (In fact, I'm not sure what useful thing one could do here.)
Also, a comparison of the PMW with a template file would be in order. I frequently fret over where to store often-used bits of code and a template file works for that. (You must have solved such organizational conundrums long since!)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.