While working in Excel, we often have to deal with large data sets. It is a wise idea to fill the first cell manually and then apply the autofill methods provided by Excel when there is a very big column to enter data into. In this article, I’ll show how to fill down to the last row with data in Excel with proper examples and illustrations.

Download Practice Workbook

3 Quick Methods to Fill Down to Last Row with Data in Excel

Here we’ve got a data set with the Names and Joining Dates of some employees of a company called Jupyter Group.

Today our objective is to fill the Employee IDs up to the last row automatically.

1. Use Fill Handle to Fill Down to the Last Row in Excel

You can use the Fill Handle of Excel to fill down a column up to the last row.

Step 1:

• First, fill the first cell manually.
• Here I have entered 1 into the first cell of the Employee IDs.

Step 2:

• Then double click or drag the small plus (+) sign on the rightmost bottom corner of the first cell up to the last cell. This is called Fill Handle.

All the cells will be filled with the value of the first cell (1 in this example).

Step 3:

• Now, if you want to fill them in series (Like 1, 2, 3, 4, …), click on the drop-down menu associated with the last cell. This is called Auto Fill Options.

Step 4:

• You will get a few options. Click on Fill Series.

You will find that your selected column has been filled with a Series of increasing numbers, like this, 1, 2, 3, 4, 5, …

Note: This method is very useful when you need to copy down a formula through a column with increasing cell references.

For example, in this data set, let us add a new column and enter this formula in the first cell of the new column.

This formula adds 6 months to the first date.

Now to copy this formula to the rest of the cells, just double click or drag the Fill Handle.

You will find that the formula will be copied to all the cells automatically with increasing cell references.

Cell E5 will get EDATE(D5,6).

Cell E6 will get EDATE(D6,6).

And so on.

2. Run Fill Series Option from the Excel Toolbar to Autofill Data

This method is useful when you want to fill down a column with a step increment of anything other than 1.

For example, to create a series like 1, 4, 7, 10, 13, … (Step increment is 3 here).

Step 1:

• Fill the first cell manually.
• Here I have entered 1 as the employee ID of the first employee.

Step 2:

• Then select the whole column.
• And go to the Home > Fill tool under the Editing group of commands.

Step 3:

• Click on Fill.
• You will get a few options. Select Series.

Step 4:

• You will get a dialogue box called Series.
• From the Series in menu, select Columns.
• And from the Type menu, select Linear.
• Then in the Step value box, enter the step increment that you want. For the sake of this example, I’m entering 3.

Step 5:

• Then click OK.
• You will find that your column has been filled with a series of increment 3, {1, 4, 7, 10, 13, …}.

3. Insert Excel Formulas to Fill Down to the Last Row with Random Values

Till now, we have seen how to fill down a column with a series of numbers.

If you want to fill down your column with random numbers other than a series of numbers, you can use this method.

Step 1:

• Enter this RANDBETWEEN function in the first cell:

• Here, the arguments “bottom” and “top “are the numbers respectively within which you want your random numbers.
• The related formula with the RANDBETWEEN function here will be:

• It will generate a random number between 1 and 100.

Step 2:

• Then double-click or drag the Fill Handle up to the last row.
•  All the cells will be filled with random numbers between 1 and 100.

Step 3:

• There is a problem with using the RANDBETWEEN function. That is, RANDBETWEEN is a volatile function.
• That means, each time you make any change in any cell of your worksheet, it will re-calculate and return a new value.
• To solve this problem, select the whole column and press CTRL+ C on your keyboard. And you’ll find the column highlighted as shown below.

Step 4:

• Right-click on your mouse.
• Select Paste Value from the options available.

• All the RANDBETWEEN outputs will turn into values, and they won’t change anymore.

Conclusion

Using these methods, you can fill down to the last row with data in Excel automatically. Do you know any other method? Or do you have any questions? Feel free to ask us.

Related Readings

Fix: Excel Autofill Not Working (7 Issues)

How to Number Columns in Excel Automatically (5 Easy Ways)

How to Use Autofill Formula in Excel (6 Ways)

How to Perform Predictive Autofill in Excel (5 Methods)

How to Enter Sequential Dates Across Multiple Sheets in Excel

The post How to Fill Down to Last Row with Data in Excel (3 Quick Methods) appeared first on ExcelDemy.