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.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.
Jim says
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 says
Hi Jim,
No, sorry.
Mynda
Brandon says
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 says
Nice tip, cheers Brandon.
Mynda
David says
This is an absolutely fantastic time saving feature. AWESOME!!
Mynda Treacy says
Thanks, David 🙂 Glad you’ll find it useful.
chichi says
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 says
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 says
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 says
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 says
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 says
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 says
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 says
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 says
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 says
Thanks for the article.
You forgot to add that we can select visible cells only with the shortcut:
left Alt + semicolon
Mynda Treacy says
Wow, I didn’t know that! Awesome tip.
Cheers,
Mynda