Take a Guess to Win Windows 10 Pro Key!
previous arrow
next arrow
Slider

Ten Tricks for AutoFill in Excel You Should Know

Date:2022-5-30 Author:Sandra

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.

Ten Tricks for AutoFill in Excel You Should Know

The correct way is to enter number 1 in cell A1 and use the fill handle but keep pressing the Ctrl key.

Ten Tricks for AutoFill in Excel You Should Know

Or you can type numbers 1 and 2 in cells A1 and A2, then fill it.

Ten Tricks for AutoFill in Excel You Should Know

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.

Ten Tricks for AutoFill in Excel You Should Know

No.3 Enter Working Day

Ten Tricks for AutoFill in Excel You Should Know
Ten Tricks for AutoFill in Excel You Should Know

No.4 Fill Date in Increments of Month or Year

Ten Tricks for AutoFill in Excel You Should Know
Ten Tricks for AutoFill in Excel You Should Know

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.

Ten Tricks for AutoFill in Excel You Should Know

No.6 Fill with Any Number of Intervals

Ten Tricks for AutoFill in Excel You Should Know

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.

Ten Tricks for AutoFill in Excel You Should Know

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.

Ten Tricks for AutoFill in Excel You Should Know

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.

Ten Tricks for AutoFill in Excel You Should Know

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.

Ten Tricks for AutoFill in Excel You Should Know

Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *