The three essential Office suites for professionals: Word, PPT, and Excel. Recently, I have to count a lot of data indicators for my year-end summary report. I encountered some small Excel problems and noted a few Excel tips and tricks in a series of posts.
When a project management counts the team hours of a project, I exported the project working hours statistics from ZenTao’s statistics module into an Excel sheet. The structure is as follows. The cells of the same project column are merged.
When we need to filter and focus on specific projects, the merged cells will cause the filtering to fail normally, and blank options will appear. As shown in the figure below. Therefore, it’s necessary to unmerge the cells and automatically fill the names of each similar project into the blank cells.
If there are only a few dozen rows, just copy and paste manually. However, It is troublesome to have a worksheet with thousands of merged cells. Writing Python scripts can also handle it, but as a programming novice, I want to solve the problem in the most efficient way. Here’s how to quickly unmerge cells and automatically fill the original values using Excel’s built-in features, as shown in the screenshot below.
1. Select the Column with Merged Cells
2. Click Home > Merge & Center > Unmerge Cells
3. The merged cells are now unmerged, with only the first cell filling with the original value. Then select the worksheet fill range again, here select the project name column.
4. Then click Home > Find & Select > Locate Conditions, as shown below:
5. Click OK, all blank cells in the table range are selected. Then type = and select the cell containing the original value of the first merged cell. As shown below:
6. Press Ctrl + Enter, and all blank cells are filled with the original merged data, as shown below:
That’s the method I used today. Of course, if you have time, you can use VBA code to fill duplicate data, which is even more convenient.
