I want to ask you guys a question at the beginning of this tutorial. How do you enter a serial number from 1 to 10 in Excel? Type 1, 2, 3… one by one? If you do so, the ten hidden tricks are tailored for you.
No.1 Enter A Serial Number from 1 to 10
If you enter number 1 in cell A1 and use the fill handle directly, you will get a list of number 1.
The correct way is to enter number 1 in cell A1 and use the fill handle but keep pressing the Ctrl key.
Or you can type numbers 1 and 2 in cells A1 and A2, then fill it.
No.2 Enter A Serial Number from 1 to 1000
It’s obviously difficult to fill the list to 1000 by dragging the fill handle. So, we first type A1:A1000 in the Name Box and press Enter key, then type =ROW(A1) and press Ctrl and Enter key together.
No.3 Enter Working Day
No.4 Fill Date in Increments of Month or Year
No.5 Fill Date in Increments of Minute
When we fill the time data, the default increase is by hour, if you need to increase by one minute, you can type more than one time data.
No.6 Fill with Any Number of Intervals
No.7 Enter Multiple Content
If you enter “2 apples”, which contains both number and text. The numbers still can be increased by using the fill handle. If you keep pressing the Ctrl key while filling, the contents are just repeated.
No.8 Enter Consecutive Letters
We couldn’t get the consecutive letters by the fill handle directly in Excel. So, we need to finish this with the help of a formula.
Type =SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,) in cell A1 and fill the list.
No.9 Fill with The Repeat Serial Number of A Fixed Number of Times
If you want to enter 1, 1, 1, 1, 2, 2, 2, 2, this kind of fixed serial number. We also need the help of a formula.
Type =INT((ROW(A1)-1)/4)+1 in cell A1 and fill the list.
The “4” in =INT((ROW(A1)-1)/4)+1 represents the times each number is repeated.
No.10 Enter The Serial Number of The Specified Cycle
If we need to quickly get the serial number of 1, 2, 3, 4, 1, 2, 3, 4, …. We do need to use a formula.
Type =MOD((ROW(A1)-1),4)+1 in cell A1 and fill the list.