In our everyday Excel tasks, there’s often a need to efficiently fill cells with incremental values. For instance, this might involve a simple series like 1, 2, 3… Alternatively, in certain scenarios, there arises a requirement to populate cells with increments of a specific interval, such as 10001, 10012, 10023…
This guide provides solutions to rapidly and automatically accomplish these incremental cell-filling tasks.
In this article:
Method 1: Using Autofill Function
Excel offers the Autofill function, which allows you to swiftly populate cells with incremental values.
1. Choose the cell where you want to begin your series. For instance, let’s use cell A1 and input 10001.
2. In the cell below the starting value (e.g., A2), input the second value you desire.
3. Select both cells A1 and A2, then drag the autofill handle down to fill the cells with the desired incremental numbers.
Method 2: Employing Formulas
Another way is by using formulas to achieve autofill:
1. Select an empty cell and input your starting value. For instance, if you want to start at 10001, input this value.
2. In the cell below, input the formula “=A1+11” (A1 being the starting cell and 11 representing the interval; modify these values as needed).
3. Press Enter and then drag the formula down to fill the cells with the desired incremental values based on the specified interval.
Method 3: Automated Increment Cell Filling Using Kutools for Excel
Kutools for Excel provides the “Insert Sequence Number” utility, allowing you to rapidly automate the process of filling cells with incremental numbers. Additionally, you have the option to append a prefix or suffix to each generated number.
📢 Supercharge your Excel experience with Kutools! Unlock powerful features, streamline tasks, and boost productivity.
For a limited time, enjoy a whopping 30% off!
Don’t miss this chance to excel in Excel.
1. Choose the list or range where you intend to populate the increment cells, then click on Kutools, navigate to Insert, and select Insert Sequence Number. Refer to the screenshot.
2. In the emerging Insert Sequence Number dialog box, click on the “New” option to introduce a fresh sequence into the dialog Refer to the screenshot.
3. In the extended dialog box, perform the following actions:
- Provide a name for the sequence.
- Input the initial number for the incremental sequence in the “Start number” textbox.
- Enter the interval number in the “Increment” textbox.
- Define the number of digits for the populated numbers in the “No. of digits” textbox.
- Add a prefix or suffix to the numbers in the “Prefix (optional)” or “Suffix (optional)” textbox, as required.
4. Select the “Add” option to include this newly created incremental sequence number in the dialog. Subsequently, click on “Fill Range.” Refer to the screenshot.
You will now observe that the chosen cells have been populated with the incremental numbers.
Please take note of the following:
- If you wish to reset these incremental sequences and begin inserting from the initial number again, navigate to the Insert Sequence Number dialog. Select the sequence and click on “Reset” followed by “Ok.”
- Should you desire to conclude the sequence at a particular number, repeat the sequence insertion process, starting from the initial number. Check the “End number” option in the dialog. Enter the desired ending number in the textbox below. You can preview that the sequence will conclude with this number. Afterward, restart the sequence as needed.