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.
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).