This post will guide you how to filter all related data if column has merged cells in Excel 2007/2010/2013/2016. How do I filter merged cells to show all related cells in Excel. How to filter data on the merged cells in Excel. How to Sort data containing merged cells in Excel 2010/2013.

• Filtering Merged Cells
• Video: Filtering Merged Cells in Excel 2013
• Copy/Paste Merged Cells into Single Cells
• Video: Copy/Paste Merged Cells into Single Cells in Excel 2013
• Sort Data Containing Merged Cells
• Video: Sort Data Containing Merged Cells in Excel 2013

Filtering Merged Cells

---

Assuming that you have a list of data in range B1:C7 which contains merged cells. And you want to filter all related cells from merged cells. You can do the following steps:

#1 select your merged cells in Column B and press Ctrl +C to copy it. And then pasted to other blank cells in another column. And it will keep the original merged cell format.

#2 select merged cells B1:B7 and then go to HOME tab, click Merge & Center command under Alignment group. It will cancel all merged cells from the selected merged cells.

#3 keep the selection of range of cells B1:B7, and go to HOME tab, click Find & Select command under Editing group, and select Go to Special from the popup menu list. And the Go To Special dialog will open.

#4 select Blanks radio button under Select section. And click OK button. All blank cells have been selected in range B1:B7.

#5 Type one formula =B2 and press UP arrow key on your keyboard, and then press CTRL + Enter keys to fill all the selected cells with value above.

 

#6 then you need to restore the default cell formatting for the original merged cells from E2:E7 range to B1:B7.  Select the E2:E7, and then go to HOME tab, click Format Painter command under Clipboard group.

#7 drag the Format Painter to fill from B2:B7 to restore the original merged Cell Format.

#8 select Column B, and go to DATA tab, click Filter command under Sort & Filter group. And one filter icon will be added into the first cell of Column B. and you can click the filter icon to filter merged cells.

Video: Filtering Merged Cells in Excel 2013

Copy/Paste Merged Cells into Single Cells

---

When you copy the merged cells and then pasted it into other cells, the merged cells also will be pasted in the destination cells. And if you want to paste echo merged cells into one single cell. You can do the following steps:

#1 select the merged cells B1:B7, and press Ctrl + C keys in your keyboard.

#2 select one single blank cells and right click on it, select Pasted Special from the popup menu list. And the Paste Special dialog will open.

#3 select Formula and number formats radio button under Paste section, and click OK button.

#4 each merged cells will be pasted into one single cell.

Video: Copy/Paste Merged Cells into Single Cells

 

Sort Data Containing Merged Cells

---

If you try to sort the cells that contain merged cells in the selected range of cells, and you will get a warning message dialog, it will warn you that “to do this, all the merged cells need to be the same size”.

So how to sort the data in selected range of cells that contain merged cells in Excel 2010/2013/2016. Let’s do the following steps:

#1 select the range of cells that contain merged cells that you want to sort it.

#2 go to HOME tab, click Merge & Center command under Alignment group. And all merged selected cells will be canceled.

#3 go to HOME tab, click Find & Select command under Editing group. And select Go To Special menu from the popup menu list. And the Go To Special dialog will open.

#4 select Blanks radio button under Select section. And click OK button. All blank cells have been selected in range B1:B7.

#5 Type one formula =B2 in the formula box and press UP arrow key on your keyboard, and then press CTRL + Enter keys to fill all the selected cells with the value of the first blank cell above.

#6 select the range B1:B7, and go to DATA tab, click Sort A to Z command under Sort & Filter group to sort the selected cells.

#7 after the cells were sorted, and you can merge the same cells again.

Video: Sort Data Containing Merged Cells in Excel 2013