Follow these steps: Select the cells that you want to copy For more information, see Select cells, ranges, rows, or columns on a worksheet. Tip: To cancel a selection of cells, click any cell in the worksheet. Click Home Find & Select, and pick Go To Special. Click Visible cells only OK. Click Copy (or press Ctrl+C). Excel pastes the copied data into consecutive rows or columns. If the paste area contains hidden rows or columns, you might have to unhide the paste area to see all of the copied cells. When you copy or paste hidden or filtered data to another application or another instance of Excel, only visible cells are copied.
Sometimes Microsoft Excel is just too helpful. Like American shop assistants to an English shopper (I’m not being xenophobic, I’m just not used to lots of people asking if I want help finding things (try shopping in Reading on a Saturday afternoon).
Anyone who has tried to paste data in a filtered Excel spreadsheet knows this. Excel will also paste the data into the hidden (filtered out) cells. It obviously thinks it’s being helpful but it’s really not.
There have been many suggested workarounds but it comes down to using Paste into Excel Visible Fields or a Paste into Excel Visible fields only with code.
There are two solutions that we use. If you’re running Excel 2013 or above, you can utilise Flashfill. For earlier versions, you might be able to use the Fill function..
Let’s look at Fill first. Here is our example sheet:
A nice simple table with numbers in column B, whether they are odd or even in column A and the square of the number in column C. What I would like to do is filter on odd numbers (because I am a little odd), copy the square and paste those into the new column D.
Let’s try to do that in the most obvious way and see what happens.
Filter the table to show only odd numbers. Select all the squares in column C and copy.
Click in cell D2, right-click and select Paste Values. But wait! Only half of the values are shown. That’s because Excel is being over-helpful and pasting into the hidden, filtered-out rows as well as the visible rows. It would be lovely if there was a ‘Paste Values into Visible Cells’ option but you’ve already spent an hour searching the internet to discover there just isn’t.
If we clear the filter, we can see exactly that behaviour. Our five selected cells have been pasted into the interim rows.
Now go up to the ribbon (Home tab) and click Fill and Fill Right. Obviously if your destination column is to the left then feel free to hit Fill Left instead.
And voila, unlike the previous attempt, we are seeing all five desired values.
And just to be sure, let’s clear the filter condition to make sure nothing has been copied into the hidden rows.
Bingo. We have our desired outcome. Obviously this only works in the same sheet and if your columns are adjacent left or right to the cells you wish to copy. If there are columns in between, you can hide those columns and this method will still work; Excel doesn’t paste into hidden columns in the same way it pastes into hidden rows. In the screenshot below, I moved column A between the source column and the destination. I filtered on Odd numbers in the same way, then hid column C. Select Columns B and D and use the Fill Right method and as the screenshot works, once I unhide column C and clear the filter, everything still works out ok.
Flashfill Will Only Update Visible Cells
In Excel 2013, we have the lovely Flashfill feature which we blogged about previously. Flashfill will also help but it’s not relevant for Excel versions earlier than 2013 (or Office 365 ProPlus if you ‘re in the cloud).
You can filter on odd numbers, type 1 in the first cell of the destination column, type 9 in the next cell down, hit Enter and then CTRL + E to force Flashfill to take over. All the desired cells will be copied and if you clear the filter condition, you’ll see that the hidden rows haven’t been touched. This is why we love Flashfill!
If you’d like more hints and tips, subscribe to our newsletter or better still, sign-up for one of our courses.
Tags: CalcExtensions
Rating:
This extension provides an alternative to the 'Select Visible Cells' command in Microsoft Excel. It´s useful to use with subtotals or ranges with hidden columns or rows.
How To Copy Data In Filtered Cells In Excel
Description
To use Copy only visible cells, do the following steps:
1. install the extension;
2. restart LibreOffice;
3. open your sheet with hidden rows or columns;
4. select a range;
5. insted the simple copy button, click on Copy only visible cells button (or Edit -> Copy only visible cells menu);
6. paste in your target document, for example, another sheet or a RTF text table.
The hidden rows or columns won´t paste.
Select And Copy Visible Cells From Filtered Data In Microsoft Excel
Note: it´s not possible paste especial with 'DDE Link' option in Writer.
Available on 16 languages: Bulgarian, Catalan, Danish, Dutch, English (default language), Estonian, French, German, Hungarian, Italian, Lithuanian, Portuguese (Brazil), Portuguese (Portugal), Slovenian, Spanish and Swedish. Special thanks: Andrew Pitonyak, Stephan Wunderlich, Ryan Nelson, Sheila Tozatti, Paolo Mantovani, Christian Junker and Comune di Bologna.
Homepage: http://wiki.documentfoundation.org/PT-BR/Extensions/Projects/CopyVisiCells/en
Release List
- ReleaseDescriptionCompatibilityOperating SystemsLicenseRelease notes
- 2.0The code was completely refactored for LibreOffice 5.2.x.5.2Linux, Windows, macOSLGPLDownload
- 1.2Added 5.0 and 5.1 compatibility. Important: this version is not 5.2 compatible.5.0Linux, Windows, macOSLGPLDownload
- 1.1Big ranges copy bug fixed.4.1LGPLDownload
- 1.0.5New Catalan and Dutch translations.3.3LGPLDownload