You try to unmerge cells in Excel, but the option is grayed out or you get an error message. This prevents you from editing your spreadsheet layout. The problem is almost always caused by worksheet protection or a shared workbook setting. This article explains how to remove these restrictions so you can unmerge cells again.
Key Takeaways: Unmerge Cells in Excel
- Review > Unprotect Sheet: Removes the password protection that blocks all formatting changes, including unmerging.
- Review > Share Workbook > Allow changes by more than one user: Uncheck this to stop sharing, which is required before you can unmerge cells.
- Home > Format > Lock Cell: Ensure this is unchecked for the cells you want to unmerge if the sheet is protected with specific permissions.
Why Excel Blocks the Unmerge Cells Command
Excel disables the unmerge cells command to preserve data integrity under specific conditions. The primary reason is worksheet protection. When you protect a sheet, you can choose which actions users can perform. By default, formatting cells is not allowed. Since unmerging is a formatting change, the command becomes unavailable until you unprotect the sheet.
The second common reason is a shared workbook. This legacy feature allows multiple people to edit a file simultaneously. To prevent conflicts, Excel locks certain operations, including merging and unmerging cells. You must stop sharing the workbook to regain full editing control. A less common cause is when cells are part of a locked table or a protected range with specific permissions that exclude formatting.
Steps to Unprotect a Sheet and Unmerge Cells
If the worksheet is protected, you need the password to turn off protection. Follow these steps to unprotect the sheet and then unmerge your cells.
- Go to the Review tab
Open the Excel workbook and select the worksheet where you cannot unmerge cells. Click the Review tab on the ribbon. - Click Unprotect Sheet
In the Protect group, click the Unprotect Sheet button. If a password was set, a dialog box will appear. - Enter the password
Type the correct password into the dialog box and click OK. If you do not know the password, you cannot unprotect the sheet using standard methods. - Select the merged cells
Once the sheet is unprotected, click on the merged cell or range of cells you want to separate. - Click Merge & Center
Go to the Home tab. In the Alignment group, click the drop-down arrow next to Merge & Center. Select Unmerge Cells from the list. The cells will revert to individual cells.
Stop Sharing a Workbook to Enable Unmerge
If your workbook is shared, you must remove this setting before you can unmerge cells. Be aware that this will delete the change history.
- Go to the Review tab
With the problematic workbook open, click the Review tab on the ribbon. - Click Share Workbook
In the Changes group, click the Share Workbook button. A dialog box will open. - Uncheck the sharing option
In the dialog box, on the Editing tab, uncheck the box labeled ‘Allow changes by more than one user at the same time’. Click OK. - Confirm the action
Excel will show a warning that this will remove the workbook from shared use and erase the change history. Click Yes to confirm. - Unmerge the cells
Now you can select your merged cells, go to Home > Merge & Center drop-down, and select Unmerge Cells.
If Standard Methods Do Not Work
Excel Says “The Cell or Chart You Are Trying to Change Is Protected”
This error means the sheet is protected, but with specific permissions. The creator may have allowed certain users to edit ranges. You need the password to unprotect the sheet entirely, or you must be given permission to edit the range containing the merged cells. Contact the file owner.
Unmerge Option is Grayed Out in a Table
Excel Tables have their own structure. You cannot merge or unmerge cells within a formal Table. Convert the Table to a normal range first by clicking anywhere inside it, going to Table Design > Tools > Convert to Range, and clicking Yes. Then you can unmerge the cells.
Workbook is in Read-Only or Final Mode
If the file was marked as Final or is opened as read-only from a network location, you cannot make changes. Save a copy to your local drive by clicking File > Save a Copy. Open the new copy to edit and unmerge cells.
Worksheet Protection vs. Workbook Sharing
| Item | Worksheet Protection | Shared Workbook |
|---|---|---|
| Primary Purpose | Prevent unauthorized changes to cell content and formatting | Allow multiple users to edit the same file simultaneously |
| How to Disable | Review tab > Unprotect Sheet | Review tab > Share Workbook > Uncheck sharing option |
| Effect on Unmerge Cells | Directly blocks the command if formatting is not allowed | Locks the command entirely until sharing is stopped |
| Password Required | Usually yes, if one was set | No |
| Data Loss Risk | None | Change history is permanently deleted |
You can now identify and remove the barriers that prevent unmerging cells in Excel. First, check for sheet protection on the Review tab. If that is not the issue, verify the workbook is not shared. For persistent problems, remember that cells inside an Excel Table must be converted to a range first. Next, explore using the ‘Allow Users to Edit Ranges’ feature for more granular control over protection.