Shulou(Shulou.com)11/24 Report--

Hello, everyone, Hi~. I'm Tian Tian, who doesn't want to work on weekdays.

In the morning, I saw such a table in the elevator.

Boy, I thought I was blind. When did 84 disinfectant have so many "brothers and sisters"?

At a glance, we can see that the classmate who made the watch, Excel did not master it well!

At work, the most common way to make a table is to add a serial number, so is there any other way besides the drop-down fill?

Today Tian Tian will teach you 5 ways to update the serial number automatically with the Excel function to help you improve your work efficiency!

1. The automatically updated sequence number ❶ is entered in the blank cell: "= Row function".

The parentheses are empty and the number of rows in the current cell is counted.

❷ subtracts the number 1 from the calculation result to get the sequence number 1, and double-click the cell to fill the formula down to get the sequence number that can be updated automatically.

In this way, when we delete a row, we can also ensure that the sequence number is still continuous.

2, automatically rearrange the sequence number ❶ in the blank cell enter: "= Subtotal function".

First parameter: select the number 3, which means to calculate the number of non-empty cells.

The second parameter: select the cell on the right and enter "colon". The formula will automatically complete the range of cells.

PS: the formula means to calculate the number of non-empty cells from B22 cell to B22 cell range.

❷ select B22 in front of the colon, use the "F4 key" to quickly lock, put anti-parentheses, enter "* 1" at the end of the formula, press the enter key, double-click the lower right corner of the cell to fill the formula in batches.

When we screen the department, the serial number is automatically reordered according to the screening results.

3, merge cell serial number ❶ select the blank column, enter: "= Max function" in the active cell.

Use the mouse to select the serial number cell above, enter "colon", and the formula automatically completes the range of cells.

PS: the formula means to calculate the maximum value of cells A41 to A41.

❷ uses the "F4 key" to lock the front A41, put in anti-parentheses, enter "+ 1" after the formula, and finally press the "Ctrl+Enter" key combination on the keyboard to fill the formula in batch.

4, cross-line fill serial number ❶ select the serial number column, press the "Ctrl+G" key combination to open "positioning condition", select "null value", enter the function formula: "= Counta".

Select the cell on the right and enter the colon, and the formula automatically completes the range of cells.

PS: the formula means to calculate the number of non-empty cells from B62 cells to B62 cell ranges.

❷ uses the "F4 key" to lock the first B62 and press the "Ctrl+Enter" key combination on the keyboard to batch fill the formula.

5. The sequence number of the independent loop ❶ is entered in the blank cell: "= Countif function".

The first parameter: select the cell on the right, enter "colon", the first parameter represents the statistical area, marked with a comma, the second parameter: select the cell on the right.

❷ uses the "F4 key" to lock the first B86, press enter after the formula is entered, and fill the formula down. In this way, our serial number will be displayed in a separate loop according to the contents of the cell on the right.

PS: the meaning of the formula is to count the number of B86 occurrences from B86 to B86.

All right, have you learned all five skills today? Is it super practical? go and try it ~ (* '∀ `) ~ ♥

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Tian Tian

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.