How to Create a Personal Macro Workbook in Excel: Your Ultimate Guide
Do you find yourself repeatedly performing the same tasks in Excel? Automating your workflow with VBA macros can save you tons of time! But wouldn’t it be great if you could access your favorite macros across all workbooks? That’s where a Personal Macro Workbook comes in.
In this guide, I’ll walk you through setting up your Personal Macro Workbook so you can store and use macros anytime, in any Excel file.
What is a Personal Macro Workbook?
A Personal Macro Workbook (PERSONAL.XLSB) is a hidden Excel file that stores your macros and loads automatically whenever you open Excel. This means your macros are always accessible, no matter which workbook you’re working on.
How to Create a Personal Macro Workbook
Step 1: Open Excel
Fire up Excel to get started!
Step 2: Save a New Workbook as Binary Format
-
Click on File > Save As
-
Choose Excel Binary Workbook (
*.xlsb) as the file type -
Save it in the following location:
C:\Users[YOUR_USERNAME]\AppData\Roaming\Microsoft\Excel\XLSTART
(Replace
[YOUR_USERNAME]with your actual system username.)
💡 Quick Tip: You can quickly access this path by pressing Win + R, pasting the following, and hitting Enter:
%AppData%\Microsoft\Excel\XLSTART
- Name the file whatever you like, but for convenience, many users name it PERSONAL
- Click Save
Step 3: Hide the Personal Macro Workbook
Now that your workbook is created, you don’t want it popping up every time you open Excel.
- Go to the View tab in Excel
- Click Hide Window
That’s it! Excel will still load the Personal Macro Workbook in the background, making your macros available without cluttering your workspace.
How to Enable the Developer Tab in Excel
Before you can write and use macros, you need to enable the Developer tab.
Step 1: Open Excel Settings
- Click on File > Options
- In the Excel Options window, select Customize Ribbon
- Under Main Tabs, check the box next to Developer
- Click OK
🎉 Now, the Developer tab will appear in the Excel ribbon, giving you quick access to the Visual Basic Editor, Macro Recorder, and other essential VBA tools.
Wrapping Up
Congratulations! 🎉 You’ve successfully created your Personal Macro Workbook, enabling you to store and access macros across all your Excel files effortlessly.
Next Steps
- Start recording macros and saving them to your
Personal Macro Workbook - Explore writing simple VBA code to automate your tasks
- Share your favorite macros with colleagues to boost productivity!