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.

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.

• 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.

Read More: How to Fill Blank Cells with Value Above in Excel VBA (3 Easy Methods)

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.

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

• This will highlight all the blank cells.

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

• 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.

Read More: How to Fill Blank Cells in Excel with Go To Special (With 3 Examples)

Similar Readings

• How to Return Value if Cell is Blank (12 Ways)
• Find, Count and Apply Formula If a Cell is Not Blank (With Examples)
• How to Highlight Blank Cells in Excel (4 Fruitful Ways)
• Null vs Blank in Excel
• How to Delete Blank Cells in Excel and Shift Data Up

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.

• 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.

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

Read More: How to Find and Replace Blank Cells in Excel (4 Methods)

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

• Now, close the Visual Basic code window.
• Now select the whole dataset.

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

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

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

Read More: Excel VBA: Check If Multiple Cells Are Empty (9 Examples)

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.

Related Articles

• How to Fill Blank Cells with 0 in Excel (3 Methods)
• How to Remove Blank Cells Using Formula in Excel (7 Methods)
• Fill Blank Cells with Dash in Excel (3 Easy Ways)
• How to Fill Blank Cells with Color in Excel (5 Methods)
• How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
• If Cell is Blank Then Show 0 in Excel (4 Ways)