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:
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.
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.