Advance to blank cells in a worksheet in Excel

Question

How do I fill blanks in a column with the previous cell contents?

Answer 1

Hi,

• Select all cells you want (in my case cells from A2 to A20)
• Press F5 from keyboard
• Click Special button
• Click Blanks
• Click Ok
• Place the equal sign in the first empty cell
• Hold down Ctrl key on the keyboard (continue to hold this button down until the end of the following procedure)
• Click on the first cell that is not empty
• Press the Enter key on the keyboard

If you want the solution via a formula, then in an empty cell (for example in cell B2) apply one of the following three formulas:

=LOOKUP(ROW(A2:A19), ROW(A2:A19) / (A2:A19 <> ""), A2:A19)

=BYROW(A2:A19, LAMBDA(a, XLOOKUP(TRUE, A2:a <> "", A2:a,,, -1)))

=SCAN("", A2:A19, LAMBDA(a,b, IF(b <> "", b, a)))

Hope this helps. If so, please mark my answer as a Valid Answer and kindly upvote it, as this way other users of this forum who have the same question as you can benefit.

IlirU

Answer 2

Dear @Terri Rogers,

Thank you for reaching out to the Microsoft Q&A forum. I understand how time-consuming it can be to manually fill in missing data in a large worksheet. This is a common challenge when working with exported reports where the data only appears in the first row of a group. 

I have a very efficient method for you to fill all blank cells with the value from the cell directly above them in just a few steps. 

To help me narrow down the cause, could you please provide the following details?  

• What version of Excel are you using? 
• Does this issue occur in other Excel files with multiple sheets as well? 
• Have you tried using Excel in safe mode to see if the problem persists there? 
• Are there any specific error messages when trying to navigate to the found cells? 

In the meantime, please try the following official troubleshooting steps which usually resolve issues with basic issues: 

Step 1: Fill a Formula Down Instantly (The Double-Click Trick) 

If you have a formula in the top cell and want to apply it to the entire column based on the data next to it: 

• Select the cell with the formula. 
• Double-click the small green square (the Fill Handle) in the bottom-right corner of the cell. 

Result: Excel will automatically "shoot" the formula down to the last row of your data range. 

Fill data automatically in worksheet cells - Microsoft Support 

Step 2: Fill Data Automatically (Flash Fill) 

If you are trying to split or combine data (e.g., extracting first names from a full name list): 

• Type the first one or two examples manually in the adjacent column. 
• Press Ctrl + E on your keyboard. 

Result: Excel senses the pattern and fills the rest of the column instantly using Flash Fill. 

Step3: Use the "Fill" by VBA 

Filling blank cells in a column with the value from the cell above is a classic Excel task, you can tackle it with a tiny VBA macro. Here’s a quick way based on Microsoft’s documentation: Fill a Value Down into Blank Cells in a Column | Microsoft Learn 

1. Press Alt + F11 to open the VBA editor. 
2. Insert a new module (Insert > Module). 
3. Paste this code into the module window: 

      Sub FillValueDown() 
          Dim ws As Worksheet 
          Dim lastRow As Long 
          Dim rng As Range 
          Dim cell As Range 
       
          ' Change “Sheet1” to your sheet name and “A” to your target column 
          Set ws = ThisWorkbook.Worksheets("Sheet1") 
          lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 
          Set rng = ws.Range("A2:A" & lastRow) 
       
          For Each cell In rng 
              If IsEmpty(cell) Then 
                  cell.Value = cell.Offset(-1, 0).Value 
              End If 
          Next cell 
      End Sub 
   

4. Close the VBA editor. 
5. Back in Excel, run the macro (Alt + F8 → select “FillValueDown” > Run). 

This loops down column A from row 2 to the last used row; whenever it finds an empty cell, it copies in the value from the cell above. You can adjust the sheet name and column letter as needed. 

I hope these techniques save you a significant amount of time. Thank you for your understanding and cooperation. Please let me know how it goes after trying the steps above. I’m here to help further if needed.  

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.