Personal Macro Workbook: Build Your Own

Updated 14 November 2026 · Standard formula, shown below

A personal macro workbook is a single Excel file (named Personal.xlsb by default) that stores macros, custom functions, and often custom ribbon buttons so they are available in every workbook you open on your computer. You create it once, save it in a special startup folder, and Excel loads it automatically each time the program starts. This article explains what it is, how to build one, what to put in it, and how to avoid the common pitfalls that trip up most users.

What the Personal Macro Workbook Actually Does

When you record a macro in Excel, the default destination is usually This Workbook, meaning the macro only exists inside that file. The Personal Macro Workbook is a hidden, always-open file that acts as a global library. Any macro stored there is available from any workbook, on any worksheet, at any time. It is stored as a binary file (Personal.xlsb) rather than the standard .xlsx format because it can contain VBA code, which a normal workbook cannot.

Excel creates this file automatically the first time you record a macro and choose Personal Macro Workbook as the destination. If you have never recorded a macro, the file does not exist yet. You can also create it manually by saving a new workbook as an Excel Macro-Enabled Workbook (.xlsb) and naming it Personal, but the automatic method is simpler and less error-prone.

How to Create and Save It

Open Excel and go to the View tab (or Developer tab if you have it enabled).
Click Macros > Record Macro.
In the dialog, give the macro a name (no spaces, start with a letter), and in the Store macro in dropdown, select Personal Macro Workbook.
Perform the actions you want to record, then click Stop Recording.

Excel then creates the Personal.xlsb file in your startup folder. On Windows, that folder is usually %APPDATA%\Microsoft\Excel\XLSTART. On a Mac, it is ~/Library/Group Containers/UBF8T346G9.Office/User Content/Startup/Excel. The file is hidden by default, so you won’t see it in the workbook list, but it is loaded every time Excel starts.

To view or edit the macros inside it, you need to unhide the workbook. Go to the View tab, click Unhide, select PERSONAL.XLSB, and confirm. Then press Alt+F11 to open the VBA editor and see the modules. Remember to hide it again when you are done, otherwise it will remain visible in your taskbar.

What to Store in It (and What Not to)

The Personal Macro Workbook is best for small, general-purpose macros that you use across many files. Common examples include:

Formatting shortcuts (e.g., apply a specific border style or number format)
Quick data-cleaning routines (remove duplicates, trim spaces, convert text to proper case)
Custom functions (UDFs) that you want available in any workbook, like a currency converter or a date-difference calculator
Automated report generation that pulls data from the active sheet

Do not store macros that are specific to one project, contain sensitive data, or depend on a particular file path. Those belong in the workbook they are meant for. Also avoid storing large amounts of data or formatting in the Personal workbook itself—it is meant for code, not content.

How to Add Macros Without Recording

Recording is fine for simple tasks, but for anything more complex, you will want to write VBA code directly. Open the VBA editor (Alt+F11), find PERSONAL.XLSB in the Project Explorer, right-click it, and choose Insert > Module. Then paste or type your code. A simple example:

Sub FixDates()
    Selection.NumberFormat = "yyyy-mm-dd"
End Sub

This macro changes the number format of the selected cells to an ISO date format. To use it, select a range, press Alt+F8, choose FixDates, and run it.

You can also assign macros to buttons or keyboard shortcuts. To add a keyboard shortcut, go to the Macros dialog, select the macro, click Options, and enter a letter. Be careful not to overwrite existing shortcuts like Ctrl+C (copy) or Ctrl+V (paste).

Common Problems and How to Fix Them

The most frequent issue is that the Personal Macro Workbook does not load. This usually happens because the file is missing from the XLSTART folder, or because Excel’s trust center settings block macros. To check, go to File > Options > Trust Center > Trust Center Settings > Macro Settings. If “Disable all macros without notification” is selected, change it to “Disable VBA macros with notification” or “Enable all macros” (the latter is less secure).

Another common problem is that the Personal workbook is hidden and you cannot find it. Use the Unhide button on the View tab. If that button is greyed out, the file is not loaded—check the XLSTART folder for a file named Personal.xlsb. If it is missing, re-record a macro and choose the Personal Macro Workbook destination again.

If you get a “macro cannot be found” error, the macro might be in a different module or the name might be misspelled. Open the VBA editor and look under PERSONAL.XLSB > Modules. You can also use the Immediate Window (press Ctrl+G) and type ?Application.StartupPath to see the exact folder Excel is using.

Comparing Personal Workbook vs. Add-Ins

An Excel Add-In (.xlam) is a more advanced way to distribute macros. Unlike the Personal Macro Workbook, an add-in can be loaded or unloaded per session, can be shared with other users, and can include custom ribbon tabs. The Personal workbook is simpler and always loads, but it is tied to your user profile and cannot be easily shared. If you need to give a macro to a colleague, an add-in is the better choice. If you just want your own macros available everywhere, the Personal workbook is fine.

Another difference: the Personal workbook is hidden, but an add-in is also hidden by default. The main advantage of an add-in is that it can be installed via the Add-Ins dialog, and you can toggle it on or off. The Personal workbook is always on—you cannot disable it without moving the file out of the startup folder.

Security and Best Practices

Because the Personal Macro Workbook runs automatically on startup, it is a prime target for malware. Only put code you have written or that you fully trust. Never copy a macro from an unknown source into your Personal workbook. Also, keep a backup copy of the file somewhere safe, because if Excel crashes or you reinstall the system, the Personal workbook can be lost.

Always test a new macro on a copy of your data first. A small error can delete data or format cells incorrectly. Use Application.Undo where possible, but note that not all VBA actions are undoable. Also, be aware that the Personal workbook is not encrypted by default—anyone with access to your computer can open it and read your code. If you have sensitive macros, consider protecting the VBA project with a password (though this is not strong protection).

Frequently Asked Questions

How do I delete a macro from my Personal Macro Workbook?

Open the VBA editor (Alt+F11), find the module containing the macro, and delete the entire Sub...End Sub block. If you want to remove all macros, you can delete the whole module by right-clicking it and choosing Remove Module. The Personal workbook itself remains, just empty.

Will my Personal Macro Workbook work on another computer?

No, not automatically. The file is stored locally on your machine. To use it elsewhere, copy the Personal.xlsb file to the same XLSTART folder on the other computer. You must have permission to write to that folder, and macros must be enabled there.

Can I store custom functions (UDFs) in the Personal Macro Workbook?

Yes. Any function you write in a standard module in the Personal workbook becomes available in all workbooks, just like built-in functions. For example, if you define Function Square(x) : Square = x^2 : End Function, you can type =Square(5) in any cell and get 25.

Why is my Personal Macro Workbook not loading on startup?

Check that the file exists in the correct startup folder. Use the Immediate Window in the VBA editor to type ?Application.StartupPath and press Enter—this shows the exact folder. If the file is there, check your macro security settings. Also, if you have Excel open in multiple instances, only the first instance loads the Personal workbook.

Can I rename the Personal Macro Workbook?

You can rename the file, but then Excel will not recognize it as the Personal Macro Workbook. If you rename it, it becomes a regular workbook that opens normally, and your macros will not be automatically available. It is best to keep the name Personal.xlsb.

Is the Personal Macro Workbook the same as a normal hidden workbook?

It is a hidden workbook, but it is special because Excel loads it automatically from the XLSTART folder. You can hide any workbook manually, but only the one in the startup folder is loaded at startup. The Personal workbook is also stored in a binary format (.xlsb) to support VBA code.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.