This video shows the problems that you can have when you try to copy and paste data into a filtered list. You can accidentally paste data into hidden rows, and ruin your data.

Watch this video to see why the problem occurs, and a couple of workarounds, to help you avoid the problem.

Visit this page to download the sample file.
https://www.contextures.com/excelcopypastefilteredlist.html

Video Timeline:
00:00 Introduction
00:30 Filter a Sales List
00:48 Copy Visible Row Data
01:06 Paste in Filtered Rows
01:36 Check Incorrect Pasted Data
02:23 Workaround 1: Sort Data
02:57 Workaround 2: New Column
04:15 Conclusion

‘========
Video Transcript

In an Excel list, if you try to copy and paste when the list is filtered, you might run into some problems.

So we’re going to take a look at how those problems occur, and how you can avoid it.

So in this list, we’ve got some ship dates that are missing, and it’s all the chocolate chip cookies. Somebody just didn’t fill those in

I know they were always shipped on the same date that they were ordered.

So I’d like to copy the order date into the ship date column, and do them all at once.

To start, I’m going to filter the product column for chocolate chip.

I start typing it, and it comes up with just that item.

When I press Enter, it’s filtered for chocolate chip.

Now I can see the dates, and I want to copy from there to here.

So from the same row to the same row, you’d expect that to go smoothly.

But if I copy these dates.

I’ll select them – click copy.

And you can see that each date is individually selected.

It hasn’t just selected a block of cells; it selected those five dates.

I’m going to come over here and click in the first ship date.

And when I paste, instead of getting those five dates into these five cells, it’s only showing me 2 ship dates.

It was the first one, and then the fourth one.

So it’s missed some by the look of it.

If we look over here, we can see that we’re in rows 6, 9, 12, 36 and 44.

So the data wasn’t all together in the list.

To see what happened, I’m going to remove the filter.

I’ve got a clear button up on my Quick Access Toolbar.

I’ll click that, and I can see that all the dates, even though they were copied individually, have all been pasted into a block.

So there were two chocolate chip cookies in those rows, but it also overwrote the ship dates for bran, banana and a whole grain order.

That’s not what I wanted at all, so I’m going to very quickly undo that paste.

I get back to the original, and I’ll press the Escape key to get rid of that copy marquee.

So it’s not going to work to just copy from here and paste to there because it copies individually, but pastes as a block.

One thing we could do is just try and sort our list, so all these chocolate chip cookies are together.

So if I clear the filter, I could sort by Product

And then go down and copy and paste from here over to here.

So that will work well for you, in some cases – just get the items together and then copy and paste.

But in some cases that won’t work. Perhaps you’ve got three or four things filtered and you’re down to a few items, and you can’t sort to get them all together.

In that case, you could add a column in your table temporarily, and use it to mark the rows.

I’m going to insert a column here

And we’ll use that to mark the rows that we want to copy and paste.

I’m going to sort the list by date again, which is the way it was originally.

And in this column, we’re going to put our mark.

First I’m going to filter by chocolate chip

And if there were other things you were filtering, you could apply those filters.

Now you’re down to the list you want to change.

And in this column, I’m going to select the cells, type an X, and then press Ctrl and Enter to put that X in all those cells that are visible.

And when I clear the filter, we can see that there are Xs only in the rows that were filtered. None of the other rows are marked.

So now I could sort this column, Data, A to Z, and all those chocolate chip, or whatever we had filtered, is at the top.

I can now copy from here over to there without overwriting any of the other data.

And once you’re finished, then you could delete this column, and sort again by date.

So to avoid overwriting data in hidden rows, make sure you’re not copying and pasting in a filtered list, if there are other rows hidden, between the items where you’re copying and pasting.

Please take the opportunity to connect and share this video with your friends and family if you find it useful.