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

Get FREE Advanced Excel Exercises with Solutions!

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

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.

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

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


Similar Readings


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

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

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

  • A Macro dialog box will appear. Select the FillCellFromLeft option from the Macro 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

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

Durjoy Paul

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo