The way to split and merge Batch “merge/unmerge” cells

Usually in Excel when organizing data, we often merge or unmerge the cells of the operation. For example, for visual aesthetics, data integration, etc. need to merge some cells, but the merged cells and data sorting, filtering and other operations bring great inconvenience, so sometimes you need to cancel the merged cells. So how to quickly “merge / unmerge” cells? The following I will operate in Excel 2019 as an example for you to come together.

○Category summary Quickly merge cells

After the data is organized, if you do not have to perform similar sorting, filtering and other operations, in order to make the table looks better visually, the content looks more intuitive, we can use the “Category Summary” method to quickly merge cells. For example, in order to easily see which employees are in each department, now you need to merge the departments in column A (Figure 1).


Figure 1 Merger example

Select the data in A1:B10, click “Data → Category Summary → OK”, in the “selected summary items” under the check box “Department” (that is, grouped by department category), and then Click “OK” to complete the grouping (Figure 2).


Figure 2 Category Summary

Continue to select the data in A2:A13 (i.e., the need to merge cells in the department, excluding A1), press the F5 key to open the “Positioning” window, click “Positioning Conditions”, in the window that opens, click on the “empty value “, and then click “OK” (Figure 3).


Figure 3 Positioning cells

At this point it will be A2: A13 data in all the empty cells selected, click “Start → Center after merging”, you can merge all the empty cells at once (Figure 4).


Figure 4 Merge cells

The next step is to apply the formatting of the merged cells to column B. Continue to select the data in A1:A13, click the right mouse button and select “Copy”, and then select the data in B1:B13, click “Start → Paste → Selective Paste” in turn, in the window that opens, select “Format”, click “OK” to paste the format of the cells in column A to column B, so that column B will automatically complete the merging of cells (Figure 5).


Figure 5 Paste format

see from the figure it also contains excess data cells, operate as above to continue to select B1: B14 in the data, press the F5 key to open the “positioning”, click “positioning conditions”, click on the “formula → number “, that is, only select the line with the number in column B, click “OK”. Then click the right mouse button and select “Delete → Delete the entire line”, the B column contains the number of lines to delete all, and then finally the A column to delete (Figure 6).


Figure 6 The effect after merging

As you can see from the above operation, here we first use the “Category Summary” and “Positioning Conditions” method to achieve automatic classification of data in column A according to the department, and then through the “selective paste / format ” method to automatically apply the merge format to column B to complete the operation. It is different from the conventional manual click “Start→Center after merging” method, the conventional method requires manual selection of cells for merging according to the number of employees in each department, for example, there are 3 people in department A and 50 people in department B, then you need to select A2:A4 and A5:A54 for merging, if there are more departments The operation is not only time-consuming, but also prone to errors, the method described above can be faster and more accurate to achieve the merging operation.

Fire link: If you want to involve the merged cells in the calculation, you can refer to the article “Cell merging data as usual” in Issue 19, 2016 and “How to efficiently handle existing data after merging cells” in Issue 23, 2017 of this publication.

○Use VBA to quickly unmerge cells

If we cancel the above merged cells, we can do it quickly with the help of VBA scripts.

Open the “” link to download the required script file (extraction code: 85kr), then press “Alt+F11” in the current document to open the VBA editing window, click on the following “Insert → Module”, enter the downloaded code (Figure 7).


Figure 7 Setup Code

Code explanation: Here first set a cell variable, then iterate through all the cells in the current worksheet to check, and unmerge if a merged cell is found.

return to the Excel window, click “Development Tools ¡ú Macro”, in the window that opens you can see a new macro called “unmerge cells”, click “Execute”. that can cancel all the merged cells in the worksheet, and will automatically fill the data before the merger (Figure 8).


Figure 8 Running macros

We can save it as “Excel macro-enabled workbook” format, so that when we sort, filter and other operations, if Excel prompts a merged cell can not complete the operation, then just copy and paste it into the document, run the above macro can quickly cancel the merger.

As mentioned above, merging cells will bring inconvenience to the subsequent sorting, referencing and other operations. In practice, if you really need to retain the merged cells, then we can also achieve the “pseudo-merge” through a workaround. You can first use the VBA script introduced above to cancel the merger, and then the need to show the “merge” effect of the cell processing. For example, for the merging of columns (such as Figure 9, C1: E1 cells need to be merged), first enter the contents of cell C1, then select C1: E1 cells and right-click to select “Set cell formatting → alignment → horizontal alignment → cross-column centering” can be. If you want to merge the content of A3: A4, you can first merge any two lines of cells in the new document, such as G3: G4, while entering the contents of the merged display. Right-click G3: G4 and select Copy, and then positioned to A3: A4 cells, in turn, select “Start → Paste → → other paste options → linked images” can be. This has the effect of merging cells, but also does not affect the calculation, because the original cells in the document was not merged (Figure 9).


Leave a Comment