Locking merged cells

Resolved · Medium Priority · Version 365

Mon 3 Oct 2022, 09:31 Delegate Alan said...

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

Mon 3 Oct 2022, 11:30 Trainer Richard said...

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

Thu 6 Oct 2022, 09:51 Delegate Alan said...

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

Thu 6 Oct 2022, 09:56 Trainer Richard said...

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

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Cycling through Absoulte cell references

If you are working with formulas in excel and need to convert your formula to an absolute formula, instead on manually adding in the $dollar signs you can highlight the specific part of your formula and press the F4 key.

You can cycle through all the absolute options by pressing the button (up to four times)

View all Excel hints and tips