How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways)

At times, you can work in a dataset that has empty cells and you need to take the exact value from the left cell. In that case, you can do it manually but it is very time-consuming. Excel gives you a platform where you can fill your blank cells by using some Excel commands and VBA. This article will mainly focus on how to fill blank cells with a value from the left in Excel.


Download Practice Workbook

Download this practice workbook


4 Easy Ways to Fill Blank Cells with Value from Left in Excel

We have found four useful methods to fill blank cells in Excel with values from the left. All the methods are relatively easy to digest. These methods consist of some Excel commands and VBA. To show all the methods effectively, we take a dataset that includes some products, their tag price, and final price. Some of the products have similar tag prices and final prices. In that case, we leave some final prices blank.

Fill Blank Cells in Excel with Value From Left


1. Fill Blank Cells with Value from Left Using Fill Handle in Excel

The easiest way to fill blank cells in Excel with values from the left is by using the Fill handle. In this method, you can only drag the cell from the left to the desired cell. To use this method, follow the next steps.

Steps

  • Here, we have a blank cell F6. Now, select cell E6 and in the bottom right corner you will get a Fill Handle Icon.

Fill Blank Cells with Value from Left Using Fill Handle in Excel

  • Drag this icon to our desired cell F6.

  • Do the same for all other blank cells. Then, we have a complete dataset with no blank cells.

Fill Blank Cells in Excel with Value from Left


2. Utilizing Go To Special Command in Excel

Our next method is based on the Go To Special command. We need to use the Fill Handle icon for each cell in the previous method. But this method will help you to fill blank cells at a time. To use this method, follow the following steps.

 Steps

  • First, select the entire dataset.

  • Next, go to the Home tab in the ribbon and select Find & Select option from the Editing group.

  • From the Find & Select option, select Go To Special.

Fill Blank Cells with Value From Left Using Go To Special Command

Keyboard Shortcut

Press Ctrl+G’ or press F5 to open Go To dialog box. From there, select Special.

  • After that, a Go To Special dialog box will appear. From there, select Blanks and click on OK.

Fill Blank Cells with Value From Left Using Go To Special Command

  • This will highlight all the blank cells.

 

  • Next, in the blank cell F6, write down the following formula.

=E6

  • Now, press Ctrl+Enter’ to apply this formula to all other blank cells. If you press only Enter, then it will just fill the formula in all blank cells.

Fill Blank Cells in Excel with Value from Left


3. Find and Replace Command to Fill Blank Cells with Value from Left

Next, we can fill all blank cells of a sheet with values from the left by utilizing Find and Replace command. This method is quite similar to the previous method. Here, we need to select blank cells utilizing the Find command and then apply the formula.

Steps

  • Select the entire dataset where you have some blank cells.

  • Next, go to the Home tab in the ribbon and select Find & Select option from the Editing group.

  • From the Find & Select option, select Find.

Utilizing Find and Replace to Fill Blank Cells with Value from Left in Excel

  • A Find and Replace dialog box will appear. Leave the Find What option blank and then click the Find All option.

  • That will find out all the blank cells in the dataset. Now, press Ctrl+A’ to select all the blank cells and click on Close.

  • This will highlight all the blank cells and show them on your dataset.

  • Now, select cell F12, and write down the following formula.
=E12

  • Now press Ctrl+Enter’ to apply this in all the blank cells.

Fill Blank Cells in Excel with Value from Left


4. Embedding VBA Code in Excel

In the previous two methods, we need to select all the blank cells and then apply a formula to fill them. But we can use VBA where you don’t need to do any further things. Just use VBA and run that will eventually fill blank cells.

Steps

  • To apply the VBA, you need to open the Developer tab. You can open it by pressing Alt+F11′ or you can customize your ribbon section and get the Developer This will open up the Visual basic interface.

  • Next, go to the Insert tab and select Module.

  • A Module code window will occur. In there, copy the following code and paste it.
Sub FillCellFromLeft()
Dim p As Range

For Each p In Selection
    If p.Value = "" Then
        p.Value = p.Offset(0, -1).Value
    End If
Next p
End Sub

Breakdown of the VBA Code

  • Here cell.Value = “” denotes the blank cells in your dataset.
  • Value = cell.Offset(0, -1).Value demonstrates that the blank cells will offset the left column values. You can change the offset value (-1,0) which denotes the blank cell acquires the value of the above row.
  • Now, close the Visual Basic code window.
  • Now select the whole dataset.

  • Go to the View tab in the ribbon and select Marcos.

  • A Marco dialog box will appear. Select the FillCellFromLeft option from the Marco Name and click on Run.

Embedding VBA Code in Excel

  • This will fill all the blank cells of a sheet with values from the left.

Fill Blank Cells in Excel with Value from Left


Conclusion

To fill blank cells in Excel with values from the left, we have four useful ways through which you can do the work. All four methods including the VBA are fairly easy to understand. If you need anything, feel free to ask in the comment section, and don’t forget to visit our Exceldemy page.

Durjoy Paul
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo