Have you ever tried to copy and paste a range with hidden rows or columns and didn't get the results you expected?
This problem can be frustrating and time consuming! In this post and video I will explain how to solve this problem with a very simple shortcut. The shortcut will select the visible cells only in a range, ignoring the hidden rows and columns.
Video
Please leave a comment below with any questions.
Why Are My Hidden Rows & Columns Being Pasted?
When you copy a range of cells that contains hidden rows or columns, Excel includes ALL of the selected rows. It does not matter if they are visible or hidden. When you go to paste the range, all the cells in the selection will be pasted.
This is actually a nice feature, but it's not always what we are expecting to happen. Sometimes we only want to copy and paste the visible cells, and exclude the hidden rows & columns.
This commonly happens when you are working with a range that has filters applied to it. Or when you have hidden rows or columns from a collapsed group in an outline. The Subtotals feature automatically creates row groupings that can cause rows to be hidden, as shown in the example above.
Select the Visible Cells Only
Excel gives us an option to select the visible cells only using the keyboard shortcut Alt+; (hold down the Alt key, then press the semi-colon key). The Mac shortcut is Cmd+Shift+Z.
This shortcut will exclude all the hidden rows and columns from the selection.
Copy & Paste Visible Cells
Here are instructions on how to copy and paste visible cells only (please see the video above for details):
- Select the entire range you want to copy.
- Press Alt+; to select the visible cells only. You will notice that the selection is cut up to skip the hidden rows and columns.
- Copy the range – Press Ctrl+C or Right-click>Copy
- Select the cell or range that you want to paste to
- Paste the range – Press Ctrl+V or Right-click>Paste
Alternatives to Alt+;
If you don't use this shortcut often then it might be hard to remember the keyboard shortcut. Here are a few alternatives for selecting visible cells.
Add a Button to the Quick Access Toolbar (QAT)
Please see the video for step-by-step instructions on how to add this button to the QAT. Pressing the button will select visible cells only. It also shows the keyboard shortcut when you hover your mouse over the button.
The Go To Special Menu
You can also find the Select Visible Cells Only option on the Go To Special menu.
This can be a very useful menu for selecting other types of cells besides just visible cells. As you can see in the image above there are a lot of options for different types of cells you can select.
There are a few different ways to open the Go To Special menu.
1. On the Home Tab of the Ribbon, press the Find & Select button on the far right side. Then choose Go To Special…
2. Press Ctrl+G or the F5 key on the keyboard from anywhere in a worksheet. Then press the Special button.
So Many Different Ways to Select Visible Cells
As you can see, there are a lot of different ways to select the visible cells in Excel. For me, the keyboard shortcut (Alt+;) is by far the fastest. If you don't think you'll remember it then put the button on the QAT. 🙂
Either way, this is one shortcut that should help save you a lot of time!
Paste to the Visible Cells Only
If you have ever received the following error message in Excel, “The command cannot be used on multiple selections”, then you know that you cannot paste to the visible cells in range that contains hidden rows or columns. This is a limitation of Excel, and it can be frustrating.
The video below explains two workarounds for this.
Click the following link to learn more about the Paste Visible feature and download a free trial of the Paste Buddy add-in.
Copy Visible Cells to Outlook Email
Mandy asked a great question in the comments below about how to paste the visible cells to an email in Outlook. The following screencast animation shows how to do this.
Here are the steps to copy the visible cells to an email :
- Select visible cells in Excel – Alt+;
- Copy the range – Ctrl+C
- Open an email in Outlook
- Choose from the Paste Options on the Message tab
The Paste button on the Message tab of the Ribbon contains a small drop-down arrow underneath it. Click this arrow to see the Paste Options. You can hover over each of the options to get a preview of what the paste will look like in the email.
When copying and pasting from Excel to Outlook, I usually use either the “Picture” or “Keep Text Only” options. If I know the user will be copying the data out of Outlook, then it's best to give them the text. If I know the user will only be looking at the data, then I will paste a picture because I know the formatting will remain in tact.
Please leave a comment below with any questions.
Great tip- thank you. I’d like to subscribe to your offer.
Appreciate it.
Great Tip – Helped me in saving the time of using excel. Thanks
I would like to sign up for your offer. I have a question though. It pastes a lot fewer values in my pasted column than there are in my column where I selected and copied the visible values only. Why?
Hi Judi,
I’m not sure I understand your question. It might be easier to see an example. Are you pasting to a range or sheet that does NOT have hidden or filtered rows? If so, then it might be that all the rows in your filtered range were not selected before selecting visible and copying. I hope that helps.
Hello John, I followed the above instruction and all worked brilliantly. Except when I attempted to pasted those into the range I needed,it only pasted 6 of 54 numbers in random order. I REALLY don’t want to have to copy and paste each cell individually
Thank you kindly, April
Thank you!
Its superb as your other posts : D, regards for posting .
How to copy paste hidden cells on hidden cells (only values) -i´m looking for quick way to add running numbers to a table that includes hidden cells and I want to paste the runnig numbers to specific rows (when there are hidden cells).
This video is useless. The question is how to PASTE in filtered cells. All you showed is how to copy from filtered cells!
Read the question carefully first.
Hi Arif,
Please see the section in the article “Paste to the Visible Cells Only”. There is a video there and I believe it answers your question. Pasting to a filtered range is not directly possible in Excel. However, I show two workarounds to that issue. I hope that helps.
Exactly what I wanted. Thank you for your perfect explanation.
Great and to the point! Thank you so much!
a big thank to u
Excellent!
Hey Jon,
Can select visible cells only be used with chart tools when creating a chart based on multiple worksheets?
Great Tip Thank you
Question ( you give them a finger and they gone take the hole hand 🙂 )
do you have als a VBA code for that ?
Thank you
Gr Harrie
Hi Jon,
Quick question, when pasting visible cells only into a new excel sheet, I select “keep source column width” and only the columns appear but no data. Any other selection in paste shows data, but changes all the column widths. Any suggestions?
kind regards
Maggie
Hi, I believe you can do paste special twice – so you paste in the data and then with the paste area still selected choose paste column widths
EXCELLENT VIDEO
Just what I needed. !
How do I copy and past filter cells into another column which is filtered also?
I have the same question. It’s easy to select and copy visible cells only, however, when you copy a group of filtered cells and want to paste into another column which is also filtered, Excel actually does not allow you to do.
Same question. Thanks!
Nevermind, watched the second video. Brilliant, thanks!
So how to you then paste these visible cells as values – so in your example, how do you paste the formulas in D7-12-17-18 as values
hi!
I have a problem with copy and paste all the visible cells. the error occurs when I paste the selected invisible cells. the problem is not all the visible cells appear after pasting the date ( or another words – there are a lot of missing cells), although I have followed the step by copying all the visible cells.
please help!!
Hi Yanti,
Ensure that you are pasting to an area where there are no hidden rows or columns.
Regards,
Toni-Marie
I’m using my cell phone I don’t no how to do it on here
Very usefull. How can i auto that in VBA?
I use auto filter and when i select visable cells only excel selects all rows to 14K instead of just data cells?
Thank you,
this is a phenomenal command. Always find useful, time-saving information from your site.
Thanks a bunch for the tip!!
This was very helpful and to the point! Thank you!
Hi, could you please also explain how to do vice versa and if it is possible. I need to copy the data in visible cells only and paste values in the same area. Before pasting the values I try to select again visible cells and then paste the values, however I get the error:”This action won’t work on multiple selections.”
Great tutorial to assist with a very frustrating topic.
Thanks for tip!
Wonderful, this is of great help!
Thank you!
Thank you
the cells that I selected from my table has formulas and different column widths. I tried what you said here and pasted my selected rows on another sheet, but when I select ‘keep column width,’ the values on all the cells went gone. then, when I kept source formatting, the values were still there, but the column widths had all gone so narrow, and the formulas have not been pasted, only the resulting values.
how can I copy selected rows on my table while still maintaining both column widths and formulas and will look just exactly like the form from the original sheet? thanks. and additionally, how can I maintain it as a table? because when I pasted, it was not formatted as table anymore. I needed to format it again to table.
Thanks.
(some details on what i did: i selected ranges including the table headings. did that affect it?
i tried both the alt ; and the ctrl g. it both didn’t copy the formulas.
i tried to copy paste the table headings first before the selected rows; still it didn’t copy the formulas, and I have to format to table it again. and, the column widths got so wider this time when i formatted it to table.)
Great stuff … worked like a charm.
Great voice, excellent explanation
After using the formula to copy and paste, I need to remove the copy column from my report. Once I do this, the paste column error’s out. How can I keep the data in my paste column when I remove the copy column from my report?
excellent Video and pretty helpful !!
Is it possible to copy to columns to the right? I have tried using the method suggested but this does not seem to work for me
Thank you so much. This is a life save! I will subscribe. You make it so easy to follow and set up the quick access tool bar.
Hello, I tried to used this function but then my goal is to paste the visible cells in a column right next to it but I get an error message saying this action won’t work on multiple selections. Is there any way around this?
Very helpful. Thanks. Btw, when the cells have some merged cells, this shortcut isn’t able to work. Is there a work around?
Go to special and Alt; is not working in my excel.
Kindly give me resolution.
This won’t work when I’m trying to paste the visible hidden cells to another spreadsheet that also has hidden cells. Only seems to work when pasting to a regular excel spreadsheet. I have a “master” speadsheet and I asked each of my sales people to fill out their parts, and when they send it back I sort by their name and then want to cut and paste only their info to my master, also sorted on only that person’s name. It’s not working !
Hello
i am an Estimation Engineer usually work on excel for estimation.
I have some data which is visible and is available in Sheet 1 (lets Say), i want to paste this visible data in another sheet (sheet 02) where i have filtered data.
When i try to paste there, it is not working as what i need exactly,
(Data in sheet 01 which i want to copy is visible, but where I want to paste has filtered data) Thanks
Thank you for sharing this short-cut. years that I could have used this!
You shared on copying visible cells but how do you copy from visible cells and pasting on visible cells? Because when I copy from a visible cell, and then paste on filter cells, some values are paste on hidden cells as well. How do you overcome that?
My entire database is about 2000 rows and two columns wide. When I try this only about 8 rows get copied when I’m trying to copy about 125 rows. Would you know why? Thank you.
Hi,
I could not manage in a big set of data. It returns the command cant be mane in multiple selection. Is there a way of turning around?
How to paste values and source formatting at the same time in the paste buddy add-on?
I could not get this to work while I have an existing filter on the column. It selected correctly but when I pasted, it still pasted the hidden data.
Nice and easy, just like I like it! Thanks