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.
📁 Download Excel File
You can download the Excel file used for the demonstration from the link below.
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.
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.
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
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.
- 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
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.
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.
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.
- 6 Ways to Autofill Sequential Letters in Excel
- 3 Ways to Auto Number or Renumber After Filter in Excel