6 Ways to Fill Down to Last Row with Data Not Blank in Excel

We need to fill down blank cells to the last row with data in excel frequently. It is very annoying and inefficient to fill those cells manually not to mention time-consuming when Excel can automatically do that for you. To fill down to last row with data not blank in Excel efficiently, you can follow the next 6 convenient approaches.


📁 Download Excel File

Download the following Excel workbook to realize the topic more clearly.


Learn to Fill Down to Last Row with Data Not Blank in Excel with These 6 Easy Approaches

Today we will learn how to fill down to last row with data in Excel with 6 approaches. We will use the sample dataset below.


Approach

1. Using Fill Handle Icon

One of the most common features in Excel that most people use is the Fill Handle icon. Let’s see how that works.

  ⬇️⬇️ STEPS ⬇️⬇️

  • First, Enter AA01 in cell B6.
  • Next, move your cursor to the bottom right corner of cell B6 and double-click.

  • Excel will automatically increment the data till the last row.

fill down to last row with data using fill handle

  • Now, we will show how to apply formulas to the last row using the Fill Handle icon.
  • Select cell E6 and type in the following formula, and press Enter:

=D6*5%

  • Just like before, move your cursor to the bottom right corner of cell E6 and double-click.

  • Done! All the cells now contain 5% of the Price as Commission.

fill down to last row with data applying fill handle


Approach

2. Applying Fill Option

Now, we will show how to fill down to the last row with data using the Fill option from the Excel Ribbon.

  ⬇️⬇️ STEPS ⬇️⬇️

  • We will first enter Serial No. 1 in cell B6.
  • Now select cells B6:B12.
  • Navigate to the Home tab and under the Editing group, click the Fill drop-down menu.
  • Then, select Series.

fill down to last row with data using the fill option from excel ribbon

  • A dialog box will appear momentarily.
  • Check in the Columns and Linear. Click OK.

  • You will get the serial numbers easily.

  • Now, we will show you how to apply formulas using the Fill option.
  • Select cell E6 and type in the following formula, and press Enter:

=D6*5%

  • At this time, select cell E7. Just like before, get to the Fill drop-down menu and click Down.

fill down to last row with data utilizing the fill option from excel ribbon

  • The formula is now applied to cell E7. Or, you could just select cell E7 and press Ctrl+D concurrently to do the same.

  • Do this to other cells as well.

Note:

  • Using the Fill Handle icon is very efficient if you want to apply formulas.
  • You can press Ctrl+R simultaneously to apply formulas to the right in rows.
  • If you change the Step value to 2 for Linear, you will get 1,3,5,7….
  • If you choose Growth from the Series dialog box and change the Step value to 2, you will get 1,2,4,8….
  • The Autofill option will fill the cells with 1.

Approach

3. Implementing Power Query

Now it’s time to use the Power Query to fill down to last row with data not blank.

  ⬇️⬇️ STEPS ⬇️⬇️

  • To use Power Query, we need to convert our dataset to a Table.
  • Select cells B5:E12. Now, go to the Insert tab and click Table.

fill down to last row with data applying the power query

  • A Create Table dialog box will open. Check in the My table has headers box and click OK.

  • Done! Afterward, select cells B5:E12 Navigate to the Data tab and click From Table/Range (for Excel 365, click From Sheet).

fill down to last row with data implementing the power query

  • The Power Query Editor will open.

fill down to last row with data applying the power query

  • After that, right-click on the Device header, and from Fill, click Down.

  • Your table will be just like the following screenshot.
  • Now from the Ribbon, click Close & Load.

fill down to last row with data employing the power query

  • A new worksheet will contain the filled data.

fill down to last row with data applying the power query


Approach

4. Utilizing Go To Special Command

We will find the blank cells using the Go To Special dialog box and then we will fill down those blank cells with data.

  ⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, select cells B5:E12 and go to the Home tab, and click the Find & Select menu.

  • Click Go To Special.

  • From the Go To Special dialog box check in the Blanks button and Click OK.

  • The blank cells are identified and the first blank cell is automatically selected.

  • Now we will type = and then use the up arrow key to declare the formula:

=B6

fill down to last row with data using go to special dialog box

  • Now press Ctrl+Enter together.

fill down to last row with data using go to special dialog box

  • All the blank cells will now accommodate the upper cell’s value.

Approach

5. Executing VBA Code

This time we will show how to execute VBA code to fill down to the last row with data in Excel.

  ⬇️⬇️ STEPS ⬇️⬇️

  • Initially, right-click on the VBA sheet. Click View Code.

  • Suddenly, a code window will open. Insert the following code in the window.
Sub Fill_Down_to_Last_Row_with_Data()
Dim rg As range
Set rg = range("B6:E12")
Dim cl As range
For Each cl In rg.Cells
If cl = "" Then
cl.FillDown
End If
Next
End Sub

how to fill down to last row with data by executing vba code

  • Now to execute the code, press F5 and click Run.
  • Go back to the VBA sheet and you will notice the change just like in the photo below.

fill down to last row with data by running a vba code


Approach

6. Using RANDBETWEEN Function

In our final method, we will generate random numbers between 50 to 100 as Commission using the RANDBETWEEN function. Let me show you.

  ⬇️⬇️ STEPS ⬇️⬇️

  • Select cell E6 and type in the following formula and press Enter:

=RANDBETWEEN(50,100)

fill down to last row with data using randbetween function

  • Use the Fill Handle icon to apply the formula as we learned from the first approach.

  • You’re all set!

📝 Takeaways from This Article

📌  First of all, we learned how to use the Fill Handle icon.

📌  Secondly, we demonstrated how to use the Fill option from the Excel Ribbon.

📌  Thirdly, we implemented Power Query to fill down to the last row with data.

📌  Later, we used the Go To Special dialog box to identify the blank cells and filled those with data.

📌  After that, we ran a VBA code to execute fill down to last row with data.

📌  Finally, we showed how to fill down to the last row with random numbers using the RANDBETWEEN function.


Conclusion

That concludes the discussion for today. These are some convenient methods to fill down to the last row with data. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.

(Visited 93 times, 1 visits today)
Nazmus Sakib

Nazmus Sakib

Hello, and thanks for visiting my profile. Right now I am researching Microsoft Excel, thus I will be sharing articles about myriad ways to utilize Excel effectively here. I love to watch movies and TV series in my free time. I am also a huge football fan. My priority is to continually broaden my horizons in order to enrich my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo