Office hacks: merging cells with the same value but different numbers in the column

Want to merge the value of the Excel table data columns in the same cell, if the value of the number of cells contained in the same okay, you just need to merge a class with the format brush to continuously brush the other cells, but if the value of the number of cells contained in different, how to do it? In fact, the use of “Category Summary + locate empty values” can be very good to complete this task.

First, as needed, sorted by the desired category in ascending or descending order. In this way, data from the same category are placed together (Figure 1).


Then switch to the “Data” tab, click “Category Summary”, in the pop-up window, the “Category Field” is set to “Shift “, “Summary mode” is set to “Count”, “Selected summary items” is checked at “Shift “, click “OK” (Figure 2).


Next, select the A2: A17 cell area, switch to the “Start” tab, click “Find and Select → Location Conditions”, in the pop-up window, select “null”. so that the empty cells in the A2:A17 cell area will be selected. Click the “merge after the center” button, and then in the formula editing bar, type “= B2”, while pressing Ctrl + Enter key combination. Thus, the merged cell is assigned to the class value (Figure 3).


Next, select the data area, switch to the “Data” tab, click “Category Summary” in the pop-up window, click “Delete All”, so that the summary items (such as counts, etc.) will be all deleted (Figure 4).


Finally, select the merged cells, copy, click “Paste → Paste Values → Value”, the merged cells are centered horizontally and vertically, the “Shift” field moved to the merged cells, delete the original shift data. In this way, the operation of merging the same value in the column but the number of different cells is complete (Figure 5).


Tip: The screenshot in this article is completed in Excel 2019, the method is applicable in all versions from 2003 to 2019.

Leave a Comment