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 savior.
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.
How to Filter in Excel with Merged Cells: Step-by-Step Procedure
In this article, I am going to use this dataset for demonstrating purposes. In the range of Cells B4:B16, the Products Name in merged cells are shown.
What happens in many cases is that we need to filter merged cells in Excel. However, 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 the Sort and Filter group in the Data tab.
- After clicking the Filter icon, you are going to see drop-down icons in every header in the table.
- 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.
- Then, you will notice that only one entry from cell C14 is showing instead of all three entries.
- To resolve these issues, first copy the product column’s entries to other cells for later use.
- Then we have to unmerge the whole column. To do that, select the cell range of cells B5:B17, and then from the Home tab, go to Merge & Center.
- 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.
- A small window opens, in which you have to select Blanks, and then click on 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.
- 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.
- Next, select the range of cells H5:H16 and click Format Painter.
- After clicking Format Painter, select the cells from B5:B16.
- It will turn all the cells into the same merged format as before.
- Next, click the small drop-down menu on the corner of the Products Name
- 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.
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 an 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 be sorted, 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.
- Then Excel will ask whether it expands the selection or not.
- Select Expand the selection and click Sort.
- Then there will be a small window saying that for sorting you need to have cells of the same size.
- To resolve these issues, at first copy the product column’s entries to other cells for later use.
- Then we have to unmerge the whole column. To do that, select the cell range B5:B17, and then from the Home tab, go to Merge & Center.
- 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.
- A small window opens, in which you have to select Blanks, and 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 the arrow. After that, press Alt + Enter.
- 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.
- 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 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.
- After clicking the OK, you will see that all your data is now according to price from low to high order.
Read More: How to Filter by List in Another Sheet in Excel
Download Practice Workbook
Download this practice workbook below.
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 will be highly appreciated.
<< Go Back to Data | Filter in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
