This will create a simple formula that fills the first blank cell with the value from the above cell: With all the blank cells selected, type the equality sign (=) and press the Up Arrow key.In the Go To Special dialog window, tick off the Blanks option, and click OK:.Select the entire table again, go to the Home tab > Editing group, click Find & Select, and then click Go To Special….This will split all merged cells, but only the upper-left unmerged cells will be filled with data. Select your table (or just the columns that have merged cells) and click the Merge & Center button on the Home tab.To unmerge cells and fill down with duplicate values, please follow these steps: To improve the structure of your dataset, you may often need not only to unmerge cells but also fill each unmerged cell with the value from the original cell, as shown in the screenshot below:
#REDUCE MERGE AND CENTER IN EXCEL HOW TO#
How to unmerge cells and copy the original value to each unmerged cell
![reduce merge and center in excel reduce merge and center in excel](https://www.exceltip.com/wp-content/uploads/2014/08/27.png)
![reduce merge and center in excel reduce merge and center in excel](https://www.uab.edu/students/studentcenter/images/maps/1st-floor.jpg)
![reduce merge and center in excel reduce merge and center in excel](https://cdn.extendoffice.com/images/stories/doc-excel/delete-split-merged-cells/doc-delete-split-merged-cells-8.png)
The results look the same as merging, but all cells are intact. Now let's apply the Merge and Center approach to our main table. If we check the Format Cells dialog box, we'll see that Merge is left unchecked. The result looks the same as a merge, but all the cells are still in place. If the original selection has text in cells to the right, you'll need to manually remove that text before Center Across Selection takes effect.įor the next two rows, let's remove the text in columns N and O first, and then apply Center Across Selection. To access Center Across Selection, you need to go to the Alignment tab in the Format Cells dialog box.įrom the Horizontal menu, select Center Across Selection and click OK. Like merging, the first step is to select the cells across which you'd like to center text. In contrast, Center Across Selection only centers text it does not combine cells. Other text, if it exists, is destroyed during the merge. Only the value in the upper left cell is maintained. Recall that Merge & Center physically merges cells and centers the remaining text. Here we have the same table we looked at in an earlier lesson on aligning text across cells using Merge.
![reduce merge and center in excel reduce merge and center in excel](https://images.wondershare.com/pdfelement/word/merge-cells-01.jpg)
Unlike merging, Center Across Selection leaves all cells in place but still centers text across columns. In this lesson, we'll look at another approach to centering text across more than one column.