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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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.

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

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.

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

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

If a Cell is Blank then Copy Another Cell in Excel

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.

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

❺ 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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