X

Ten Tricks for AutoFill in Excel You Should Know

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.

Categories: Excel Tips
Sandra: