Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

locking merged cells

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Locking merged cells

Locking merged cells

ResolvedVersion 365

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

Excel tip:

Hiding a worksheet in Excel

Want to circulate an Excel workbook file but keep certain worksheets hidden from your colleagues' view?

You can do so by bringing up the sheet you wish to hide on your screen; then going to Format - Sheet - Hide.

It will not be immediately obvious that a sheet is hidden from view unless perhaps the sheet are still labelled Sheet 1, Sheet 2 etc.

To display the sheet again, you can go to Format - Sheet - Unhide on any of the other sheets in the workbook. A dialogue box will appear, allowing you to select the hidden sheet/s. Click OK to make the sheet/s reappear again.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.