A blank cell has no content in it. So, it’s obvious that you might not want to copy a blank cell. Rather, you may want to copy the contents of the adjacent cell having meaningful contents. In this tutorial, you will learn 3 methods to check if a cell is blank and then copy another cell in Excel with ease.
If a Cell Is Blank then Copy Another Cell in Excel: 3 Methods
1. Use IF Function to Copy Another Cell If a Cell Is Blank
We want the Employee IDs to be copied to the Output column. If any of the Employee IDs are missing, then we will copy the Passport IDs to the Output column.
Now follow the steps below to do that.
❶ First click on the top cell of the Output column.
❷ After that insert the following formula of IF in that cell.
=IF(B5="",C5,B5)In this formula,
- B5=”” checks whether the top cell of the Employee ID column i.e. cell B5 is blank or not.
- C5 denotes the cell to copy if cell B5 is blank.
- B5 will be copied if B5 is not blank.
❸ After that hit the ENTER button.
Now it’s time to copy the above formula to the rest of the cells of the Output column. To do so,
❹ Put your mouse cursor to the right-bottom corner of cell C5.
A small plus icon called the Fill Handle will appear.
❺ Double-click on the Fill Handle icon or just drag it to the end of the Output column.
Now you will see the Employee IDs are copied to the Output column. Where the Employee IDs are missing, the Passport IDs have been copied instead.
Read More: If Cell is Blank Then Show 0 in Excel
2. Combine IF and ISBLANK Functions to Copy Another Cell If a Cell Is Blank
In this method, we will write a formula combining the IF and the ISBLANK function to check if a cell is blank or not. If the cell is blank, the formula will copy the cell content of the right-next cell.
To do that,
❶ First of all, click on the topmost cell of the Output column.
❷ After that, insert the following formula in that cell.
=IF(ISBLANK(B5),C5,B5)Where,
- ISBLANK(B5) checks whether cell B5 is blank or not.
- C5 is the cell reference, from where the formula copies data if cell B5 is blank.
- B5 refers If cell B5 is not blank, the formula will copy data from cell B5.
❸ Now press the ENTER button to execute the formula.
❹ Place the mouse cursor on the right-bottom corner of the topmost cell of the Output column.
A small plus icon will appear.
❺ Double-click on the plus icon or simply drag down the plus icon to the end of the Output column.
Now you will see the Employee IDs in the Output column. If any Employee ID is missing, the corresponding Passport ID will appear in the Output column.
Read More: How to Find If Cell is Blank in Excel
3. Use Excel VBA Script to Check If a Cell Is Blank, then Copy Next Cell
In the following dataset, there are two columns. One is Employee ID and the other is Passport ID.
Now we will write a Visual Basic script that will check if any of the cells of the Employee ID is blank. If it’s blank then the script will copy data from the adjacent cell to fill it up.
To do that,
❶ Press ALT + F11 keys to open the VBA editor.
❷ Create a new module from Insert > Module.
❸ Copy the following VBA code.
Sub FillTheBlanks()
Application.ScreenUpdating = False
Dim x As Range
For Each x In Range("B5:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If x.Value = "" Then x.Value = x.Offset(, 1).Value
Next
Application.ScreenUpdating = True
End Sub❹ Paste and save the above code in the VBA editor.
❺ Now hit the F5 key from your keyboard to run the VBA code.
After pressing the F5 key, you will see the blank cells of the Employee ID column are filled will the data from the adjacent cells of the Output column.
Read More: How to Check If Cell Is Empty Using Excel VBA
Things to Remember
- To open the VBA editor, press ALT + F11 keys together.
- Press F5 to run the VBA codes.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 3 methods to check if a cell is blank and then copy another cell in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP. And please visit our website Exceldemy to explore more.
Related Articles
- How to Calculate in Excel If Cells are Not Blank
- How to Return Value If Cell is Blank
- How to Apply Conditional Formatting in Excel If Another Cell Is Blank
- Excel If Two Cells Are Blank Then Return Value
- How to Check If Cell Is Empty in Excel
- Excel VBA: Check If Multiple Cells Are Empty
- How to Find & Count If a Cell Is Not Blank
<< Go Back to If Cell is Blank Then | Blank Cells | Excel Cells | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
