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).
- Copy Visible Cells In Microsoft Excel Formula
- How To Copy Data In Filtered Cells In Excel
- Copy Visible Cells In Microsoft Excel Spreadsheet
How to Copy Visible cells using GoTo Special option in Excel? First, select the range of cells in which you want to act. Then go to Find & Select option under the Editing group in the Home menu. Then select GoTo special, and select the Visible cells only option in the dialogue box that opens up. Select Only Visible Cells In Excel – Keyboard Shortcut. Open the Excel sheet you want to copy cells from. Select the cells you want to copy. Don’t worry if it includes hidden rows and columns in the selected cell range. Once you’ve selected the cells, type the Alt+; shortcut. A white outline will appear around the selected cells.
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.
How Do I Use The Alt+; Excel Shortcut To Select And Copy/Paste Only Visible Cells?
Say you have 1000 rows of data, with 15 hidden rows. You’ve hidden those 15 rows because you don’t want to copy them. You select the 1000 rows with Ctrl+A, then you do Ctrl+C to copy, and then Ctrl+V to paste. Your selection (before you paste) looks like this:
You’re moving fast and you think you’re a shortcut genius. But to your chagrin, they hidden cells get copied anyway.
Why? Because you selected them (even if you didn’t know it). Creating el capitan bootable usb.
So how do you solve this problem without slowing down?
Simple: Alt+;
Here are the steps:
- Select the data, including the hidden cells/rows/columns
- Hit Alt+; (doing this de-selects the hidden cells/rows/columns — see picture below)
- Hit Ctrl+C to copy
- Move to where you want to paste
- Hit Ctrl+V to paste
…and you’re done.
Copy Visible Cells In Microsoft Excel Formula
So the key step here is #2. It contains this valuable shortcut:
How To Copy Data In Filtered Cells In Excel
De-Select Hidden Cells/Rows/Columns: Alt+;
You use this shortcut AFTER you have selected the data — but BEFORE you copy (or cut, or format, or whatever else you want to do).
After you hit Alt+; you’ll see that the selection looks different — only the visible cells are selected:
Once you add Alt+; to your sequence you can walk around with the swagger of an Excel shortcut guru.
Impress your co-workers, your boss, your friends, your loved ones…
Copy Visible Cells In Microsoft Excel Spreadsheet
This baby can really save you some time. Oculus app download mac. Enjoy!