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.
Update from Alex Blakenburg.
- Copying Manually
- If you copy just the filtered table range, it copies values and formatting and only the filtered data set.
- If you copy the filtered table plus rows below and/or columns to the right , it copies values and formatting but excludes table formatting and only the filtered data set.
- If you copy the filtered table plus rows above and/or columns to the left, it replicates the entire table as a table with the filter removed.
- Copying using VBA
This is really quirky.
- If the sheet with the table is active AND the activecell is inside the table, it copies the Filtered Data as values with formatting. (if only the table was selected will include table formatting if UsedRange selected excludes table formatting)
- If the activecell is not in the table or another sheet is active then it will copy the entire table as a table.
- If we want to copy just the filtered records. We need to select visible cells to be sure it works regardless of whether the active cell is in the table or not.
- If we want the copy the table as a table. We need to make sure there are no filters applied to the table (ShowAllData) in case the active cell is in the table range.
Great! it worked perfectly.
Thank you
Great to hear, Rogerio!
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?
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
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?
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.
thanks it worked
Great to hear, Prabhjeet!
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.”
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.
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!
Glad it was helpful, Carl.
Great! it worked perfectly.
Thank you
this is only applicable if you’ll be pasting the same data (in your case, its formula) only…
how about pasting different sets data?
Hi Marco,
You can’t paste data that has been copied from non-contiguous cells to different non-contiguous cells.
Mynda
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?
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
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 …
Yes, I think that’s wise 🙂
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!
Good to know, Andy.
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…
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.
ITS DOSE NOT WORK
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.
This didn’t work, it still pasted in the hidden areas. Thanks for nothing.
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.
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.
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.
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.
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
“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!
this is not working
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
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
Thanks, Mohit.
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
Hi Jackie,
Unfortunately, that’s a limitation of this technique. i.e. you can’t paste to non-contiguous cells.
Mynda
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
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?
Hi Jim,
No, sorry.
Mynda
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!
Nice tip, cheers Brandon.
Mynda
This is an absolutely fantastic time saving feature. AWESOME!!
Thanks, David 🙂 Glad you’ll find it useful.
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.
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
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????
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
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 :/
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
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
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
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
Thanks for the article.
You forgot to add that we can select visible cells only with the shortcut:
left Alt + semicolon
Wow, I didn’t know that! Awesome tip.
Cheers,
Mynda