May 11th 2022

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.

The Ultimate Guide to Cloud Gaming: D…
best projectors for home
The Advantages Of Playing Online Casi…
Vitamins That Help With Weight Loss
Understanding the Software and Apps o…

Download Practice Workbook

Download this practice workbook

Fill Blank Cells with Value from Left.xlsm

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

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.

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.

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.

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

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

4. Embedding VBA 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 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.

This will fill all the blank cells of a sheet with values from the 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.

The post How to Fill Blank Cells with Value from Left in Excel (4 Suitable Ways) appeared first on ExcelDemy.

This post first appeared on ExcelDemy.com, please read the originial post: here