Workaround for sorting and filtering of merged cells
- / 2013
First step: Unmerging all merged cells
1. Select all cells in the worksheet.
A quick way to do so is to click the triangle at the intersection of the row headers and column headers:
2. On the Home tab, in the Alignment group, click Merge & Center:
When you click this button, all selected cells in the worksheet will be merged.
NOTE: If Merge & Center button isn't highlighted, there are no merged cells.
Second step: Filling the Gaps in a Sheet
3. Select the range that has the gaps.
4. On the Home tab, in the Editing group, choose the Find & Select drop-down list and then click Go To Special...:
5. In the Go To Special dialog box, select the Blanks option and click OK:
This action selects the blank cells in the original selection.
6. On the Formula bar, type an equal sign (=) followed by the address of the first cell with an entry in the column (=B9, in this example) and press Ctrl+Enter:
7. Reselect the original range and press Ctrl+C to copy the selection.
8. On the Home tab, in the Clipboard group, choose the Paste drop-down list and then click Paste Values to convert the formulas to values:
After you complete these steps, the gaps are filled in with the correct information. Now it's a normal list, and you can do whatever you like with it - including sorting, filtering, etc.