How to copy and paste only non-blank cells in MS Excel

Lot of time we need to copy selected range of cells along with blank cells and paste them to filled cells. At that time all data is overwrite with blank cells. At this situation you must need to know how to copy and paste only non-blank cells in MS Excel. This article helps you to quickly copy and paste only non-blank cells in MS Excel with few simple steps.

When you are doing work in your office or at home we need to proper manage data in active worksheet of MS Excel. If you want to copy range of continuous cells which have blank cells also and when you paste them to another location, it will overwrite the previous filled data. At this situation either you have to manually type one by one data at another place or copy and paste small range of cells. It will not only consume lot of time but also you will be frustrated. At this situation you need to know easy and quick way which solved your problem. In this article we have mentioned two methods: Go To Special and Filter which helps to solve your problem.

How to copy and paste only non-blank cells in MS Excel:

Before start the process how to copy and paste only non-blank cells in MS Excel first we need to know importance of “Go To Special” and “Filter” commands. Go To Special is an important command of Microsoft Excel which enables you to quickly select cell which have any type of data, comments, formulas and many more. While Filter command enable you to quickly turn on filter options on selected range of cells. After that you will choose any specific data as per your need.

Must Read: Prevent duplicate values with Data Validation in MS Excel

There are two methods given in this article which helps you to easily copy and paste only non blank cells in MS Excel. You have to take few simple and easy steps which is given below.

Method 1: With the help of Go To Special command:

If you want to copy and paste only non blank cells in MS Excel with the help of Go To special command then you just take few simple steps:

How to copy and paste only non-blank cells in MS Excel

Step 1: Select the range of cells in which you have lot of blank cells.

Step 2: Click on the Home tab > Click on the Find & Select command > Click on the Go To Special… command or you can press Ctrl+G. You can also press F5 function key to quickly active Go To Special dialogue box in MS Excel.

Step 3: Select “Constants” option in the Go To Special dialogue box and click on the OK command.

Step 4: Click on the copy command or you can also press “Ctrl+C“. Now, take cursor on another location click on the Paste command or you can also press “Ctrl+V“.

Must Read: How to launch camera by double tap on Android phone

Method 2: With the help of Filter command:

If you want to copy and paste only non blank cells in MS Excel with the help of filter command then you just take few simple steps:

How to copy and paste only non-blank cells in MS Excel

Step 1: Select the range of cells whose non blank cells you wan to paste at another location.

Step 2: Click on the Home tab > Sort & Filter > Click on the “Filter” command, or Data tab > Filter command. You can also press Ctrl+Shift+L to quickly active filter option on range of selected cells.

Step 3: Click on the drop down arrow of product name and turn off (Blanks) option and click on the OK button.

Step 4: Press “Ctrl+C” to copy the range of cells. Now, take cursor at another location and press “Ctrl+V“.

Must Read: Easy Excel IF Formula for Income-Tax Calculation AY 2015-16

I hope after reading this article you problem will be solved and you will be able to know how to copy and paste only non-blank cells in MS Excel. If you have any suggestion regarding this article then write us in the comment box. Thanks to all.

Leave a Reply

Your email address will not be published. Required fields are marked *