How to Change the Default Location of personal.xlsb in Excel

How to Change the Default Location of Personal.xlsb in Excel

Personal.xlsb is a hidden workbook in Microsoft Excel that can store macros and commands used in multiple workbooks. By default, this workbook is saved in the XLSTART folder of the user’s profile. However, it may be necessary to change the default location of the Personal.xlsb workbook based on user preferences or organizational policies. In this article, we explore step-by-step instructions on how to change the default location of Personal.xlsb in Excel.

Step 1: Locate Personal.xlsb Workbook

To change the default location of Personal.xlsb in Excel, you need to first locate the workbook. Follow these steps:

1. Open Excel and go to the File menu.
2. Click on ‘Options’ and then select ‘Add-ins.’
3. In the ‘Manage’ box, select ‘Excel Add-ins’ and then click on the ‘Go’ button.
4. In the ‘Add-ins’ box, uncheck ‘Analysis ToolPak’ and click on ‘OK.’
5. This will display the Developer tab on the Ribbon menu. Click on the Developer tab and then click on ‘Visual Basic’ to open the Visual Basic Editor.
6. In the Project Explorer window, you will see a workbook named ‘Personal.xlsb.’ Right-click on it and select ‘Properties.’

Step 2: Change Default Location

Once you have located the Personal.xlsb workbook, you can change the default location by following these steps:

1. In the ‘Properties’ window, you will see a field named ‘Full Name.’ Copy this path to the clipboard.
2. Close Excel and open Windows Explorer.
3. Navigate to the folder where you want to save the Personal.xlsb workbook.
4. Right-click on an empty space in the folder and select ‘New’ and then ‘Text Document.’
5. Rename the text document as ‘Personal.xlsb’ (without quotes).
6. Double-click on the newly created workbook to open it in Excel.
7. Go to the Developer tab and click on ‘Visual Basic’ to open the Visual Basic Editor.
8. In the Project Explorer window, you will see a workbook named ‘This Workbook.’ Right-click on it and select ‘Import File.’
9. In the ‘Import File’ dialog box, navigate to the location where you saved the original Personal.xlsb workbook and select it.
10. Click on ‘Open’ to import the file into the new workbook.
11. Close the Visual Basic Editor and save the workbook.

Step 3: Set New Default Location

To ensure that Excel always uses the newly created Personal.xlsb workbook, follow these steps:

1. Open Excel and go to the File menu.
2. Click on ‘Options’ and then select ‘Add-ins.’
3. In the ‘Manage’ box, select ‘Excel Add-ins’ and then click on the ‘Go’ button.
4. In the ‘Add-ins’ box, check ‘Analysis ToolPak’ and click on ‘OK.’
5. Go to the Developer tab and click on ‘Visual Basic’ to open the Visual Basic Editor.
6. In the Project Explorer window, you will see a workbook named ‘Personal.xlsb.’ Right-click on it and select ‘Remove Personal.xlsb.’
7. Close the Visual Basic Editor and save the workbook.

Conclusion

Changing the default location of Personal.xlsb in Excel can be done easily by following the steps outlined in this article. These steps involve locating the workbook, creating a new workbook in the desired location, importing the original workbook into the new workbook, and setting the new workbook as the default location. By following these steps, users can customize their Excel environment to suit their needs and preferences.