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

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.

Read more: How to Use Autofill Formula in Excel


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.

Data Set to Fill Down to the Last Row in Excel

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.

Filling the First Cell Manually in Excel

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.

Dragging the Fill Handle to Fill Down to the Last Row in Excel

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.

Auto Fill Options in Excel

Step 4:

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

Clicking Fill Series to Fill Down to the Last Row in Excel

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

Autofill in Excel Using the Fill Handle

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.

=EDATE(D4,6)

Entering Formula in the First Cell in Excel

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.

Dragging Down Formula Using the Fill Handle in Excel

Read more: How to Autofill Numbers in Excel


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.

Filling the First Cell Manually in Excel

Step 2:

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

Selecting Fill Tool from Excel Toolbar

Step 3:

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

Selecting Fill Series Option from Excel Toolbar

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.

Series Dialogue Box in Excel

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, …}.

Autofill Column Using Fill Series Tool in Excel

Read more: How to Autocomplete Cells or Columns From List in Excel


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:

=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.

RANDBETWEEN Formula in Excel

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.

Dragging the Fill Handle in Excel

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.

Copying a Column in Excel

Step 4:

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

Selecting Paste Value in Excel

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

Autofill Column with Random Values in Excel


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.


Further Readings

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