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.
26 Responses to “Skip Blank doesnt skip blank cells ?!?”
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.
I just tried what you said. It seems not to be working.
@Michael
Can you please be a bit more specific
Can you tell us where it stops working ?
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.
A better workaround for pasting a formula but skipping blanks:
1) Click "copy" on the formula you would like to copy
2) Highlight the area where you would like to paste (including blanks which you would not like to paste in)
3) Press F5
4) Click "Special"
5) Click on either "constants" or "formulas" and this will highlight cells with formulas or constants (no blanks)
6) special paste formulas
Because only the cells with formulas or constants are highlighted, the paste will skip blank cells
Skip Blank trick not work please explain properly.
The #10 response by Will is the easiest way to get rid of blank cells -- either blank cells off your current worksheet or blank cells in a range of cells that you want to copy and paste.
Let's say Column A has a lot of blank cells mixed among your data and you want to eliminate the blanks so that, when finished, you only want Column A to show cells with data in them, one after another without blanks. First highlight/select column A, or any range of cells in Column A. Press the F5 key. When you press the F5 key, a little window entitled "Go to" will appear. There will be a box in the lower left of this "Go to" window entitled "special." Click on this ":special" box. When you click on it, another little window entitled "Go To Special" will appear. Inside this "Go To Special" window at the top is the word "Select" and then a list of options that you
can check. Two of the options are "constants" and "blanks." Check "blanks." Now all the blank cells in Column A will be selected or highlighted. You can now use the "Edit-Delete" command at the top of your worksheet to delete the selected blank cells and "shift upward" the remaining cells. The result will be that all the blank cells in Column A will be gone -- only the cells with data in them will be left.
If instead of just eliminating blank cells on your current worksheet, you want to copy Column A to another worksheet without the blank cells, follow the same procedure as above but in the "Go To Special" window, select "constants" instead of "blanks." This will select/highlight only those cells in Column A that have data in them. After they are selected/highlighted, edit-copy the selected cells to the new worksheet and all the data will appear without the blank cells.
These two procedures are very useful when you have a large amount of data and it is too cumbersome to use the ctrl key to individually select each cell with data you want to copy. If you only have several cells with data mixed in among blank cells, you can hold down the control key and serially select each data cell, edit-copy the selection, and the pasted selection will have no blank cells.
Perhaps an easier way to select all the blanks in Column A or within a range in Column A... first make your selection, then press the following keystrokes...
All the blank cells within your original selection (within the Used Range) will now be selected. By the way, if there is nothing in the "Find what" field when the dialog box appears, you can omit pressing the key if you want. To select all the non-blank cells within the range of cells, make your selection and then press the following keystrokes instead...
Note, Shift+8 is the asterisk (*) keystroke.
Heh-heh, I forgot, you can't use paired angle-brackets in this comment processor, so I'll use square-brackets instead...
Perhaps an easier way to select all the blanks in Column A or within a range in Column A… first make your selection, then press the following keystrokes…
[Ctrl+f] [Delete] [Alt+i] [Ctrl+a] [Esc]
All the blank cells within your original selection (within the Used Range) will now be selected. By the way, if there is nothing in the “Find what” field when the dialog box appears, you can omit pressing the [Delete] key if you want. To select all the non-blank cells within the range of cells, make your selection and then press the following keystrokes instead…
[Ctrl+f] [Shift+8] [Alt+i] [Ctrl+a] [Esc]
Note, Shift+8 is the asterisk (*) keystroke.
Thanks for the tip, I will use "apply filter" to skip blanks.
Thanks Will!
Chandoo,
I still have problems with your tip on pasting tricks, I'm using Excel 2010. In the tip to eliminate blanks, you say to "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)."
It turns out you cannot paste the data wherever you want. If, for example, the data is in column A (from A3 - A11), you cannot paste the data in any column location between rows 3 to 11, or you'll be missing data in the cells that had the original blanks.
I had the problem of data "disappearing" when trying to paste as well. I'm still confused as to which rows it is eliminating, but pasting below the table solved my problem. I assume pasting in another tab would work too.
Hey Chandoo,,
I want to paste value in one after one cell. please suggest
I am trying to match two Values by VLOOKUP, these are Arabic letters + numbers,
I have pasted these values for your reference, please anybody can advice how can i match them
Value as per sheet 1 Value as per sheet 2
? ? ? 845 845 ? ? ?
How can i match them through vlookup
Value 1 : A B C 123
Value 2 : C B A 123
This is just an eg, I am using ARABIC letters instead of English letters and they are reversed due to conversion.
Now i have to match them through VLOOKUP please advice
LOVE your blog! Super helpful (even when a rare mistake is made).
A simple way would be to Sort the data and paste wherever you want. Also, in case you want to maintain the original data, just paste and sort afterwards.
Hi Sir,
I want to link two cell in excel which will be inter-dependent of each other, I can't left blank if another cell has data.
Ex. I have two cell named "A1" & "B1", Cell A1 has Vehicle Type - Market & Own, Cell B1 has Freight Amount, now I want to set as : If cell A1 has Market then B1 must have some value and I can't skip it blank.