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

In day to day use of Excel, we may have multiple data under a single entry, like the dataset shown below. Therefore we need to merge multiple cells to form a larger cell for better clarity about the data and organization purposes. But, at the same time, we do need to perform one of the most used functions of Excel, Filter. Using a filter in Excel with those merged cells is difficult as filtering requires all the cells to be the same size. And that’s where this article will work as a saviour.

This article not only informs you about the process but will guide you with a demonstration with a real-life illustrative example. So the question of how to filter data in Excel is answered with an explanation of real-life data below.


Download Practice Workbook

Download this practice workbook below.


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

In this article, I am going to use this dataset for demonstrating purposes. In the range of Cells B4:B16, Products Name in merged cells are shown.

What happens in many cases is that we need to filter merged cells in excel. But merging imposes a problem while filtering data. For example, look at the dataset below, We are going to try filtering the following table.

📌 Steps

  • First, select the table and click the Filter icon from 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 are going to see drop-down icons in every header in the table.

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

  • Now, we are going to try to filter out these merged cells. For this, we select the drop-down icon of the Product Name column and a context menu like shown in the figure will appear.
  • From that figure, unselect all the options under Text Filters then select only the motherboard.
  • After checking this, click OK.

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

  • Then, you will notice that only one entry from cell C14 is showing instead of all three entries.

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

  • To resolve these issues, at first copy the product column’s entries to other cells for later use.

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

  • Then we have to unmerge the whole column. For doing that, select the cell range of cells B5:B17, and then from the Home tab, go to Merge & Center.

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

  • After that, all the cells in the Product Name will be unmerged. There will be empty cells in between the rows.
  • Now we have to fill the empty Cells, for this select the range of cells B5:B16. And then from the Home tab go to Find and Select from the Editing After clicking Find and Select,  a new dropdown menu will appear from that menu select. From that menu click Go To Special.

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

  • A small window opens, in which you have to select Blanks, then click on OK.

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

  • After clicking OK, you will see that all of the blank cells in the column Product Name are selected.

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

  • Next, press “=”. Then press up the arrow After that, press Alt + Enter.

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

  • As you press Alt+Enter. All of the blank spaces will then be filled up by the nearest neighbor cell text in an upward direction.

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

  • Next, select the range of cells H5:H16 and click Format Painter.
  • After clicking 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 in the same merged format as before.

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

  • Next, 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, checkboxes only on the Motherboard from Text Filter and then click OK.

  • After clicking OK, you are going to see that, unlike the first time filtering, this time after filtering all 3 of the entries are visible. That means the filtering process is 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

To Sort the merged cells in excel, you have to follow almost the identical process. You have to fill up the void cells with text or data from the neighbor cells. Only after that, you will be able to sort the data or texts.

📌 Steps

  • The table shown below needs to sort, but there is a problem while sorting as text in column Product Name is in merged condition. On the other hand for sorting. all the cells must be of the same size.

  • First, 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

  • Then excel will ask whether it expands selection or not.
  • Select Expand the selection and click Sort.

Sorting Merged Cells in Excel 

  • Then there will be a small window saying that for sorting you need to have cells of the same size.

Sorting Merged Cells in Excel 

  • To resolve these issues, at first copy the product column’s entries to other cells for later use.

Sorting Merged Cells in Excel 

  • Then we have to unmerge the whole column. For doing that, select the cell range B5:B17, and then from the Home tab, go to Merge & Center.

Sorting Merged Cells in Excel 

  • After that, all the cells in the Product Name will be unmerged. There will be empty cells in between the rows.
  • Now we have to fill the empty Cells, for this selection, the range of cells B5:B16 and then from the Home tab go to Find and Select from the Editing After clicking Find and Select, a new dropdown menu will appear. From that menu click Go To Special.

Sorting Merged Cells in Excel 

  • A small window opens, in which you have to select Blanks, then click OK.

  • After clicking OK, you will see that all of the blank cells in the column Product Name are selected.
  • Next, press “=”. Then press up the arrow. After that, press Alt + Enter.

Sorting Merged Cells in Excel 

  • As you press Alt+Enter. All of the blank spaces will then be filled up by the nearest neighbor cell text in the upward direction.

  • After completing the above procedures, then do the sorting process once again. Here select the cells that need sorting.

Sorting Merged Cells in Excel 

  • You will get a notification saying whether you want to expand the selection or not. 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 actually the sorting going to apply on Sort On. Then select the order in which the sort you wanna do, select Smallest to Largest in this case.

Sorting Merged Cells in Excel 

  • After clicking the OK, you will see that all your data is now according to price from low to high order.


Conclusion

To sum it up, the answer to the question “how to filter in excel with merged cell” is discussed in real-life data with an explanation. Here you need to apply format painting on the unmerged cells to make this procedure successful. Another task is to Sort in merged also demonstrated.

For this problem, a practice workbook is available for download where you can practice and get used to this method.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo