Alan has attended:
Excel Intermediate course
Locking merged cells
Hi, when trying to lock some cells, an error message came up saying that merged cells couldn't be locked. I can't see which cells are merged, however. Is there any way to get around this issue?
Many thanks,
Alan
RE: Locking merged cells
Hi Alan,
Thank you for using the forum to ask a question.
Merged cells are good for formatting but poor for functionality as you have experienced.
How to find a Merged cell in an Excel Worksheet
1. Select all cells on the sheet (Ctrl A will help)
2. On the Ribbon, under the Home tab use "Find & Select" the magnifying glass and select "Find"
3. You are looking for the Formatting so under the "Find what" click on the Grey Format box
4. Go to the "Alignment" tab and tick the box "Merge Cells" and choose OK
5. Now you can "Find Next" and it will jump you to the Merged Cells in the document, you will need to unmerge and resolve manually.
Kind regards
Richard
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Locking merged cells
Thanks, I was trying this today but for ordering columns of cells by date order. I managed to locate the merged cells and unmerge them but when I did this, it affected the column headings and when I date ordered the cells, it put the column headings to the bottom of the rows, which is obviously not what I want to happen. Is there any way to unmerge the cells without it affecting the column headings?
Many thanks,
Alan
RE: Locking merged cells
Hi Alan,
I'm glad you found the merged cells.
There isn't a way to resolve this automatically, it's a slow and manual process. Occasionally it messes up the column headings as you mentioned.
Part of the "Best Practice use of Excel" we refer to in our training courses is that merge cells should never be used in tables/flat lists of data as they always cause problems
Lookup formulas, Pivots, etc all struggle with merged cells
I hope you manage to resolve this and share with others in your team the reason to avoid merge cells at all costs (only exception is for specific formatting but that should be rare)
Kind regards
Richard
