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.

Use IF Function to Check If a Cell is Blank then Copy Another Cell in Excel

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

Check If a Cell is Blank then Copy Another Cell in Excel

Read More: If Cell is Blank Then Show 0 in Excel


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.

Combine IF and ISBLANK Function to Check If a Cell is Blank then Copy Another Cell in Excel

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

If a Cell is Blank then Copy Another Cell in Excel

 


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.

Use VBA Script to Check If a Cell is Blank then Copy Another Cell in Excel

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

Read More: How to Check If Cell Is Empty Using Excel VBA


Download Practice Workbook


Related Articles


<< Go Back to If Cell is Blank Then | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mrinmoy Roy
Mrinmoy Roy

Mrinmoy Roy, a dedicated professional with a BSc in Electronics and Communication Engineering from Khulna University of Engineering & Technology, Bangladesh, brings over two years of expertise to the ExcelDemy project. As a prolific contributor, he has authored around 180 articles, showcasing his deep knowledge and passion for Microsoft Excel, Data Analysis, and VBA. His unwavering commitment to continuous learning, combined with versatile skills, renders him well-suited for roles in data management and spreadsheet solutions. He has interest... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo