Mark McClelland

Mark McClelland

Shared notes of a writer and software developer.
Mark is the author of Upload, a near-future sci-fi novel about the first person to upload his consciousness to a computer. Connect on Mastodon.

How To Fix: File in Use - Personal.xlsb is locked for editing

The Problem

Excel often shows the "Personal.xlsb is locked for editing" dialog when the application launches -- even though you don't have any other workbooks open.

Best Fix (Advanced Users Only): Disable Preview Handler For Excel

I'm including this solution... but this is only recommended for advanced Windows users. You should only use this approach if you are comfortable with the Windows Registry Editor (regedit), and you're okay with completely removing support for Excel files in the File Explorer preview pane. (Don't worry, as long as you're careful and know how to keep track of your data, you can restore it later if you need to, by re-importing the registry key as you originally found it.)

If you follow these steps, you can make it so Windows will no longer attempt to preview Excel files in the File Explorer preview pane:

  1. Open Windows Registry Editor (Win + R -> regedit)
  2. Paste this registry key into the key textbox at the top of the Registry Editor: Computer\HKEY_CLASSES_ROOT\.xlsx\ShellEx\{8895b1c6-b41f-4c1c-a562-0d564250836f}
  3. Right-click on the {8895b1c6-b41f-4c1c-a562-0d564250836f} key and export it to a file where you can save it in case you want to later restore the ability to preview Excel files in File Explorer's preview pane
  4. Once you've completed step 3 and saved the .reg file, right-click the {8895b1c6-b41f-4c1c-a562-0d564250836f} key and choose "Delete"
  5. Confirm that you want to delete this key

Now, test to make sure it worked:
  1. Close any open File Explorer windows (to make sure the registry change takes effect)
  2. Open a folder that contains Excel files, select a .xlsx file, and confirm that it does not appear in the preview pane (it should instead say "No preview available")
  3. Now, open Excel -- and you should no longer see the dreaded "Personal.xlsb is locked for editing" dialog.

How to restore Excel preview handler:
  1. Open Registry Editor (Win + R -> regedit)
  2. Choose File -> Import
  3. Import the file you saved before deleting the preview-handler key

Another Approach: Just Don't Use the Preview Pane

Hide the preview pane in Windows File Explorer, then reboot. If you never use the preview pane, this problem goes away -- at least in my experience.

Thanks to Miguel Dominguez for this recommendation in the comments.

OR: Save ALL Your Workbooks As .xlsm Files

You'll see below why this works, but saving all your workbooks as macro-enabled workbooks (.xlsm files) also makes this problem go away. At the time I'm writing this, macro-enabled workbooks can't be opened by the File Explorer preview pane, so the root issue is never triggered.

Thanks again to Miguel Dominguez for this recommendation.

But Wait, There's Still One More Work-Around! Kill Excel Before You Open Excel!

I'm not a fan of this approach, because if you're not careful you could accidentally lose work in an open Excel workbook. Before you open Excel, make sure you don't have any workbooks open, then kill all excel.exe processes. You can do this with a simple batch command:
taskkill /f /im excel.exe
This will kill any lingering processes, which should release competing file locks.

Thanks to @loubli for mentioning this approach in comments.

Why Is This Happening?

Whenever I come across a problem like this, I find it very frustrating to patch the problem without understanding its source. Thanks to a comment on this blog post, I discovered at least one possible source for this issue: Windows File Explorer's preview pane. When the preview pane loads any spreadsheet*, it launches an Excel process, which loads your personal workbook in the background, to make sure any macros or other resources in your personal workbook are available to the spreadsheet being previewed. I've experimented with leaving my preview pane turned off all the time, and so far I'm not seeing the "Personal.xlsb is locked for editing" message anymore.

* Okay, not any spreadsheet. The preview pane doesn't support loading .xlsm files. The problem here really applies to non-macro-enabled files.

Couldn't Microsoft Fix This?

Yes!

Microsoft might be able to fix this by making it so the preview-pane launches Excel with a special option to load Personal.xlsb in read-only mode. There should be no need for macros in your personal workbook to edit the workbook in order to work; in fact, I'm not sure that's even possible. If you want to help fix this problem at the root, do me a favor: search for a bug report for this problem; if you don't find one, submit a bug report; and then comment below with a link to the bug report, so we can all upvote it.

What About Just Making Personal.xlsb A Read-Only File?

I used to think that making the Personal.xlsb file read-only was fixing the problem. That was before I understood the root cause. From my testing since then, I think it was a coincidence that it seemed to work. In case you want to give it a try, or you came here looking for this content for a different reason, I'm keeping these notes here.

To mark the PERSONAL.XLSB file as read-only:
  1. Copy this path to the directory where Personal.xlsb is typically stored: %appdata%\Microsoft\Excel\XLSTART
  2. Press Windows + R to open the Run dialog, paste the path, and click "OK" to run the command
  3. Right-click "PERSONAL.XLSB" and choose "Properties"
  4. Check the "Read-only" box in the "Attributes" section at the bottom of the "General" tab

If in the future you need to edit a macro in your personal workbook (the Personal.xlsb file), you can always un-check the read-only box.

For those comfortable with the Windows command-line, you can just do the following:
attrib +R %appdata%\Microsoft\Excel\XLSTART\Personal.xlsb

And when you want to edit your personal workbook, you can remove the read-only flag with the -R flag:
attrib -R %appdata%\Microsoft\Excel\XLSTART\Personal.xlsb

If it turns out your Personal.xlsb file is not in the directory mentioned above, or if you want to learn more about how to use this file to store Excel macros that you want to use across multiple workbooks, you can read more on Microsoft's support site.

Run Excel First! (And Leave It Open)

I tried this approach, which was reported in comments to work (thanks, Mike Dennison!), but I ended up getting the "Personal.xlsb is locked for editing" dialog when I attempted to preview a .xlsx file in the File Explorer preview pane. If nothing else is quite right for you, give this a try -- your mileage may vary. Perhaps the timing of when you first open Excel could make this work?

If you're okay with launching Excel when you first start Windows and then leaving it open all the time, the Excel process you started should prevent the Excel file-previewer from attempting to lock the Personal.xlsb file. If you don't mind the "clutter" of having Excel running all the time, this is arguably the best approach, because you don't lose the file preview feature for Excel spreadsheets.

If you like this idea, but you don't want to have to remember to open Excel after restarting your computer, you can put an Excel shortcut in your Windows Startup folder:

  1. Press Win+R to open the Run dialog
  2. Type "shell:startup" and press OK -- this will open your startup folder
  3. Search for Excel from the Windows Start menu
  4. Right-click and choose "Open File Location"
  5. Copy the Excel.lnk shortcut
  6. Paste the shortcut into the Startup folder

The next time you start Windows, Excel will automatically start.

I make my living as a software developer, but my true love is writing. If you found this helpful, you'd like to thank me, and you enjoy reading -- or know someone who does -- buy one of my books!

Published by Mark McClelland on

comments powered by Disqus