How to Fill Down to Last Row with Data in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Fill Down to Last Row with Data in Excel: 4 Easy Ways

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

Easy Ways to Fill Down to Last Row with Data in Excel

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


Method 1. Using Fill Handle Tool to Fill Down to Last Row in Excel

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

Steps:

  • Firstly, fill in the first cell manually.
  • Secondly,I have entered 1 into the first cell of the Employee IDs.
  • Thirdly, 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 a Fill Handle.

Using Fill Handle Tool to Fill Down to Last Row in Excel

  • After that, all the cells will be filled with the value of the first cell (1 in this example).
  • 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.
  • Besides, you will get a few options.
  • Then, click on Fill Series.

  • Therefore, you will find that your selected column has been filled with a series of increasing numbers, like this, 1, 2, 3, 4, 5, …

Read More: How to AutoFill Formula When Inserting Rows in Excel


Method 2. Running Fill Series Option from 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).

Steps:

  • Firstly, fill in the first cell manually.
  • After that, I entered 1 as the employee ID of the first employee.
  • Then, select the whole column.
  • Besides, go to the Home > Fill tool under the Editing group of commands.
  • Now, click on Fill.
  • Therefore, you will get a few options—select Series.

Running Fill Series Option from Excel Toolbar to Autofill Data to Fill Down to Last Row in Excel

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

  • Consequently, you will find that your column has been filled with a series of increments 3, {1, 4, 7, 10, 13, …}.

Read More: How to Autofill Dates in Excel 


Method 3. Inserting RANDBETWEEN Function to Fill Down to Last Row with Random Values

Up until 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.

Steps:

=RANDBETWEEN(bottom,top)

  • 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:

=RANDBETWEEN(1,100)

  • It will generate a random number between 1 and 100.
  • Then double-click or drag the Fill Handle up to the last row.

Inserting RANDBETWEEN Function to Fill Down to Last Row with Random Values in Excel

  • Here, all the cells will be filled with random numbers between 1 and 100.
  • There is a problem with using the RANDBETWEEN 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.

  • So, right-click on your mouse.
  • Besides, select “Paste Value” from the options available.

  • After that, all the RANDBETWEEN outputs will turn into values, and they won’t change any more.


Method 4. Applying VBA Code to Fill Down to Last Row with Data in Excel

VBA is a programming language that may be used for a variety of tasks, and different types of users can use it for those tasks. Using the Alt + F11 keyboard shortcut, you can launch the VBA editor. In the last section, we will generate VBA code that makes it very easy to fill down to the last row with data Excel.

Steps:

  • Firstly, we will open the Developer tab.
  • Then, we will select the Visual Basic Command.

Applying VBA Code to Fill Down to Last Row in Excel

  • Here, the Visual Basic window will open.
  • After that, from the Insert option, we will choose the new Module to write the VBA

  • So, paste the following VBA code into the Module
  • Besides, save the following code.
Sub Fill_Down_to_LastRow()
Range("B5").AutoFill Destination:=Range("B5:B21"), Type:=xlFillSeries
End Sub
  • After that, press F5 to run the VBA

  • Finally, you will get the following desired result.


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

In this article, we’ve covered 4 Easy Ways to Fill Down to the Last Row with Data in Excel. We sincerely hope you enjoyed and learned a lot from this article.

If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Further Readings


<< Go Back to Excel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo