Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Copy and Paste Skip Blank Cells in Excel?
Generally, in Excel, a list may or may not contain empty cells, and when we want to copy the values, the blank cells will also be copied by default. If we need to copy only the cells with data in them by skipping the blank cells, we need to use a special process. If we try to do this manually, it can be time-consuming.
Read this tutorial to learn how you can copy and paste in Excel without skipping blank cells. We can complete this task in a faster and more efficient way by using the array formulas.
How to Copy and Paste Skip Blank Cells in Excel
Here, we will first use the array formula to get the first value, then fill the whole list using the auto-fill handler. Let's go over a simple procedure for using formulas to copy and paste blank cells in Excel. The formula we're using is an array formula with the LOOKUP function. LOOKUP is one of the reference functions. It is used when you need to look in a single row or column and find a value from the same position in a second row or column.
Step 1
Consider an Excel sheet with data in the form of a list and empty cells between the elements of the list, as shown in the image below.
Now, in our case, cell B2, enter the formula as follows.
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF($A$2:$A$14<>"",ROW($A$2:$A$14)),ROWS($B$2:B2))))) and click CTRL + SHIFT + ENTER to get our first result, which looks similar to the below image. In the formula, A2:A14 is the range of cells you want to copy, and B1 is the address of the current cell.
Empty cell > Formula > CTRL + SHIFT + ENTER
Step 2
Then drag down from the first result till empty cells are printed, and our final result will be similar to the below image.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can copy and paste blank cells to highlight a particular set of data in Excel.
1K+ Views
To Continue Learning Please Login