Excel fill series, with drag shortcut, automatically increment and fill column with long numbers

It automatically fill series to cells with dragging method in Excel, that is, the numbers will be automatically incremented, either dragging down or dragging to the right, dragging it there, the number is increasing; this method is suitable for auto-filling short numbering, if you want to automatically fill a long sequence, then this method is not good, you need to use the "series" provided by Excel, it can automatically fill up to tens of thousands of sequences.

In addition to implementing digital auto-filling, you will sometimes encounter auto-filling of numbers + letters + numbers. In general, if a string has two parts, the last digit will only be automatically incremented if you drag. The number that is not the last is automatically incremented and requires a function method.

I, Excel fill series

(I) How to drag down numbers in excel(Excel fill down shortcut)

Select the cell to automatically fill numbers, such as A1, enter 1, click A2 to exit the input state, select A1 again, move the mouse to the cell fill handle in the lower right corner of A1, hold down the left button, drag down until drag to the 10th row, the cells that pass through are filled with 1, click the "Auto Fill Options" icon, select "Fill Series" in the pop-up menu, then all 1 becomes the series from 1 to 10; the process steps are shown in Figure 1:

Figure 1

(II) Excel auto generate number sequence

1. Select cell A1, enter 1, press Enter, click A1 again, select the "Home" tab, click the "fill" icon in Eniting group in the upper right corner of the Excel window, select "Series in the pop-up menu, open the "Series" dialog, select "Columns" for "Series in", input 1 for "Step value", enter 5000 for "Stop value", press Enter, then generate the series from 1 to 5000; see screenshot in Figure 2:

Figure 2

2. Tip: Open the "Series" dialog, you can also use shortcut keys: Alt + H + F + I + S, the button method is: hold down Alt, press H once, press F once, press I once, press S once.

3. "Step value" refers to how many intervals generate a number. In the demonstration, "Step value" is set to 1, then generate a number every interval 1; if "Step value" is set to 2, then generate a number every interval 2; If the start number is set to 1, an automatic number such as 1, 3, 5, ... can be generated.

II, Excel auto fill is incremented by 0.1 each time.(How to fill excel column with numbers)

1. Select the cell A1, hold down Alt, press H, F, I, S respectively in the order, open the "Series" dialog, select "Columns" for "Series in", and  enter 0.1 for "Step value", enter 3.2 for "Stop value", press Enter, the sequence of 2.2 to 3.2 is automatically generated; see screenshot in Figure 3:

Figure 3

2. If you want to increment 0.2 each time, you can set the step value to 0.2; if you want to increment 0.01 each time, you can set the step value to 0.01, and so on.

III, Excel 15 or more long numbers are automatically filled

(I) Auto fill 15-bit long numbers(Excel fill cells with sequential numbers)

1. There is a 15-digit long numbers in cell A1. After exiting the input state, it is automatically displayed in scientific notation, double-click A1, and each digit is automatically displayed. Move the mouse to the cell fill handle in the lower right corner of A1, drag down, the cell that passes through is automatically filled with increasing long numbers; the operation steps are as shown in Figure 4:

Figure 4

2. Excel displays long numbers in scientific notation by default. If you want to display each number, you can set the format of cell to text by pressing Ctrl + 1 to open the " Format Cells" dialog, select "Text" on the left.

(II) Auto fill 18-bit long numbers

1. Double click cell A1, copy the formula ="123456789099888001"&ROW(A1) to cell A1, move the mouse to the cell fill handle in the lower right corner of A1, hold down the left button of mouse, drag down, and the cells will be automatically filled by long numbers, see screenshot in Figure 5:

Figure 5

2. This method is to use the Row function to increase the number, the reason why it can display a long numbers is because the numbers are converted to text type, ROW(A1) is connected to a string of numbers of text type ROW(A1), it will automatically convert the returned row number to text.

IV, Excel fill series with letters and numbers(numbers + letters)

1. If there is a string of picture names 2019-001ten3.jpg consisting of "numbers + letters + numbers + letters", the 001 in middle is required to be automatically incremented. If you drag directly down, the 001 will don't automatically increment. See screenshot in Figure 6:

Figure 6

2. In order to make 001 automatically increment, you can use the Row() function to achieve, copy the formula ="2019-00"&ROW()&"ten3.jpg" to A2, press Enter, return to 2019-002ten3.jpg, 001 changes to 002, realize automatic increment; drag down, 002 is automatically incremented to 003, 004, ...; see screenshot in Figure 7:

Figure 7

3. The formula description: In the formula ="2019-00"&ROW()&"ten3.jpg", the non-incremental part is enclosed in double quotes, the incremental part is obtained by the Row() function to return the row number of the current cell to automatically increment 1, then connect the part that is not incremented with Row() with &.

Hint: If 2019-001ten3.jpg is not in cell A1, but in cell A3, the formula should be changed to ="2019-00"&ROW(A2)&"ten3.jpg", that is, get the row number of A2 with Row function, others and so on.