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.
How to Fill Blank Cells with Value from Left in Excel: 4 Easy Ways
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 in blank cells at a time. Let’s follow the instructions below by using the Go To Special command to fill blank cells with values from the left.
Steps
- First, select the entire dataset.
- Next, go to the Home tab in the ribbon and select the 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 the 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.
Read More: How to Fill Blank Cells with Value Below in Excel
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 the 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. Go through the process below to use the Find and Replace command to replace blank cells with values from the left.
Steps
- Select the entire dataset where you have some blank cells.
- Next, go to the Home tab in the ribbon and select the 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.
=E12
- Now press ‘Ctrl+Enter’ to apply this in all the blank cells.
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 SubBreakdown 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.
- This will fill all the blank cells of a sheet with values from the left.
Read More: How to Fill Blank Cells with Value Above in Excel VBA
Download Practice Workbook
Download this practice workbook
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.
