If a Cell Is Blank then Copy Another Cell in Excel (3 Methods)

Method 1 – Use IF Function to Copy Another Cell If a Cell Is Blank

In the sample dataset, we want to copy the Employee IDs to the Output column. If any of the Employee IDs are missing, then we want the Passport IDs to be copied to the Output column.

❶ Click on the top cell of the Output column.

❷ Insert the following IF formula 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.

❸ Hit ENTER to execute the formula.

❹ Put the 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.

The Employee IDs are copied to the Output column. Where the Employee IDs are missing, the Passport IDs will be copied instead.

Method 2 – Combine IF and ISBLANK Functions to Copy Another Cell If a Cell Is Blank

❶ Click on the top cell of the Output column.

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

❸ Hit ENTER to execute the formula.

❹ Place the 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.

The Employee IDs will be copied to the Output column. If any Employee ID is missing, the corresponding Passport ID will be copied instead.

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

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

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

Download Practice Workbook

If Cell Is Blank then Copy Another Cell.xlsm