Practical Excel skills sharing: implement filtering function after merging cells

In the previous article " Practical Excel skills sharing: It turns out that the "positioning function" is so useful! 》, we learned about the wonderful use of positioning function. Today we will talk about how to implement the filtering function in merged cells, share a copy and paste method to solve this problem, and also share with you a good alternative to merge cells. I believe everyone will benefit a lot from learning it.

In our daily work, we often use merged cells. However, merged cells actually just beautify the table. It will cause problems in our subsequent statistical work. There is a lot of trouble. Today I will provide you with two ideas to solve this problem.

1. Basic operation methods of merging cells

Before explaining the problems that are easy to occur when merging cells, let’s first understand the merging How cells are created.

In the table shown below, if there are several identical departments in the first column, we can merge them.

Select the table area that needs to be merged.

Click the "Merge and Center" button under the "Home" tab.

A dialog box will pop up, as shown below. What this sentence means is that after the cells are merged, only the content and address of the upper left corner cell are left. Other cells behind or below become empty cells. This is equivalent to the fact that after merging here, cells A4 and A5 are both empty. Just click OK.

According to the same method as above, merge the "Personnel" and "President's Office" cells in sequence. We can see the end result.

2. Problems that occurred after merging cells

At the end of the month, the company’s design director came to find the financial To design the department's payroll, we need to filter out the design department from the table.

Select the table area and leave the title bar above the table unselected.

Click the "Filter" button in the "Sort & Filter" group of the "Data" tab.

Click the drop-down menu in the "Department" list and select the "Design" department.

Think about a question, why does the salary of only the top employee in the design department appear in the table at this time? Don’t tell me, it’s because Da Tou works the hardest at work.

In fact, the answer has been written in the previous explanation. Merging cells only retains the content and address of the upper left cell. If you're skeptical about this statement, we can un-merge and see the results.

Select the merged cells.

Click the drop-down menu of the "Merge and Center" button under the "Home" tab and click Cancel Merge.

The results obtained are as follows. At this time, you can see that after canceling the merge, only the contents of the first cell are retained in the previously merged cells. As shown below The other cells are all empty cells.

3. How to solve this problem

Let’s solve this problem together with everyone.

Select merged cells A3-A10.

Press the copy shortcut key "ctrl C", then click on any blank cell, and press the paste shortcut key "ctrl V". This operation is to preserve the format and size of the original merged cells.

Then select the merged cells A3-A10 in the original table.

Click the "Merge and Center" drop-down menu under the "Home" tab and click "Cancel Cell Merge".

You can now see the results after canceling the merge.

Keep A3-A10 in the selected state, press the positioning shortcut key "ctrl G", select the "Positioning Conditions" button in the pop-up dialog box, and in the "Positioning Conditions" dialog box Check "Null Value" in the box. (Because we have already talked about the positioning function in the previous tutorial, I won’t go into details here. The link is here: //m.sbmmt.com/topic/excel/491745.html)

At this point you can see that all null values ​​have been selected, and the results are as follows.

Enter "=A3" directly in the edit bar (A3 represents the first non-empty cell).

Press "ctrl and enter" to fill the formula into all empty cells.

Select the A3-A10 cell range, press "ctrl C" to copy, and then selectively paste it as a value in the original range. The purpose of this operation is to fix the formula result ( We have a special tutorial on pasting selectively before. For those who are not familiar with it, click on the link to take a look: //m.sbmmt.com/topic/excel/491640.html). At this time, when we click on cell A4, we can see that there is no formula in the edit bar.

Select the merged cell range A15-A22 copied and pasted previously.

Click Format Painter under the "Home" tab.

Then swipe directly over A3-A10, and you will see that the A3-A10 cell area has been merged again. The results are as follows.

Analysis, the A3-A10 area still seems to be merged at this time, but in fact it is a false merge at this time (only the format and size are merged styles) , filtering operations can still be performed. The previous steps have described the screening operation method, so I will not go into details here. The results of screening the design department are as follows. In addition, we can also filter out the Human Resources Department and President's Office respectively.

If you don’t believe that this is a false merge, you can try to cancel the merge operation (this is also mentioned in the previous step ), we directly cancel the merge of cells A3-A10 and see the following results.

Friends with good memory will find that there are characters in cells A4, A5, A7, A9, and A10 at this time, and the previous questions (cannot be filtered out The reason why the entire design department appears is because after merging cells, only the first cell has characters.

4. Alternative ways to merge cells

If our tables are merged vertically, we can use the above fake merge method to solve the problem , if our tables are merged horizontally, another method can be used.

Select the header area that needs to be merged.

Press the format cell shortcut key "ctrl 1" to pop up the format cell dialog box, and select "Center Across Columns" in the "Horizontal Alignment" drop-down menu.

You can see at this time that the display style of the title is the same as after merging and centering.

But at this time, each cell in the first row is not merged, and can still be selected individually. For example, below I selected cell C1, but we can see that the edit bar above the table is empty. Because we entered the title in cell A1 earlier, at this time we just let the text display across columns among several cells, so cells B1-G1 are still empty cells.

Another advantage of this is that if I want to add a column of salary items behind the table, insert the column directly after it, and the title will be automatically centered again. Select column H, right-click and select Insert from the drop-down menu.

You can see at this point that the title bar is automatically centered across columns again.

If you use the merge and center method, you must first cancel the merge and then select cells A1-H1 for merge and center.

Here is just a demonstration using the title. Friends, you can give it a try. Horizontal cell merging is replaced by "center across columns". At this time, you can filter the data at will.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: implement filtering function after merging cells. For more information, please follow other related articles on the PHP Chinese website!