How To Copy & Paste Visible Cells Only

Have you ever tried to copy and paste a range with hidden rows or columns and didn’t get the results you expected?

Copy Paste Select Visible Cells in Excel

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

Select Visible Cells Only in Excel

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)

Select Visible Cells in the Quick Access Toolbar

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.

Paste Visible Cells That Command Cannot Be Used on Multiple Selections Error in Excel

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.

Click here to get Paste Buddy

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.

Please share

Jon Acampora

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 228 comments

Older Comments

Melynda Parks - April 10, 2018

Thank you, Thank you, Thank you!
I’ve spent countless hours trying to copy rows in continuous order after running filters and your video made it so easy!! your excel campus will be my first stop when i am having issues. I can’t believe how easy it is.
Thanks again for your help.
Melynda

Zubair - March 29, 2018

My Question is…. Suppose in Sheet 1 There is a column A1 : A10 (to copy), in sheet 2 there is a filtered data A1,A3,A5,A7,A9,A11,A13,A15,A17,A19 (to paste) how can i paste into the selected visible cells.. when i paste the Sheet 1 data A1:A10, it only paste the A1 data in all the visible cells…

karyn - March 18, 2018

Hi,

Is there any way to paste from a range of cells to selected visible cell?

Eg. copy “Cell A1 to A10” paste to cell “C1, C3, C4,C5,C7 to C12”.

Thank you

Annie Nichols - March 15, 2018

Ooops! I may have spoken too soon! I don’t get the “marching ants” around the cells I wish to copy in the filtered worksheet. Followed your amazing directions to the letter…even put the shortcut on the toolbar up top…but still no “marching ants!” Any suggestions?

Jon Acampora - March 15, 2018

Hi Annie,

Thanks for the nice feedback! It depends on what version of Excel you are using, but you might not need to select the visible cells before copying IF the range is filtered.

Otherwise, make sure to press Alt+; BEFORE pressing Copy. That is the most common issue I see. I hope that helps.

Annie Nichols - March 15, 2018

OH MY GAWD!!!
I wish I had watched your video a long time ago! I have literally spent hours upon hours manually copying cells or creating extra worksheets to do this very thing. Thank You, Thank You, Thank You!!!!

Kishor Dey - March 14, 2018

while selecting ALT and ; to copy only the visual cell. the below error is showing-
Cannot change part of a merged cell.

Please assist.

Kishor Dey - March 14, 2018

Cannot change part of a merge cell. The error is showing. Please assist.

Nico - February 27, 2018

Just what I needed! Thank you!

ALI - February 25, 2018

sir.I have problem to change data to filter cell but i cant
for example I have large number data.In first step I used that .but in 2nd step I need sum value from that data.for that to complete 2nd step I have to filter that data. and change value of that numbers. I want to copy data from other sheet and past to that filter data. but i cant. for that i need a help please.

M - February 21, 2018

thanks for the tips!

Yashwant - February 12, 2018

Thank you, it was helpul

Kevin Kline - February 8, 2018

How can I paste a formula into only visible cells? I have large sheets with hundreds of line. If I want to update Steel only for an updated formula. How can I keep it from overwriting hidden cells.

Kevin - January 19, 2018

Freakin’ Awesome! Thanks for this.

Luke - January 4, 2018

How can I do the opposite of what you’re describing? I’m copying A1 to D1 with B and C hidden. I want to copy/paste A1 through D1 into A2 through D2. I’ve been able to do this in the past. Now all of a sudden when I select A1 to D1 with B and C hidden, it DOESN’T select B and C. In the past it did. Any thoughts?

Why would the copy feature follow one logic and the paste follow another? I need them in sync. It used to work and now it doesn’t.

BIPASHA - January 4, 2018

Hello – very useful video but when I check on the cell which I want to copy (a filter has been applied) and select visible cells it is picking up the whole spreadsheet and not the cell I have selected. Why is this?

judi poker - December 27, 2017

Good blog you have here.. It’s hard to find excellent writing like yours nowadays.
I really appreciate people like you! Take care!!

Murthy - December 11, 2017

Really very helpful and time saving and neatly explained

Clare Buttery - December 4, 2017

Really helpful thank you Im a novice so very clear and easy to follow.

John M - November 29, 2017

Is there a way to paste non-adjacent selected cells into outlook without all the cells in between. Every time I select specific cells to copy/paste and paste into other programs besides excel, i get all the cells in between the first and last selected.

a1 first – selected in excel
a2 second
a3 third
a4 fourth – selected in excel

paste in excel:
First
fourth

paste in outlook/word
firtst
second
third
fourth

Maya - November 15, 2017

so simple and easy to follow!

Mi - November 14, 2017

Very Useful.
Thanks a lot for your Valuable Service!!!

IRIS GUTIERREZ - October 31, 2017

THANK YOU!!

Raj - October 29, 2017

Excellent. Very useful tricks. Keep them coming.

Thanx!!

Kerry - October 25, 2017

Thanks – very helpful. How can I do the reverse? cut data from an unfiltered column and paste it into a filtered column?

Kasim Mahmood - October 16, 2017

Thanks .. very helpful

Dawn - October 6, 2017

This is straightforward and awesome! Thanks for sharing.

Ashish Triapthi - October 6, 2017

Ctrl + Enter command really works, thanks otherwise I thinking of using VBA.

Dawn - October 5, 2017

Thank you! That was exactly what I was looking for and instead of fumbling my way around, it was nice to get not only a clear written tutorial, but the video. I just followed along on my other screen and voila!

MASOOD BIN AHMED - October 4, 2017

liked ! .. simple explanation… i would like to follow ..

Adam - September 30, 2017

Exactly what i was looking for thank you so much.

Tony D - September 15, 2017

From the “Paste to Visible Cells” video, I have a tip to help further. After you see the first half of the video and you’ve copied all visible cells to the new column via a formula, and you want to get rid of the formula: remove all filters, highlight the new column, copy, and use Paste Special to paste Values only. This will replace the formulas with the data from the formula. Note: This will work if you have no formulas you want to keep in the new column, since this method will wipe out all formulas.

Vinu G - September 6, 2017

simple way of teaching. very easy to follow. Thank you.

Thorsten - August 22, 2017

Searching on a way to copy only visible cells in Excel brought me to your page. Thanks for the excellent step by step description.

Abdul - August 19, 2017

Steps to easily paste on visible cells only in excel without any macro or additional software is as follows:
Suppose original data is:
A
E 200
G
N 400
K

You have hidden two rows for E and N, now the visible data is:
A
G
K

You want to paste 100 for A, 300 for G and 500 for K to get the following results:
A 100
E 200
G 300
N 400
K 500

The procedure to get this result easily is given below:

1. Copy the visible cells from the excel sheet

2. Paste the copied cells in a new excel sheet. You will get:
A
G
K

3. Paste the data you want to paste, in new excel sheet. You will get
A 100
G 300
K 500

4. Use VLOOKUP formula in your original excel file on visible cells, with reference to data in new excel file. Then remove the filter from original file to see all hidden cells. You will get the following result:
A 100
E 200
G 300
N 400
K 500

Bhupendra Singh - August 10, 2017

It really works. ALT + ; for filtered data. But there is a problem when more than 100,000 data is to be selected.

Joseph - August 2, 2017

Thanks so much, you have save me from possible embarassment, a my friend needed my assistance which relates to this copy and paste only the vissible.
Thanks

Emily - August 1, 2017

Thanks for sharing. I have often been frustrated with pasting visible cells only. Youo have solved it for me. Appreciate it

d - July 28, 2017

Thanks!

Andy - July 19, 2017

You are my guy from now on! Clear & Quick. And you surf too!

Martin Cunningham - July 9, 2017

Thanks for the tip. I have an excel table (table A) with a column (col 1) that looks up values from table B. I ran into a challenge trying to update col 1 to WHERE col 2 = . I filtered table 1 to show only the rows where col 2 = , and when I tried to copy and paste into col 1 it caused even the hidden rows to be updated, which was not what I wanted. I didn’t want to have to write something in VBA and was fortunate enough to find your article which showed me the Alt + ; trick. I copied from one of the filtered cells in [table A].[col 1], then selected the entire column, then hit , then paste > special > values, and it worked – only the filtered values in col 1 were updated. I was surprised i didn’t run into the “multiple selections” error. Thanks again.

arun rajappan - July 8, 2017

Your way of teaching is amazing, simple and humble and very easy to understanding

Justin - June 27, 2017

Great instructions. Added my first shortcut to the quick access toolbar (select visible cells). I’ll be back for more!

Rosy - June 25, 2017

You are a fantastic teacher. I would like to subscribe to everything you offer. One of the best explanations I have seem compared to other excel courses online. Even some of the paid courses are nowhere as good as how you show and teach. Keep up the good work.

Randy - June 20, 2017

Great Lesson!! You are very clear. I would like to subscribe to everything you offer. Thanks!