How to copy and paste skip blank cells in Excel?
Normally, when we copy some data cells in Range A which contains blanks and paste them into another Range B, the blank cells will be pasted as well, and the original data of the Range B will be missed. See following screenshots:
This will be annoying for us to miss some data, to avoid this, Excel provides a feature – Skip blanks for us to solve this problem.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The Skip blanks feature in Excel allows us to copy a data range with blank cells and paste the data within another area without overwriting the existing data with blanks. Please do with following steps:
1. Select the data range that you want to copy, and then press Ctrl + C to copy it.
2. Then select another range or cell that you need to paste the data, and then right click, choose Paste Special > Paste Special from the context menu, see screenshot:
3. In the Paste Special dialog box, check Skip blanks option, see screenshot:
4. Then click OK button, and your copied data of Range A has been pasted to Range B without the blank cells. See screenshot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoI should add that when I use the GO TO dialog box then SPECIAL and choose BLANKS, a message pops up and tells me there are no blanks even though there are. When I click on one of these "blank" cells there is nothing in the cell or in the display field up top where the cell contents also show.
To post as a guest, your comment is unpublished.· 2 months agoHello,
The “skip blanks” feature in the Paste Special dialog box does not work for me. I have a list of names in Column B and the person’s STATUS in column H. If the person has a PAID status in Column H, I used this formula to extract the name from Column B: =IF(H6="PAID",B6,"")
There are over 600 names with PAID and NONE statuses. The formula worked perfectly. Now I need to copy and paste those names into another column so only the values show.
I copied the names (which really are just the formula that produced the name) and used the Paste Special dialog box to paste that data into another column using the option to paste the values only. I checked the “skip blanks” option and hit OK.
The blanks appeared as well. I tried changing the formula to =IF(H6="PAID",B6," ") so there is a space between the two italics and that did not help. I tried =IF(H6="PAID",B6, ) just leaving the “if false” section completely blank and I got the same results.
I sorted the data which got rid of the blanks but now the names were out of order due to some cells containing Mr or Mrs or no title.
ANY HELP would be appreciated. I'm using MS Excel for Office 365.
Thanks in advance.