SubscribeSkip Blank doesnt skip blank cells ?!?
In our excel paste tricks post, I have mentioned a paste special feature called “skip blanks” that can apparently be used to skip blank cells when pasting data. I am writing about this again because, I have received an email from Bruce saying,
This is erroneous. In actuality, the result that is pasted is the same size as what was copied, only in those cell references that were copied that happened to be blank, the destination cell references aren’t “written over”
and he is correct, I am wrong. I am sorry for this mistake. For some reason I didn’t test this tip while writing, I some how thought excel skips blank cells while pasting and shared the tip with you all. My mistake and thanks alot to Bruce for teaching me this tip in the correct way. I test all the tips posted here on at least one version of excel, this was an exception, I am hoping it will not be repeated.
Just in case you want to skip blank cells, here is a work around.
Apply data filters on the range from which you want to remove blanks, filter by non-blank cells and select it. Press ctrl+c and paste it wherever you want. Excel pastes only cells matching the filter criteria (thus skipping blanks)
PS: I have corrected the post too. The e-book will be corrected later on.
|
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Adjust width of Column Shortcut Alt,...
Hey, it happens to all of us. You are doing a wonderful job.
paresh
Why are you hoping it will not be repeated?
Say you’re sure it won’t be
Another way to skip blanks in the copied range is to hold the Ctrl key while selecting multiple ranges. When pasted, the separate areas will be pasted as a single range.
That’s okay chandoo. Even I thought it works in this way, but it didn’t.
Chandoo, you mentioned that we can use apply data filter to get this done. But we can’t apply data filter on all kinds of cell ranges in excel. Also, can u explain what exactly “skip blanks” is, how and where is it used.
Chandoo, is there a way for me to skip blank cells in validation list? Blank always appear in the pull down menu as a selection if there is blank cells available in the list. Please help. Thanks.
Hey, I figured this out. Paste special – skip blanks avoids replacing values in your paste area when blank cells occur in the copy area.
I found this in the help file. This is helpful when you want to merge two sets of data.
Hi Chandoo,
thanks for your excellent posts.
I’ve got a question related to how Excel handles the date ranges. I’ve got a cell with the custom format dd-mm ( 16/03 – 22/03 ), and I’d like to have the following cells filled with the subsequent dates week by week, I mean 23/03-29/03, 30/03-05/04 and so on…
do you know what do I have to do to get the complete series ?
Thanks a lot!
@Sanwijay: using data filters is the simplest way, but it doesnt works for all types of data. You can try using ctrl+ selection as Jon pointed. One more way is to use formulas, but it is kind of painful. For some ideas you can refer to our unique items using formulas post: you can change the formula slightly to return non-blank items intead of unique items.
http://chandoo.org/wp/2008/11/06/unique-duplicate-missing-items-excel-help/
@Miguel: there is an option to skip weekends while filling dates. It skips both saturday and sunday.
http://chandoo.org/wp/2008/06/30/skip-weekends-while-autofill-dates-in-excel-howto/
If you want to instead skip only sunday, you can also use formulas. Assuming you have manually entered the date in first cell, say D2, then you can enter a formula like, =IF(WEEKDAY(D2+1)=1,D2+2,D2+1)
hi, this is Dave, and i have a question, i want to make a formule where after to capture a date1 in a cell (C4) this appear in other cell (i. e. F7), to the next time when i capture a new date2 in the cell (C4) this appear in the cotinue cell of F7 (i. e. F8 or G8), could you aid me. thx
Dave.