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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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:
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.· 7 months agoNOT WORKING ON NUMBERS !!!WORKS FINE ON TEXT
- To post as a guest, your comment is unpublished.· 1 years 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.· 1 years 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.