How Excel merges cells and merges cell shortcuts

The symptom is reproduced as follows:

First step: Enter numbers in the A1:c4 area. The actual area can be selected by oneself, the number can also according to own liking to lose.

Step Two: Select the A1:a4 area, click the Merge Center command button in the Start tab alignment Ribbon, and click OK in the pop-up dialog box to complete the cell merge.

The above two steps are a normal cell merge step, without any problems. The final effect merges the A1:A4 area into one cell, and the value inside is the value 100 in the previous A1 cell.

Step three: Continue just the operation, so that the merged A1 cell in the Geshing state, click the Format Painter button, and then drag the mouse to select the B1:b4 area, so that the B1:b4 area to apply A1 cell format, that is, the status of merged cells.

The final effect is shown in the above picture, there is nothing strange in the picture above, but you can see the problem by looking at the status bar.

Also select B1 cells, and observe the status bar to see that the count is 4 and the sum is 100, which is the same as the result before the merge.

What do we find in the picture above? Although B1:B4 cells are merged, they are not the same as the values in the upper-left cell of the merged area as we normally merge, but all values are preserved.

Validation: Let's verify that the data is really preserved, enter "=A2" in cell A6, and enter "=B2" in B6 cell, the final result shown in the following figure, you can see that the A2 value is 0, that is, the B2 's original data is preserved.

Guessing: How Excel handles merged cells

After some of the above tests, the initial guess is that the Excel merge cell operation actually performs several steps, guessing as follows:

1. Store data in the upper-left cell of the merged area

2. Empty data from all cells in the merged area

3. Merging the selected areas

4. Fill in the merged area with the previously staged left cell data

Features of Excel?

Why are all the data in the merged cells preserved by the "merged cells" that are painted in a format? We all know that the format brush itself is used to "brush" format, can only be used to "copy" format, and can not perform data deletion operations. When you use formatting to merge cells, only "format is copied," and the work that deletes data is not executed. Because the deletion of the data itself is not in the "format" category, it causes the problem mentioned above.

If it's the nature of Excel itself, we can use this feature to hide some data, and it's easy to see real data, either by using cell references or by canceling the merge of the regions.

Imaginary:

Since you can implement data retention by using the Format Painter when you implement a cell merge, Excel is itself able to implement merged cells and preserve the data, and you want subsequent Excel to provide the option to keep all of your data.