Excel merges cells while preserving all values

A merged cell is a command that users use when making a table, and it can be used to beautify multiple cells as a single cell.

In general, if you combine several cells that contain data, Excel prompts you to "when you merge cells, if more than one of the selected cells contains data, Excel preserves the data in the upper-left cell and deletes all remaining data." "As shown in Figure 72‑1.

Figure 72‑1 Excel Tips when merging multiple cells with data

This in many cases will make users feel embarrassed, the merger will lose data, affect the calculation of the data, and do not merge can not take into account the aesthetic. The following methods break through the limitations of Excel, preserving all values while merging cells. Assuming the table as shown in Figure 72‑2, you now need to merge a1:a4 and A5:a8 separately.

Figure 72‑2 table that requires merging cells

Select C1:C4 and click the Merge and center button in the toolbar. The same method merges the cells c5:c8, as shown in Figure 72‑3.

Figure 72‑3 Merge cells in a blank range of cells by the size of the destination area

Select C1:c8, click the Format Painter button in the toolbar, and then click A1 to format the copy. The result is shown in Figure 72‑4. In this process, Excel does not show any warning.

Figure 72‑4 Copy the formatting of the blank space to the target area

To verify that the merged cells retain their original data, you can use the formula in the D column to refer to the calculation and enter the formula in the D1:

=a1

Then drag to D8 for formula replication. The result is shown in Figure 72‑5.

Figure 72‑5 All values are retained after merging cells.