Use Shortcut to Paste in Excel Filtered List - From Same Rows

This video shows the problems that you can have when you try to copy and paste data into a filtered list. In my previous video, I showed a couple of workarounds, to help you avoid the problem.

Use those workarounds, if you are pasting data from another location.

However, if you are copying and pasting data from one column to another, within the filtered list, you can use a shortcut command.

Watch this video to see how you can use a keyboard shortcut, or a menu command, if you are copying and pasting across a row.

Visit this page to download the sample file.

Video Timeline
00:00 Introduction
00:42 Select Visible Cells
01:08 Copy to the Right
01:42 Copy to the Left

Instructor: Debra Dalgleish, Contextures Inc.
#ContexturesExcelTips

Tip from Khushnood Viccaji, who can be contacted at Elance:

Video Transcript
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.

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.

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, 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.

[kidseducationkh]

It's great! but if I want to paste in filter of other worksheet, How can I do?

[PremKumar-degr]

how do i paste data from one sheet to another sheet, when filter is applied

[lesegomodutwane]

I have asked around for this solution, no one seems to know this at work. Thanks a great deal. You just saved me some great deal of time

[scottyboiler]

After all these years how did I not know about fill left and fill right? Thank you for this posting.

[samdrella]

Thank you for posting this - you've saved me so many hours of copy/paste!!

[jonvmurphy]

This tip was EXTREMELY helpful, lately I've been working with huge data sets and needing to clean-up or update data cells, and was getting frustrated when i tried to change multiple filtered cells!!

[venusfly]

This saved me a TON of time today filtering and copying through data. Thank you for the EXCELLENT tutorial!!

[sandyful]

Thank you so much...Searched for same for a while...This is simplest way to do this...Thanks again🙂

[vinodjames]

Thanks Debra and Khushnood! This is a great help you guys are life saver,

[VivekSharma-lmih]

Just the thing I wanted right now, saved me from serious copy paste business 👍👍👍

[yiyangwang]

Thanks from 2020! The only video solved my problem!

[ExcelCampus]

Awesome!  Thanks Debra and Khushnood!  Before learning this I would have created a formula on the visible cells that just referenced the range to be copied, then press Ctrl+Enter to fill the selection with the formula, then copy/paste values if necessary.  But your shortcut is much easier.  Thanks again!  

[Pykenike]

Use Alt E I L instead of CTRL R to fill from right to left, instead of left to right.

[neerajrt]

Thank you, it is very help and very good tutorial.

[damodarbangalore]

Thanks for sharing your knowledge Mam 👍🙏

[ABCD--ww]

Thank you Contextures Inc., That was very useful

[giabawa]

What about if we've formula in the cell. Such as VLookup, we want to copy and paste searched/extracted data only.

[danikirom]

Nice tips thanks. But how to fill/paste data to filtered table with different worksheet? Help me please. i really need your feed back

[armaanpremjee]

This is so helpful! I was doing this manually and it was taking up so much of my time. Thanks a lot.

[thefoodadvocate]

Brilliant! Thanks! Saved me hours of painful work!