How to Filter in Excel with Merged Cells (Step-by-Step Procedure)

 

We will use a dataset of Product Names in merged cells to demonstrate how you can filter based on them.

Steps

  • Select the table and click the Filter icon from the Sort and Filter group in the Data tab.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • After clicking the Filter icon, you will see drop-down icons in every header in the table.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • We selected the drop-down icon of the Product Name column and selected only the motherboard option, then applied the filter with OK.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • Only one entry from cell C14 is showing instead of all three entries.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • Copy the product column’s entries to other cells for later use.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • Select the cell range of cells B5:B17 and, from the Home tab, go to Merge & Center.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • All the cells in the Product Name will be unmerged. There will be empty cells in between the rows.
  • Select the range of cells B5:B16.
  • From the Home tab, go to Find and Select under Editing.
  • After clicking Find and Select, a new dropdown menu will appear. From that menu, click Go To Special.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • A small window opens. Select Blanks and click on OK.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • All of the blank cells in the column Product Name are selected.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • Press “=”.
  • Press up the arrow. 
  • Press Alt + Enter.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • All of the blank spaces will then be filled up by the value of the nearest neighbor above it.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • Select the range of cells H5:H16 and click Format Painter.
  • Select the cells from B5:B16.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • It will turn all the cells into the same merged format as before.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • Click the small drop-down menu on the corner of the Products Name

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • From the dropdown filter menu, check Motherboard from Text Filter and then click OK.

  • Unlike the first time, all available entries are visible. That means the filtering process was successful.

Step-by-Step Procedure to Filter in Excel with Merged Cells

Read More: How to Filter Data in Excel using Formula


Sort Merged Cells in Excel

Steps

  • The table shown below needs to be sorted, but there is a problem while sorting as text in column Product Name is in merged condition.

  • Select the whole table and click the Sort icon from the Sort and Filter group in the Data tab.

Step-by-Step Procedure to Filter in Excel with Merged Cells

  • Excel will ask whether you want to expand the selection. Select Expand the selection and click Sort.

Sorting Merged Cells in Excel 

  • There will be a small window saying that you need to have cells of the same size.

Sorting Merged Cells in Excel 

  • Copy the product column’s entries to other cells for later use.

Sorting Merged Cells in Excel 

  • Select the cell range B5:B17 then choose Merge & Center.

Sorting Merged Cells in Excel 

  • All the cells in the Product Name will be unmerged. There will be empty cells in between the rows.
  • Select the range of cells B5:B16 and, from the Home tab, go to Find and Select under Editing.
  • Click Go To Special.

Sorting Merged Cells in Excel 

  • A small window opens. Select Blanks and click OK.

  • All of the blank cells in the column Product Name are selected.
  • Press “=”. Press the up arrow.
  • Press Alt + Enter.

Sorting Merged Cells in Excel 

  • All of the blank spaces will be filled up by the nearest neighbor above.

  • Repeat the sorting process.

Sorting Merged Cells in Excel 

  • You will get a notification saying whether you want to expand the selection. Select Expand the selection and click Sort.

  • Another new window appears asking to select the criteria Price in column sort by and in which cell the sorting going to apply on Sort On. Select the order Smallest to Largest in this case.

Sorting Merged Cells in Excel 

  • You will see that the data is properly sorted.

Read More: How to Filter by List in Another Sheet in Excel


Download the Practice Workbook


<< Go Back to Data | Filter in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo