Nehad Ulfat
There are several short and quick methods to autofill numbers in Microsoft Excel. In this article, you’ll learn how you can easily apply those useful techniques to autofill numbers under different criteria with appropriate illustrations.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
12 Suitable Approaches to AutoFill Numbers in Excel
1. AutoFill a Column with a Series of Numbers
In our first example, we’ll see the basic use of Fill Handle to autofill a series of numbers. In the picture below, a number ‘1’ has been inputted in Cell C5. Now, we’ll use the Fill Handle option to autofill the series of numbers starting from 1.
📌 Step 1:
➤ Select Cell B5.
➤ Put your mouse cursor onto the right-bottom corner of the selected cell, you’ll find a Plus (+) icon there.
📌 Step 2:
➤ Drag the Plus (+) icon downward as long as you want.
➤ Click on the options menu as shown in the following picture and select the Fill Series command.
And you’ll be shown the series of numbers starting from 1 to 9.
Read more: How to Autocomplete Cells or Columns From List in Excel
2. AutoFill Numbers by Using ROW Function in Excel
The ROW function returns the row number of a cell reference. By inserting this ROW function in a cell and dragging it downward, we can find a series of numbers in a column.
In the following picture, Cell B5 is situated in Row 5. So if you apply the ROW function in that cell, the function will return ‘5’.
Now we can use the Fill Handle option to autofill the column up to a specific cell. If I want to start the number with ‘1’, then I have to input the following formula in Cell B5:
=ROW()-4
If I wanted to start the number with ‘1’, then I had to input the following formula in Cell B5:
=ROW()-4As my first input data was in the 5th row, the ROW function returned the number ‘5’. So, to get the number ‘1’ there, we have to subtract ‘4’ from the ROW function.
3. Insert OFFSET Function to AutoFill Numbers in a Column
OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference. By using the OFFSET function, we can create a series of numbers without using the Fill Series option after copying down.
In the following picture, the formula applied in Cell B4 is:
=OFFSET(B4,-1,0)+1
After pressing Enter, you’ll find the number ‘1’. You must keep in mind that while using this formula in a cell, you have to keep the immediate upper cell blank.
Now use Fill Handle to autofill the column and you’ll get the series of numbers at once. You don’t have to choose the Fill Series option here anymore as shown in the first method.
Read more: How to Use Autofill Formula in Excel
4. AutoFill Numbers by Using Fill Series Command in Excel
We can use the Fill Series option more precisely by activating the dialogue box from the Series command. Let’s go through the following steps to see how it works.
📌 Step 1:
➤ From the Home ribbon, go to the Editing group of commands.
➤ Select the Series command from the Fill drop-down under the Editing group of commands.
A dialogue box named ‘Series’ will open up.
Let’s assume we want to create a series of numbers with a common difference of ‘2’ and the series will end with the last value of not more than 20.
📌 Step 2:
➤ Select the Columns radio button from the Series in options.
➤ Input ‘2’ and ‘20’ in the Step value and the Stop value respectively.
➤ Press OK and you’re done.
You’ll find the series of numbers with the mentioned criteria right away.
Read more: Automatic Numbering in Excel
5. AutoFill Numbers While Skipping Rows (Blank Cells)
We can use the Fill Handle option to autofill a column while skipping rows at a regular interval. Let’s assume, we want to fill a series of numbers in a column where each number will skip a row to surpass the preceding number.
What we have to do is select two successive cells starting from the first input data as shown in the following screenshot.
After auto-filling the column with the Fill Handle, you’ll find the series of numbers starting with ‘1’ while skipping a row at regular intervals.
6. AutoFill Formulas in a Column in Excel
We can use the Fill Handle option to autofill formulas too along a column or a row. In the following dataset, the first two columns are representing the amounts of sales of some salesmen. In Column D, a 5% bonus will be added to each salesman based on their sales values. In Cell D5, the first bonus amount has been done manually by using the following formula:
=C5*5%
Now if we use the Fill Handle from Cell D5 and drag it down to Cell D11, we’ll get the resultant outputs as displayed in the picture below.
Similar Readings:
- How to AutoFill Cell Based on Another Cell in Excel (5 Methods)
- Fill Down to Last Row with Data in Excel (3 Quick Methods)
7. Double-Click Fill Handle to AutoFill Numbers
There’s another way of using the Fill Handle option and that is double-clicking the icon. In this process, the entire column will be updated automatically and you won’t have to drag down the icon anymore to autofill.
In the picture below, you’re seeing the Fill Handle icon in the right-bottom corner of Cell D5. Let’s click the icon twice and you’ll see the instant output.
Like in the picture below, you’ll get the return values immediately.
8. AutoFill Numbers with a Geometric Pattern in Excel
We can autofill numbers in a series by applying a geometric pattern too. What we have to do is set a multiplier for an initial value and set the series type as Growth. Let’s go through the following steps now:
📌 Steps:
➤ Open Series dialogue box again from the Fill options in the Editing group of commands.
➤ Select Columns radio button as the Series in option.
➤ Choose Growth as the Type of the series.
➤ Input ‘2’ and ‘200’ as the Step value and the Stop value respectively.
➤ Press OK.
You’ll find the geometric series starting from 2 with also the growth rate of 2 which means each resultant value will be multiplied by 2 until the final output exceeds 200.
9. AutoFill a Date Series in a Column
We can use the Fill Series command to autofill a series of dates too. What we have to do is select the Series type as Date and then input the Step value and Stop value. If we want to show the dates in a column then we have to select the Columns radio button from the Series in options.
After pressing OK, we’ll find the series of dates as shown in the following picture.
Read more: How to Autofill Dates in Excel
10. AutoFill Row Numbers with COUNTA Function to Ignore Blank Cells
COUNTA function counts the number of cells in a range that are not empty. By using the COUNTA function, we can define the serial numbers of the non-blank rows in a table or a dataset.
In the following picture, Column B will represent the serial numbers. We have to assign a formula in Cell B5, drag it down to the bottom, and define the serial numbers for all non-blank rows.
📌 Step 1:
➤ Select Cell B5 and type the following formula:
=IF(ISBLANK(C5),””,COUNTA($C$5:C5))➤ Press Enter and you’ll get the first serial number ‘1’ as the first row in the table is not empty.
📌 Step 2:
➤ Now use the Fill Handle to autofill the entire Column B.
And you’ll find the serial numbers for all non-empty rows at once.
11. Use SUBTOTAL Function to AutoFill Numbers for Filtered Data
In the following dataset, the number of sales of the three salesmen has been recorded for successive 15 days. Column B here represents the serial numbers of the rows. Since this is a filtered data table, we’ll find out how the serial numbers react after filtering the amounts of sales of a specific salesperson.
In the following table, we’ve filtered the data for Sam only. We’ve extracted the sales values of greater than $1500 for Sam here. But after filtering the table, the serial numbers have also been modified. Let’s assume, we want to maintain the serial numbers as they were shown initially.
📌 Step 1:
➤ Select Cell B5 and type:
=SUBTOTAL(3,$C$5:C5)➤ Use Fill Handle to autofill the entire column.
📌 Step 2:
➤ Now filter the sales values of Sam to show the amounts that are greater than $1500 only.
And you’ll now see that the serial numbers have not been modified here and they’re maintaining the sequence of the numbers.
12. Create an Excel Table to AutoFill Row Numbers (ROW Function)
In our last example, we’ll show how to insert a row inside a data table while the serial numbers will be updated simultaneously.
📌 Step 1:
➤ Select the entire table data (B5:F19) and name it with SalesData by editing in the Name Box.
📌 Step 2:
➤ Select Cell B5 and type:
=ROW()-ROW(SalesData[#Headers])The entire Column B in the data table will display the serial numbers.
📌 Step 3:
➤ Now right-click any of the row numbers on the left of the spreadsheet with your mouse cursor.
➤ Select the Insert option.
Like in the picture below, a new row will be added in the selected region and the serial numbers of the entire data table will be updated simultaneously.
Concluding Words
I hope all the methods mentioned above will now help you to apply them in your Excel spreadsheets when necessary. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
