How to unmerge cells and fill values in Excel

View post on imgur.com

I've needed to do this quite a few times, but couldn't easily find a clear example online. Here's how to unmerge cells and fill values in Excel.

For the simple case where the range only contains one distinct value, it's trivial to copy the value to all cells. However, where there are many distinct values in the range, there are a couple more steps involved. This still only takes a few seconds to do.

1. Highlight the range that you want to unmerge and fill.
2. Click the "Merge & Center" button to unmerge. For each of the distinct values in the range, this will leave all but the first cell blank.
3. Click on the first blank cell, and use a formula to copy the cell above. For example, press equals, up, enter.
4. Copy this cell.
5. Highlight the range again, and press F5 to access to Go To menu.
6. Click Special... and select Blanks. Press OK.
7. Paste.

If you want to keep just values and remove the formula, simply highlight the range again, copy, paste, and paste values only. E.g. Ctrl + C, Ctrl + V, Ctrl, V

Alex