Office hacks: Excel worksheet serial number is not simple to fill

Excel worksheet data in many cases are marked with a serial number. Serial number is generally added to the first cell to enter 1, 2, and then select the two cells down after filling. This method is convenient and fast, but also has drawbacks. When there is data deletion, the serial number is not continuous, you have to re-select the fill, and the above method can not fill in the serial number by department, but also can not be filtered to maintain a continuous serial number. Next, we will solve the problems arising from the above methods by the following methods.

1. Enter the serial number with the help of the name box

Use this method to generate a larger number of serial numbers, basically how many records can generate a number of serial numbers. In cell A2, enter “= ROW (A2) – 1”, drag down the fill to the desired record, so that you can generate a continuous serial number. If there are too many records, dragging and filling is too troublesome, you can enter the number “1” in cell A2, enter “A2:A1001” in the name box (1001 is the last record of the line number), and then in the edit field, enter “= ROW (A2) -1”, press Ctrl + Enter, you can generate 1 to 1000 consecutive serial numbers (Figure 1).


Tip: If you fill the serial number from the second row, enter “=ROW(A2)-1”; if you fill the serial number from the first row, enter “=ROW(A1)”. The advantage of this method of filling serial numbers is that even if a record or multiple records are deleted, the serial number is still continuous and does not need to be refilled. 2.

2. Fill in the serial number by department

Some worksheets require separate departments to fill in the serial number, the serial number of different departments are from 1, the same department in increasing order, which can be achieved with the help of COUNTIF function.

In cell A2, enter “= COUNTIF (B $ 2: B2, B2)”, drag down to fill the last record can be (Figure 2).


Tip: To use this method, you need to sort by “department” in advance and put the records of the same department together. 3.

3. Keep consecutive serial numbers after filtering

After entering the serial number in the conventional way, once the data is filtered, the serial number will be disordered. If you need to filter the serial number still remains continuous, you can enter the formula “= SUBTOTAL (3, B $ 1: B2) -1” in cell A2, drag down to fill the last record can be (Figure 3).


4. merged cells to add consecutive serial numbers

For the merged cells of the worksheet serial number filling, you need to use the MAX function to achieve. For example, the need to add all the merged cells in column A serial number, first select the cell to add the serial number, edit the bar enter the formula “= MAX ($A$1: A1) +1”, press Ctrl + Enter key to fill (Figure 4).


Leave a Comment