Forum Discussion
How can I find merged cells in a large spreadsheet?
The file has around 80,000 cells. When I tried a new sort, it said "To do this, all the merged cells need to be the same size". I use merged cells fairly often, but I can't remember where I did that in this file (it's even possible there are none). So I followed the Help instructions for 'Find merged cells' but at the final 'Find All' step, I got "We can't find what you were looking for. Click Options for more ways to search." Any suggestions/solutions?
8 Replies
You can find merged cells in a large spreadsheet by using the Find feature in Excel. Just press Ctrl + F, click Options, then go to Format → Alignment → Merge cells and click Find All. This will show all merged cells in the sheet. Another simple way is to select the whole sheet and visually check for cells that don’t align properly, because merged cells usually stand out when you select or sort data.
Thanks, mohdadeeb. But what you've described is exactly what I did (ie, the Help instructions for 'Find merged cells') but, at the 'Find All' step, I got the error message shown in my original post. The spreadsheet is too big for me to visually check for merged cells and, as explained in my original post, I can't sort the file. Any other suggestions?
HelloLindsayGraham,
Yes, you can find merged cells using Go To Special.
Select the entire sheet (Ctrl + A twice)
Go to Home > Find & Select > Go To Special
Select Merged Cells and click OKExcel will highlight all merged cells in the sheet.
If that does not work, you can use VBA to identify them:
Sub FindMergedCells()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If c.MergeCells Then c.Interior.Color = vbYellow
End If
Next c
End SubRun the macro and it will highlight all merged cells.
Thank you, Olufemi7. Go To Special brings up a list of 15 possibilities, but Merged Cells is not one of them. I'm using the current version [2603] of Excel in Microsoft 365 -- what version are you using?
'Find merged cells' in Help says Home > Find & Select > Find > Options > Format > Alignment > tick Merge Cells > OK > Find All, but that gives me the error message quoted above. Do you get the same result?
I'd like to try other possibilities before using VBA.
Perhaps this Excel -Sort cannot find merged cells - Microsoft Q&A could help
Thanks, Sergei. However, I've read through that carefully and it does not help. I want to find the merged cells, I do not want to unmerge them (at least, not until I have found them).
In addition, Find works in my case. Ctrl-F -> Format -> Alignment -> Check "Merge Cells"
Ok -> Find All
I'm on
