Have you ever wanted to only copy or paste visible cells?
For example, below I have a table containing outstanding customer invoices. I want to insert a formula in column E to insert an overdue fee for invoices outstanding longer than 31 days:
However, I don’t want the formula on the subtotal rows (row 5, 11, 13, 19, 21 etc.)
I can use the Group buttons to quickly hide the subtotal rows. I've entered my formula in cell E2 and I just need to copy it down:
Note: The method of hiding the rows doesn't make any difference to how this technique works. You could use filters, or a regular right-click on the row label > Hide, to hide the Subtotal rows. I used the Group tool on the Data tab of the ribbon so I could easily hide/unhide the rows for the purpose of writing this tutorial quickly.
Paste Visible Cells Only
The problem with a regular copy and paste is Excel will also paste to the hidden cells, so I have to select the visible cells first. I can do this with Go To Special:
- Copy cell E2 to the clipboard – just select it and press CTRL+C
- Select the range you want to paste to. In my case E3:E51
- Press CTRL+G to open the Go To dialog box and then click ‘Special’ in the bottom left:
- In the Go To Special dialog box select the ‘Visible cells only’ button and click OK.
Notice how each group of cells are individually selected:
- You can go ahead and press CTRL+V to paste the formula into the visible cells. I've unhidden the subtotal rows in the image below so you can see the magic:
Tip: Instead of copying and pasting, I could enter the same formula in all the selected visible cells using steps 2 to 4 above, then for step 5: type in the formula and press CTRL+ENTER to enter them all in one go.
Copy Visible Cells Only
Copying cells in a filtered table will only copy the visible cells by default, but if you have hidden rows or columns (as opposed to filtered), then Excel will copy the hidden ones too.
For example, you can see in the data below that row 3 is hidden:
We can use the same technique to only copy visible cells:
- Select the range A2:E9
- CTRL+G to open the Go To dialog box
- Click ‘Special’
- Select ‘Visible cells only’
You can see there is a subtle line between rows 2 and 4 indicating row 3 is not selected:
- Press CTRL+C to copy and then go ahead and paste the cells where you want. You’ll notice they are pasted as a contiguous range of the 7 copied rows:
Filtered Tables
By default copying and pasting in filtered tables only does so for visible cells, although I have experienced occasions where this didn’t work as it should (but I couldn’t replicate it for this post 🙁 ), so I always use this technique, even in a filtered table.
Excel PivotTable Calculated Items by Position
rogerio
Great! it worked perfectly.
Thank you
Mynda Treacy
Great to hear, Rogerio!
Doug Vodicka
I’m having the problem where I copy a value from one cell in a filtered column, select the rest of the cells in the column, paste, and EXCEL pastes the value on hidden and visible cells. This happened yesterday, and again today, with a shutdown in between. So for right now, it’s a repeatable issue. The problem is repeatable in different spreadsheets. So, wondering if there is a way to get Microsoft to look into this?
Mynda Treacy
Hi Doug,
I can’t replicate that issue in my own files. A workaround is to use Go To Special to select the visible cells before pasting. See instructions in the post above under the heading “Paste Visible Cells Only”.
If you want me to test your workbook, please post your question on our Excel forum where you can also upload a sample file and we can help you further.
Mynda
Emily Fong
Heyyy
Good day! I’ve tried using your way of pasting visible cells only. But the problem is that the excel doesn’t allow me to do so. It says that the copy area & the paste area aren’t the same size. But I’ve checked. it’s actually the same size. Do you have any idea what’s going on?
Mynda Treacy
Hi Emily, when you’re pasting you can only select a single cell. If you try to select non-contiguous cells it will not allow it.
Prabhjeet
thanks it worked
Mynda Treacy
Great to hear, Prabhjeet!
Kelly
This did not work for me. When I attempt to paste after selecting visible cells only, Excel sends me an error message:
“This can’t be done on a multirange selection.Select a single range and try again.”
Mynda Treacy
The error sounds like you’re trying to copy a non-contiguous range and paste it into another non-contiguous range. This isn’t possible because the ranges are probably different sizes.
Carl
Hey! Great article, thanks.
+1 to what Mohit said. Might want to consider updating the post since that’s what was tripping me up and it’s kind of buried in the comments.
Thanks!
Mynda Treacy
Glad it was helpful, Carl.
ili maier
Great! it worked perfectly.
Thank you
Marco Mariano
this is only applicable if you’ll be pasting the same data (in your case, its formula) only…
how about pasting different sets data?
Mynda Treacy
Hi Marco,
You can’t paste data that has been copied from non-contiguous cells to different non-contiguous cells.
Mynda
Andy
Hi Mynda. You are my go-to person for all problems Excel. Thanks for the excellent advice over the years!
When you say that “you can’t paste data that has been copied from non-contiguous cells to different non-contiguous cells” does this apply to filtered tables?
I am sure that I have, in the past, copied values from a filtered column in a table to an adjacent column, and this worked. But recently I have found that, sometimes, when I paste the column into an adjacent column in the same filtered table, it pastes the values into contiguous cells, including filtered non-visible ones. That can make for a real mess when you unfilter the table!
This has cost me many hours work this week ….
BTW Is there a parallel situation with fill down or copy down? In a filtered table, can this ever enter data in non-visible rows?
Mynda Treacy
Hi Andy,
Good questions. I haven’t tested Fill Down to know off the top of my head, but I suspect it doesn’t respect hidden/filtered cells.
I’ve found the whole copying and pasting in tables to be inconsistent, so I generally avoid it where possible!
Mynda
Andy
Many thanks for your time, Mynda. I suppose the lesson is always sort rather than filter before copy/filling. Re: copy down, I’ll experiment some time and feedback …
Mynda Treacy
Yes, I think that’s wise 🙂
Andy
Actually I forgot to get back to you on this. If you have, say, an advanced filtered list I have found you can fill down, but not copy/paste. That requires any list to be sorted to allow. Hope that helps. Fill-down certainly has helped me as it saves hours!
Mynda Treacy
Good to know, Andy.
Lola
Hola,
could you give a tip on how to copy visible cells only and paste visible cells only??
They should have the same size and Alt +;, copy, select past range, alt+;, paste doesn’t work…
Catalin Bombea
Hola Lola,
Might be the same size, but there are many other unseen things that can go wrong. Can you upload a sample file on our forum?
You can also try an addin, Paste Buddy for example.
ABHIMANYU
ITS DOSE NOT WORK
Mynda Treacy
Sorry you’re having trouble getting it to work. I suspect you’re trying to do something it’s not designed to do. If you’d like to post your question and sample Excel file on our forum we can see if there’s a workaround that suits your needs.
Steve
This didn’t work, it still pasted in the hidden areas. Thanks for nothing.
Mynda Treacy
Sounds like something went wrong with steps 2 through 4 in the “Paste Visible Cells Only” section. If you care to post your question and Excel file on our forum we can take a look and see if we can reproduce the problem.
Shruthi
Hi, I had to copy from a continuous range and paste it in visible cells only. This method does not work. If you could give me a solution it would be great.
Mynda Treacy
Can’t be done as far as I know, sorry. It’s because the ranges are different shapes; one is contiguous and the other is not.
Muntaser
I did all the above steps. However, still can’t past here are an error always appear wihich is : This can’t be performed on multiple selection. I think there are add on feature should be add to my excel program anyone can give me the link to download this add on feature for Ms Excel 2016?
Thank you in advance for your cooperation.
Mynda Treacy
Hi Muntaser,
There’s no add-in required. The selected ranges must include the same rows or columns. For example, you can’t copy cells A1:B5 and D6:E10 in the one action. But you could copy A1:B5 and D1:E5.
Mynda
derek
“You can’t paste this here because the copy area and paste areas aren’t the same size” Yum…yes they are…. Thats it I’m moving to Google Sheets!
orgilmas
this is not working
Mynda Treacy
Perhaps you can post your question and Excel workbook on our Excel Forum where you can describe the steps you are taking and we can help you further.
Mynda
Mohit
Hi Mynda:
I just faced this same issue when copying a formula from one cell and pasting values only on a filtered column. It pasted on all rows of the column and not on the visible cells only.
That should replicate the scenario that you mentioned above. Thanks for the post
Mynda Treacy
Thanks, Mohit.
jackie
That doesn’t work when I go to paste on non-contiguous cells in another column. I get a error msg which says “That command cannot be used on multiple selections”
It allows me to copy as you indicate above but doesn’t allow the paste to non-contiguous cells
Mynda Treacy
Hi Jackie,
Unfortunately, that’s a limitation of this technique. i.e. you can’t paste to non-contiguous cells.
Mynda
thomas
Pasting something only into the visible cells of a filtered list is not possible and that’s a real shame. The workaround I do is using a plugin called Power-user that does that among other stuff: https://www.powerusersoftwares.com/copy-paste-visible-cells-only
Jim
In Excel 2010, in a spreadsheet with hidden rows, can I copy a columnal range of cells to another columnal range of cells without affecting the hidden cells? For example, copy b2,b4,b6 to e2,e4,e6 without overwriting hidden cells e3 and e5?
Mynda Treacy
Hi Jim,
No, sorry.
Mynda
Brandon
I just discovered from my excellent coworker a much faster way to select visible cells – after selecting the range needed, just press “ALT + ;” and it switches to only highlighting the visible cells, the same as the multiple steps above.
Posting this in the hopes that it helps others running into this same issue!
Mynda Treacy
Nice tip, cheers Brandon.
Mynda
David
This is an absolutely fantastic time saving feature. AWESOME!!
Mynda Treacy
Thanks, David 🙂 Glad you’ll find it useful.
chichi
I can’t do these steps, it appears that the nformation cannot be pasted because the copy are not the same size and shape.
please help.
Catalin Bombea
Looks like you have merged cells in the paste range?
There is not much to do about this, you have to make sure the copy and paste range have the same size.
Catalin
Monica
i counted manually the cells have the same size not merged, it does not work, should I have an add installed to make it work????
Catalin Bombea
Maybe you can upload a sample file so we can see your situation. Open a new topic on our forum, and upload a file with that problem.
It will be easier to help you, thanks for understanding.
Catalin
Clare
I’ve had a similar problem and I think its because there are some hidden cells in the selection i.e. I am using filtered data and it will only let me use this function on subsequent rows 1-5 e.g., when the row then jumps to 8 (because it is filtered) it is still counting the rows not included in the filter so is saying it isn’t the same size as the data I want to paste. Haven’t found a solution for this yet :/
Catalin Bombea
Well, not every problem can be solved exactly as we like, unfortunately. But there are so many ways to get to the same result, you will just have to find another way. If you need more help, maybe uploading a sample file to our forum will clarify the problem.
Cheers,
Catalin
jim
I would always use the select, type formula, ctrl-enter option rather than type, enter, copy, select, paste
not only fewer steps, but avoids copying formatting, validation and comments
Note that if you copy and paste a non-contiguous selection, then formulae are converted to values
As well as using alt-; as a shortcut for visible cells, Grouping is much quicker with shortcuts alt-shift-right to group and alt-shift-right to ungroup. I haven’t used the subtotal tool for years, it certainly used to be flawed when used beyond 1 level and now with pivot tables, why would you?
Jim
Cathy
I love your e-mails and tips! They are always informative and easy to follow. With the “Excel Copy and Paste Visible Cells Only” tip I encountered a small challenge that I have yet been able to resolve. That challenge is actually with the subtotal grouping. In all honesty I don’t use this function much so I’m guessing that I’m missing some really simple step. So here’s my problem, I recreated your table with the subtotals but the grouping does not let me hide just the “subtotals”. I have only been able to get it to hide the data. My “outline” shows 1-3 where your example only had 1 and 2.
Any assistance you can provide would be appreciated.
Thanks,
Cathy
Mynda Treacy
Hi Cathy,
The Subtotal tool will automatically insert groups to hide the detail rows, I manually inserted groups to hide just the subtotal rows. You’ll find the Group tool beside the Subtotal tool on the Data tab of the ribbon.
Kind regards,
Mynda
pmsocho
Thanks for the article.
You forgot to add that we can select visible cells only with the shortcut:
left Alt + semicolon
Mynda Treacy
Wow, I didn’t know that! Awesome tip.
Cheers,
Mynda