Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

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

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


4 Easy Ways 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.

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 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: Fill Down to Next Value in Excel (5 Easy Methods)


Similar Readings


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: [Fixed!] AutoFill Formula Is Not Working in Excel Table (3 Solutions)


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.

Read More: How to Fill Formula Down to Specific Row in Excel (7 Easy Methods)


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.


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. Additionally, if you want to read more articles on Excel, you may visit our website, Exceldemy. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.


Further Readings

 

 

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo