Nazmus Sakib
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.
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.
- 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.
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.
- 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.
- 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.
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.
- 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).
- The Power Query Editor will open.
- 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.
- A new worksheet will contain the filled data.
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
- Now press Ctrl+Enter together.
- All the blank cells will now accommodate the upper cell’s value.
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
- 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.
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)
- 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.
