How to quickly add an ordinal number in a merged cell in Excel

How to quickly add an ordinal number in a merged cell in Excel

As shown in the following illustration, you need to include the ordinal number in column A of the data:

If we add an ordinal by using a traditional drag fill, the system pops up with the hint: "This operation requires the merged cells to have the same size"

That is, the data source format can not be modified, and can not use the normal method to add serial number, how to do?

Do you want to fill it by hand?

The answer is no, if the amount of data is too large, we can not be one to manually add it.

Here is a simple way to introduce you.

First, select the cell to which you want to add an ordinal:

formula bar input equation:

=max ($A $1:a1) +1

Note that the key step is to press Ctrl+enter to end the input:

The final effect is as follows:

Simply say the formula:

=max ($A $1:a1) +1

The first A1 more than two dollar sign, is an absolute reference form, when the formula is copied down, the ranks will not change.

The second A1 is the relative reference form, and when the formula is copied down, the line number changes dynamically when the formula is in the same row.

This formula can be understood as:

Calculate the $a$1 cell to the maximum of one row on the line that contains the formula, and then add 1 to the calculated result. Thus the effect of sequential serial number is realized.