If you copy and paste values from one column to another, when the filter is on and the range is discontinuous, ┬áthen it won’t work and will paste the data into non-visible cells as well.


As an example, I have taken some values in column A and B as shown below:

Excel Filters
Now if I apply filter on the data and deselect say 2 and 4.

filter_2.JPG

Now I want to replace 8 and 10 with 3 and 5 and want the values in row 4 to remain as they are.

filter_31.JPG

Now if I simply copy 3 and 5 from column A and paste it in column B, then as you can see it will replace 8 and 9 with 3 and 5 and not 8 and 10.

filter_4.JPG
Even if you copy 3 and 5 and then select 8 and 10 and using F5 do visible cells only and then try and paste, it will not work and the following window will pop up.

filter_6.JPG

To get around this, just type =A3 in cell B3 and drag it down. If you want to do it all in one go, simply select 8 and 10 and type = A3 in cell B3 and hit CTRL+Enter.

filter_7.JPG

Now as you can see below, this will paste data only into the visible cells.

filter_9.JPG

Share This

Popularity: 30%



Reader's Comments

  1. Bonnie | May 6th, 2009 at 3:57 am

    Very useful!!!! Thanks for sharing!

  2. shanthi mohan | May 9th, 2009 at 2:53 am

    Hi

    This information was really useful. Thanks a ton! I have also tried this mutliple columns it is working fine. Only thing is after the copy paste, please remember to reset the data as values using Paste Special.

  3. Jagathis | July 2nd, 2009 at 12:59 pm

    Thanks a lot for this tips. Very much useful.

  4. John | January 19th, 2010 at 11:20 pm

    The only problem with this method is if your data will be used in a Pivot Table. The Pivot Table will recognize the formula as a formula (=A3), and not a number or text. If the result of the formula is a date (=A3, & A3= xx/xx/xxxx) the Pivot Table will not display the date because the Pivot Table is reading it as an actual formula and not the resultant data.

  5. 80s | March 19th, 2010 at 6:14 pm

    To comment off of John response, all you have to do is reselect the fields that have formulas copy it and right click and select paste special and change it to “values” and the formula will no longer be displayed

  6. Devendra | March 23rd, 2010 at 4:12 am

    Thanks a lot man.
    it was tricky!!!

  7. jamie | December 3rd, 2010 at 4:51 pm

    very useful thanks a lot.
    Re: Johns comments… this will not happen if you follow the instructions as per Shanthi’s post…

    afterwards you need to ‘reset’ the cells as values as apposed to formulas, you do this by copying the cells and then using paste special and choosing values. This also prevents the data being lost if the original cells are removed.

Leave a Comment

Close
E-mail It