Paste With Shortcut in Filtered Excel List

paste with shortcut in filtered Excel list

Last week, I shared a couple of workarounds for pasting data into a filtered list. Today, see how to paste with shortcut in filtered Excel list.

Simple Paste in Filtered List

If you use a simple copy and paste, you could end up overwriting data in the hidden rows.

filtercopypaste02

You can use those workarounds, if you are pasting into the filtered list, with data from another location. However, if you are copying and pasting data from one column to another, within the filtered list, there is a better solution.

Paste With a Shortcut

Instead of using one of the workarounds for copying and pasting, you can use a keyboard shortcut. It’s much quicker and easier!

This tip was shared by Khushnood Viccaji, in a comment on my previous post. Thanks for the tip – it’s a new one for me, and a real time-saver!
To copy data into the same rows in a filtered list:

  • Select the cells that you want to copy
  • Press Ctrl and select the cells where you want to paste (in the same rows)
  • To select only the visible cells in the selection, press Alt + ; (the semi-colon)
  • To copy to the right, press Ctrl + R

The data is magically copied to the visible cells on the right, even though the columns are not adjacent

filterpastefillright01

Video: Paste With Shortcut in Filtered Excel List

To see the steps for pasting into the same row in a filtered list, please watch this short video tutorial. You’ll also see how to fill data to the left in filtered rows.
The video transcript is further down on this page.

Get the Sample File

To download the sample file for this video, go to the Filtered List Pasting Problems page on my Contextures website.

For more time-saving tips, go to the Data Entry Tips page on my Contextures website. There are videos, screen shots and written instructions there, to help you get your Excel work done.

And for a full list of Excel keyboard short cuts, go to my interactive Shortcuts List.

Video Transcript

Here is the full transcript of the video, shown above.

Introduction

In my previous video, I showed you the problems you can run into when you try and copy and paste in a filtered list.

For example, if I filter this list to only show the chocolate chips, and then try to copy these dates, and paste them into this column, it doesn’t paste correctly.

So I’m going to do a copy (we can see they’re copied here) and then paste, and only two of them show up. The rest have been pasted in the background, in the hidden rows.

So I’m going to undo that, and I’m going to show you a tip that I learned from my friend, Khushnood Viccaji, and it makes copying and pasting much easier.

Select Visible Cells

First, I’m going to select the cells that I want to copy.

Then press Ctrl and select the cells where you want to paste the data.

To get this information from here to here, we’re going to make sure only the visible cells are selected.

So press the Alt key and semi-colon(;)

And now only the visible cells in our selection are selected, and you can see little lines to separate them.

Copy to the Right

Now to get from here to here, we’re going to fill, and we can fill to the right.

So I’m going to use the keyboard shortcut, which is Ctrl + R and it takes this data, and puts it in the cell to the right.

Even though it wasn’t to the immediate right, that shortcut works well for getting information to the next cell that’s selected on the right.

So none of the other data was overwritten. We can see that all the data went into the correct cells.

Now in this case, we were filling to the right, so we could use a keyboard shortcut.

Copy to the Left

But if these dates weren’t filled in and we wanted to copy from here to the left, there’s no shortcut for that.

So I’m going to select these cells first, and then the cells where I want to paste.

Again, to select just the visible cells (press) Alt + Semi-colon; go to the HOME tab, and here’s the fill.

If I click that I can fill left, and now the dates from this cell are filled into the selected cells at the left.

________________

20 thoughts on “Paste With Shortcut in Filtered Excel List”

  1. Nice tip, Debra. I enjoy many of your tips. Finally listened to your interview with Chandoo – very enjoyable and picked up a couple things from that, too!

  2. hey,
    I am using excel 2010, I tried this but it didn’t work the time as was instructed. However, it worked when I first selected the cells “to paste to” then hold CTRL key and select the cells “to be copied from”, then use CTRL+R .
    Thanks to Both Debra and Khushnood 🙂

  3. Great Tip! Thankd Debra & KV.
    I am using Excel 2007 and I encountered something similar to Usama.
    I need to select the empty cells first and then the filled ones for Ctrl right to work.
    Thanks again!!!
    Adi

  4. Hi, many thanks for sharing this.
    This technique doesnt work if the values you want to fill are with formula. How can i do if i want to paste the values only. If i click fill series the reference will move.

    1. Thanks this helped – although I ran into same issue as this comment about what I needed to be filling was values from a formula. I had to make an additional column where I pasted values then put the filter back on as quick solution

  5. Thank you very much, was trying to find a workaround for this for quite a while, and this saved me a lot of time on some complex engineering tables i´ve been making!

  6. Hi,
    Unfortunately this trick can only work when you copy and paste within the same exact sheet.
    I would appreciate if you told us whether it is possible to copy cells from a sheet to another different filtered sheet.

  7. Thank you for tips copy column left to right Ctrl+R
    if copy column right to left can not do you have tips other or not

  8. Thanks for this. Its very interesting, thanks also Khushnood Viccaji, for the fill trick.
    ( I was wondering if these ideas could possibly be better described and Titled as something like “Copying and Pasting filtered rows within a Filter”.
    Pasting into filtered rows in a similar way to what you are doing from rows outside is a long outstanding not solved problem. A few people have mistakenly thought that the solution to that is being presented here )
    Alan

  9. This really helped me and I am going to save this link in my favourites. Cannot believe how frustrated I was so many times and laborious manual work I was doing so far. Thanks to helpful souls like you who record such useful information to help others!

  10. Can you help. I have copied a filtered column over to WORD so I can clean the verbatim, and when I went back to paste into the filtered Excel it didn’t work as it went into the hidden cells as well. Can this be done from Word into the Excel?

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.