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

In Excel, there are several circumstances when we need to fill down the column to the last row with the same data type or fill the series. For example, if we want to enter the serial identification numbers of students or copy the same formula down simply for the same calculation throughout the row. Then, copying and pasting the formula into each cell, or simply entering the Id numbers into each cell, is tedious and time-consuming. However, we can easily do all of this in Excel. So, in this article, we will demonstrate five different methods for filling down the last row with data in Excel.

Contents hide

📁 Download Excel File

Learn to Fill Down to Last Row with Data in Excel with These 5 Ways

1. Using Fill Handle

2. Applying Fill Series from the Toolbar

3. Inserting Formula

4. Utilizing Power Query

5. Embedding VBA

📄 Important Notes

📝 Takeaway from This Article

Conclusion

📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.

Fill Down to Last Row with Data.xlsm

Learn to Fill Down to Last Row with Data in Excel with These 5 Ways

Firstly, let us get acquainted with our dataset used throughout the article as an example. This spreadsheet contains the Book Names and their identification number mentioned as Book ID for ABC Bookstore. We want to insert the Book ID numbers in an incrementing order. In this article, we will demonstrate 5 easy ways to fill down to last row with data in Excel using this dataset.

method 1

1. Using Fill Handle

In Microsoft Excel, Fill Handle tool helps you extend (and fill) several dates, numbers and even text to cells of the last row. In this method, we will use the fill handle to fill down to the last row with data in Excel. Let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

First, select the cell that you want to copy down to the last row. Here we are selecting cell B6 so it is now the active cell. You will see Fill Handle in the active cell of the spreadsheet in the bottom-right corner which is shown in the image below.

Now, drag the plus sign known as the fill handle icon that appears on the right side of the cell, or just double-click once the icon appears. It will fill all the cells with the same data in cell B6.

Then, click on the AutoFill Option and select Fill Series.

Finally, we will have our cells filled down to the last row with data in Excel.

method 2

2. Applying Fill Series from the Toolbar

The Fill Series tool is so efficient when you want to fill down with an incrementing value that can be 1 or more than that. Now, we will demonstrate how to use this tool to fill down to the last row with data. Let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

In the first cell, enter the first value. We selected cell B6 and entered the value.

Then, select the column or cells of the column where you want to fill down the formula.

After that, go to the Home tab and click on the Fill tool under the group of command Editing.

Next, select the Series option from the Fill.

A dialog box named Series will appear. Select Columns from the Series in options and Linear from the Type In the step value, we are entering 1 but you can enter any number to which you want to increment your data according to your worksheet and press OK.

Lastly, the outcome will look something like this.

📕 Read More: 4 Ways to Add Excel Sequence Number by Group

method 3

3. Inserting Formula

This method will use the formula for filling down to the last row with data. Let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

Enter the first value in cell B6.

Here, we want to increment the value by 1. So, we insert the formula in cell B7.

=B6+1

Now, we will select cell B7 and drag the fill handle icon or just double-click on it. The formula will be filled down to the last row and we will get our outcome.

📕 Read More: 7 Simple Ways to Increment Month by 1 in Excel

method 4

4. Utilizing Power Query

The Power Query is another useful tool that allows you to store data easily in hundreds of sources and reshape it to meet your needs with an engaging, no-code experience. In this method, we will utilize Power Query to fill down to the last row with data. Now, follow these steps.

⬇️⬇️ STEPS ⬇️⬇️

First, select the range B5:C15.

Go to the Data tab and select From Table/Range from the Get&Transform Data.

Then, Create Table popup will appear, select My table has Headers and press OK.

After that, Power Query Editor window will appear.

Then right-click on the first column and go to the Fill option and select Down.

Then go to the Home tab of the editor and select Close and Load To.

A dialog box named Import Data will appear. We selected the existing sheet and entered cell B17 as the destination cell and press OK. You can choose this or you can select a new worksheet then the table will be displayed in the new worksheet.

Finally, we will have our desired result.

method 5

5. Embedding VBA

We can fill down to the last row with data by embedding Microsoft Visual Basic Application (VBA) in our workbook. Now, let us see the steps.

⬇️⬇️ STEPS ⬇️⬇️

At first, select range B5:C15.

Then, go to the Developer tab and select Visual Basic from the code group.

Then, Microsoft Visual Basic for Application named box will appear. Go to the Insert tab and select the Module.

Enter the code and press F5.

Sub FillDowntoLastRow()

For Each inp In Selection
  If inp = "" Then
    inp.FillDown
  End If
Next

End Sub

Now, to run the code press Alt+F8 and select FillDowntoLastRow and then Run.

Lastly, we will have our outcome.

📄 Important Notes

🖊️ You can click the AutoFill Options button to alter how you wish to fill cells. Select the desired options by clicking the AutoFill Options icon that appears after you have finished dragging.

🖊️ To apply VBA codes, first, ensure you have the Developer tab in the ribbon before the application.

📝 Takeaway from This Article

📌 This article demonstrated five ways to fill the last row with data in Excel.

📌 In the first method, we used Fill Down to fill the last row with data in Excel.

📌 Then, we demonstrated the steps to use Fill Series from the toolbar to fill the last row with data in Excel.

📌 After that, we used Formula for filling down the formula to the last row.

📌 Next, we used the POWER QUERY tool to fill the last row with data in Excel.

📌 Finally, we embedded VBA to fill the last row with data in Excel.

Conclusion

This article has demonstrated five ways to fill the last row with Excel data. All these methods are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.

(Visited 164 times, 1 visits today)