Here is something annoying with Excel.
Open any Excel file with few columns of data. Hide some of those columns (select the columns and press CTRL+0). Now, copy a few rows of data. Paste it else where. Excel will paste the values in hidden columns too. We thought Excel would omit the values in hidden columns.
What the filter Excel?!? I thought we were friends, but you annoy me with some of these quirks.
How to copy and paste just visible cells only?
There are two ways to do this.
1. Select visible cells, Copy and Paste
- Select the entire range you need to copy.
- Press F5, this opens Go to dialog. Click on Special button.
- Now select visible cells option. You can use the short cut sequence – F5 ALT SY to do all this.
- Now press CTRL + C
- Then go to target sheet and press CTRL + V to paste.
While this method works, it is too long and if you need to copy and paste several times, then it can be annoying.
Related: More about Go to special
This is where the second trick comes handy.
2. Cheat Excel in to copying just visible cells
If your source data is filtered, then when you copy, Excel copies visible cells only. But how to filter when you need to see all the data. Simple, we cheat.
- Go the end of your data and type something like “X” in the blank cell beneath.
- Now apply filters to the data. Press CTRL+Shift+L to do this.
- Then filter the new value you added in step 1.
- Copy now.
- When you paste, Excel will paste just visible cells. Viola!!!
How do you copy paste visible cells?
We are almost at the end. Let me confess. I never had this problem. I know about Go to Special > Visible cells and use it if needed. But Jo (my wife) faced this issued yesterday and called me to ask how to copy visible cells. When I told her the option about visible cells thru Goto special, she is not all that thrilled, so I got thinking. I think the filter approach is better because you just do what you normally do – which is Ctrl C, Ctrl V.
What about you? Have you ever had to paste visible cells only? How did you deal with that? Share you tips and experience in comments section.
And oh yeah, Check out our quick tips section for some knock-your-socks-off level of awesomeness.
20 Responses to “Copy & paste visible cells only [Excel Trick]”
select all the cells.
press alt+semi colon (;).
only visible cells will be selected.
copy.
paste elsewhere.
voila - only visible cells are pasted.
But how to paste to visible cells of a filtered column ??
I think, the "x" option at the bottom is a really cool cheat option that I wasn't aware of, although I normally use the Alt semi Colon option which Nishkarsh mentioned above.
Alt + HFDSY all day
Is there a way to copy and paste visible cell values?
Assuming you are referring to spreadsheet which has formula and it derives values using those formula. And, you just want to copy the values and not the formula. In this case, select your range, then press ALT+ (Semicolon) together, this will select visible cells only. then go to the sheet/cell where you want to paste the data, there, instead directly pasting the data, perform paste special values. there are many ways to do that, easiest one is press ALT+Cntrl+V together, this will show a pop up window, select Values and press ok (or simply type v and enter). this should do 🙂
Appreciate the feedback. Is there a way to copy paste values on top of the formulas working with only the visible cells?
This option hangs the excel program!
I agree with Nishkarsh, alt + semi-colon is a great time saver for a task like this!
Hi I just learnt yesterday that if you Use ALT + ; it will select only visible cells. I am new to excel 2016 and finding it really hard to find my way around all the ribbons.
This was very informative & i usually do this by using ctrl+G 🙂
I daily copy/paste visible cells from filtered sheets to another (master) sheet. I do it with a macro because copying visible cells is quite easy, however I never achieved finding an excel function to paste onto visible cells.
Do you still have this macro? I'm looking for something like this to help with a monthly spreadsheet where I spend a lot of time copying/pasting from filtered/visible cells on one spreadsheet into filtered/visible cells onto another spreadsheet.
This is really one of the mysteries of Excel to me. I never had to do one of the above tricks. For ages I can just go and select visible cells and paste them elsewhere, completely hassle-free.
I only learnt last year that this is not the standard Excel behaviour and an eye-opener to me, why with macros I always need to write that stuff with SpecialCells in the syntax.
But it makes no sense to me why most of my colleagues can copy visible cells only, like I do and why only a minority cannot. They experience the stuff explained in the post. Though we all are using the same Office installation.
I've been looking for a setting somewhere, but I haven't found it.
I am confused guys... there's a specific function icon you can use to do exactly this. I have it in my quick access menu at the top of excel. You just highlight what you want to copy, click the icon, and this copies only visible cells. No need for macros or shortcuts or trickery. Just exactly what you want with a single extra click on an icon.
Cheers
alec
[…] http://chandoo.org/wp/2017/08/24/copy-paste-visible-cells-only-excel-trick/ […]
@Alec, Nishkarsh, DME & Salahuddin: Thanks for sharing more ways to deal with this problem. I have written a follow up article explaining this. 🙂
use Alt + ; for selecting visible cells only
I'm use X cheat
It's work !!!!!!!
what if i have a formula in some visible cells(where the data is filtered) and i want to paste special on the cells it self. i always want to take out the filter and then select the entire columns where i want to paste special. is there any way so that you can paste speial without taking out the filter