How to turn the serial number automatically generated by EXCEL into continuous numbers after removin

1. Open the excel document you want to operate.

2. Press the Ctrl+L key to pop up the Create Table prompt box, and click the OK button in the box.

3. Then generate the table.

4. Of course, we can modify the style of the following table.

5. In the cell below the serial number, i.e. a2, enter =subtotal(103,b$2:b2)*1

6. After entering, press the Enter key to automatically generate the serial number.

7. Then insert the row.

8. After the row is inserted successfully, the serial number will not change at this step.

9. After entering the cell corresponding to the name column, press the enter key for example, such as "Li 45" in this example. The serial number auto-growth remains continuous.

10. After the same row deletion operation is carried out, the serial number will be automatically decreasing and continuous.

1. Enter the serial number in column B cells of Excel**.

2. Then enter the function formula in cell A1: =counta($b$1:b1).

3. Click Enter to generate the formula calculation result.

4. Fill the formula of A1 downwards to generate the corresponding serial number in batches.

5. Then select the third to fifth rows to delete the data in these three rows.

6. After deletion, it can be seen in the figure below that the sequence in column A is still continuous, but the sequence number in column B has been interrupted.

Excel wants to make the sequence number automatically increment without being affected by the deleted row, it is necessary to use the formula sequence number;

The formula needs to be based on the situation of another column, from top to bottom, there are several data in another column, the serial number is several, as shown in the figure, according to the name of column b to compile the serial number, the formula is: =counta($b$2:b2);

Drop down to get other serial numbers;

Delete any of the rows, and you can see that the sequence numbers are still sequential;

<>1. Define the initial value of 1, or other numbers you want to start with;

2. Enter the cells in the second row: =counta(a$1:$a 1)+1, where a$1 represents the absolute value of the cell marked 1, and $a 1 represents the cell of the loop, which will continue to change to a2, a3 with the next application....

3,Select the second cell,Press and hold the lower right corner to pull down to automatically generate the serial number,When you delete a row in the middle,The serial number will be automatically continuous,office2013 personal test,June 12, 2019,Produced by "Technical Service Micro Classroom"!

You use the left mouse button to select and delete the first few rows, then move the mouse to the lower right of the cell, and when "ten" appears, drag it down with the left mouse button.

You use the left mouse button to select and delete the first few lines.

Material Tools: Excel2010

1. Excel is often used in the work, and each line must be numbered, and it is necessary to delete it when there is a possibility in the process, and the number should also change with the deletion of the line; A ** serial number is shown below:

2. Excel beginners are often 1, 2, 3??Enter the serial number one by one, which is very inconvenient, if you delete one of the rows, the other serial numbers will have to be re-entered; Another is to use the filling function of excel to delete a row and refill the drop-down number;

3. If the data is less, the use of filling is acceptable, if there is a lot of data, and it is deleted more frequently, then the operation on the serial number is more troublesome; You can use Excel's function function; The main use is the row() function;

4. Still use the above **, the operation steps are as follows; Enter the formula "=row()-1" in the cell under the band number of the buried order", here subtract how much according to the distance of the first cell of this cell; You can also see the original serial number of excel on the left; In fact, the value of row() is the ordinal number on the left side of the current line;

5. Then use the filling function of excel to pull down and fill the serial number column of **; Yushi.

6. At this time, each cell in the serial number column is a formula"=row()-1"；

7. When a line in ** is deleted in the future, the serial number will automatically change, and there will be no broken number;

After removing a number of rows in the sequence of good hand spine in Excel**, the numeric number is quickly rearranged in order, which can be achieved by using the row function, and the sequence number is calculated by the line number, no matter how the line changes, the sequence number always changes with the line number.

The steps of the method are as follows:

1. Open the excel** that needs to be operated, enter the formula =row() in the first cell, and then press enter to complete the formula input. [If the sequence number is not the same as the line number, you can subtract the difference between the sequence number and the line number after the function].

2. Fill in the cell formula below by dropping down, and then delete several of the rows.

3. Return to excel**, and find that after several rows are deleted in the excel** sequence, the numeric serial numbers are automatically and quickly rearranged in order.

One way to do this is to re-pull the sequence, which is also very convenient.

Another way, make the sequence into a formula, no matter how you delete it, the sequence will not go wrong, such as linking the sequence to the line number, your sequence number is a2-a100, then enter the formula in a2:

row()-1

The formula can be pulled down.

Select the Ordinal Number column. del clear... The first input is 1, hold down Ctrl, and drag down to form a new one.

Select the comprehensive serial numbers according to the column, and click in turn: - Fill - Series - OK.

1. For example, the data in the figure below. The first 3 characters of each cell data are the same.

2. Delete the three identical characters of "gdv" in batches at one time. Select all data cells for the muffler.

3. Click on the "Data" tab again; Click "Column" again. In the column wizard that pops up, click "Fixed Width", and then click "Next".

4. After the next step, go to the second page of the column wizard. Then, place the mouse under the right dot of the "v" as shown in the figure below, and an upward arrow line will be displayed.

5. Click the next step to grip the hole. Go to the third page of the column. Then click "Do not import this column (skip)"; Click Done again.

6. After the point is completed, the column wizard closes. The data has been stripped of the first three characters "gdv.""。

How to delete the first (or last) digits of the number data in the whole column in Excel can be implemented with the left or right function.

The method and base steps are as follows:

1. Enter the function =right(a1,4) in a blank cell. [where a1 is the target cell that needs to be operated, and the number 4 means to truncate the last 4 characters, that is, delete the first two characters].

2. Press Enter to complete the input of the formula, return to excel**, and you can find that the first few digits of the relevant cells have been successfully deleted.

3. Click on the lower right corner of the cell and drag the mouse to fill in the cell formula of this pure filling related forest shed.