How To Copy & Paste Visible Cells Only (Video) - Excel Campus
211

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

  1. Select the entire range you want to copy.
  2. Press Alt+; to select the visible cells only.  You will notice that the selection is cut up to skip the hidden rows and columns.
  3. Copy the range – Press Ctrl+C or Right-click>Copy
  4. Select the cell or range that you want to paste to
  5. 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.

Go To Special Menu in Excel Visible Cells Only

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…

Find and Select GoTo Special Menu

2. Press Ctrl+G or the F5 key on the keyboard from anywhere in a worksheet.  Then press the Special button.

Go To Menu in Excel - 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.

Copy Visible Cells to Outlook Email

Here are the steps to copy the visible cells to an email :

  1. Select visible cells in Excel – Alt+;
  2. Copy the range – Ctrl+C
  3. Open an email in Outlook
  4. 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 211 comments
matt - May 8, 2017

a big thank to u…a life saver

Reply
Hussain - April 29, 2017

Dear Sir,
Have a Great Day Ahead,

Please see below mentioned data that I tried my best for your better understanding.
I want result in Cell E3 when I filter data in Cell A7, (Vendor Name should be appear in Cell E3). Hope to hear from you as soon as possible.

BR/
Hussain

(Cell E3) Vendor Name: Abdul Aziz Fahad Al Hajri Est

(Cell A7) Vendor Name
Mana Al-Haider Cont. Est.
Ali-Ahmed-Al-Kanfari Gen. Co. Est.
Abdul Aziz Fahad Al Hajri Est
Danat Al-Rayan

Reply
Jahangir Alam - April 29, 2017

how may i filtering data special pest int the same cell ?

Reply
Jennifer - April 25, 2017

Jon – can you help with copying from a table with hidden columns? When pasting, the hidden column data gets inserted into the visible columns and the data gets all out of whack! Column B hidden. Copy single row, columns A to D and data in column B appears in Column C when pasted.

Reply
Christie - April 20, 2017

You are a life/time saver! Thanks so much!

Reply
Tuisiong - March 14, 2017

awesome!! there’s even a keyboard shortcut.
thanks mate

Reply
Ranganath Satam - February 25, 2017

date : 25-02-2017 time 20.36pm

Thank you very much jon, your excel command tips for windows of visible cell copy paste

worked well for my sheet which has 30 columns , and i want to select 5 out 30 paste your this command saved my lot of time.

thanks angain , keep sending such useful tips.

thanking you

yours truly

Ranganath Satam.(India)

Reply
Thank you - February 23, 2017

this helped me out! Quick and easy, right to the point.

thank you,

Angel

Reply
Hamid Hadi - February 8, 2017

Dear Sir,
I used the shortcut of Ctrl+Enter to past in visible cells only, it worked perfectly.
now i want to remove the sheet from where I copy the data. when I removing the sheet(main sours of data) the pasted data are automatically change looking like(#REF!)
so the data should copy without formula because I just need the value to be past not format and formulas or after past the link between them should cut.
because if the link is not cut when we removing the main sours from where we copy the date its changing or give us error(#REF!)
one more comment, the tactic(Alt+Enter) to past in only visible area is just working in same sheet and workbook or one sheet and workbook.
if we copy from other workbook and past to another workbook, it’s not working.
you have any recommendation for this problem?
Thanks for your guidance

All the best
Hadi

Reply
Hamid Hadi - February 4, 2017

Thanks Mr. Jon for your guidance i appreciate that this is so helpful for everyone who use excel.
one comment i have is that i watch the video of Past in visible cells that was great and helpful and you developed a button for past in visible cell how can we active this button in Excel and use it everywhere.
if possible please assist me to add this button in excel and use that.

Thanks so much in advance
Hadi

Reply
    Jon Acampora - February 4, 2017

    Hi Hamid,
    You can download and install the Paste Buddy Add-in to use the Paste Visible feature in any workbook you have open on your computer.

    Reply
      Hamid Hadi - February 7, 2017

      Dear Sir,
      i used the shortcut of ALT+Enter to past in visible cells only, it worked perfectly.
      now i want to remove the sheet from where I copy the data. when I removing the sheet(main sours) the copied data are automatically change looking like(#REF!)
      so the data should copy without formula because i just need the value to to be past not format and formulas or cut the link of them.
      because if the link is not cut when we removing the main sours from where we copy the date is changing or give us error(#REF!)

      Thanks for your guidance
      All the best
      Hadi

      Reply
        Hamid Hadi - February 7, 2017

        Sir sorry for mistake (Alt+ Enter)that i made in my above comment that was Ctrl+enter.

        one more comment, the tactic(Alt+Enter) to past in only visible cells is just working in same sheet and workbook or one sheet and workbook.
        if we copy from other workbook and past to another workbook, its not working.
        you have any recommendation for this problem?

        All the best
        Hadi

        Reply
Saeed - January 31, 2017

Hi

I have followed your information about how to cut and paste visable cells only however when i paste to the column it only pastes the first cell and not the others but does highlight all the visable cells i wish to paste any help with issue?

I am using Excel 2010

thank you in advance for your help

Reply
tian - January 13, 2017

Hi Jon,

I have data with big file in Sheet1 and I am trying to filter the data and it is filtering but when I am trying to copy visible cells only it is showing error: Microsoft Office Excel cannot create or use the data range reference because it is too complex. Try once or more of the following:
1. Use data that can be selected in one contiguous rectangle.
2. Use data from the same sheet.

Can you help me how to copy that one

Reply
    Jon Acampora - January 20, 2017

    Hi Tian,
    You might want to try sorting the data first, so there are less non-contiguous ranges to copy. Here is a technet post that describes more about that issue. I hope that helps.

    Reply
Christian del Rosario - January 13, 2017

can you help me copy big file not including the hidden files.
there is prompt Microsoft excel cannot create or use the data range reference because it too complex.

there is other way to copy the file?

Reply
    Jon Acampora - January 20, 2017

    Hi Christian,
    You might want to try sorting the data first, so there are less non-contiguous ranges to copy. Here is a technet post that describes more about that issue.

    Reply
Tim - January 4, 2017

Thanks for the help.

Reply
Umair - December 30, 2016

When i’m trying this on merged cells, it does not allow me to copy.

How do I copy only visible cells with merged rows

Reply
    Jon Acampora - January 4, 2017

    Hi Umair,
    Merged cells can cause a lot of issues like this. I’m not sure it’s possible. I would recommend using the “Center Across Selection” option instead of merged cells.

    Reply
Arul - December 7, 2016

HI ,

I want to copy a list of items from a normal column and paste it to a filtered column ..
i.e say Column A is 1,2,3,4,5 from another sheet and Column B is a, b , a , a , d , a .. i would like to filter column B for a and paste .. the Column C as 1,2,,3,4,5,
i.e , a=1 , a=2 , a=3 etc ..

Can you help on this .. Thank you ..

And .. Good Job .. Nice work .. keep it going ..

Reply
Kari - November 15, 2016

I have filtered cells and want to copy and past a formula into the visible cells only. Will this work for that as well?

Thanks

Reply
    Jon Acampora - November 21, 2016

    Hi Kari,

    Yes, the Paste Visible feature of Paste Buddy has a Paste Option that allows you to paste values, formulas, formats or any of the Paste Special commands. I hope that helps.

    Reply
Gordon fogarty - November 11, 2016

Excellent short-cut advice on coping visible cells only.
Thanks

Reply
Sandy - November 5, 2016

Excellent. Great job. Thanks for saving me time.

Reply
Kari - October 27, 2016

This video was exactly what I was looking for! Excel on a Mac is pretty terrible and I could not find the shortcut in the Help wizard. Your result was the first Google search option and should be because your video is very helpful!

Reply
    Jon Acampora - November 1, 2016

    Thank you Kari! I really appreciate the nice feedback, and happy to hear you got it working on the Mac. 🙂

    Reply
Carole Kearns - October 27, 2016

The advice on copying visible cells only doesn’t seem to work in Microsoft Office Professional Plus 2103. I tried all 3 ways but to no avail.

Any suggestions?

Thanks
Carole Kearns

Reply
Delaney - October 18, 2016

I’ve had no problem with bulk coopy/paste on filtered cells until today. Now when I copy, the data pastes into the hidden cells. Any idea why this would be happening now? It’s been fine for months. Did I change something?

Reply
Thierry - October 6, 2016

Thanks a lot

Reply
Debra Lobin - September 28, 2016

Easy to understand. The step-by-step instructions along with the video gave me exactly what I was looking for.

Thanks very much!

Reply
Gary - September 26, 2016

Does this only work for copy from the same document? I’m trying to copy from a completely different document and it’s not working.

Reply
    Jon Acampora - September 27, 2016

    Hi Gary,
    At this time the Paste Visible feature of Paste Buddy only works within the same workbook. I have plans to update that in the future to copy between workbooks, but not sure when that will be available yet.

    Thanks!

    Reply
Shirin - September 20, 2016

Hello, excellent job. I would love to know how to develop add-ons in Excel. any reference that can help me?

Reply
Thao - September 15, 2016

Awesome, this is very helpful for me. Thank you so much, Jon. It’s help me save alot of time instead of copy and paste the visible cells manually!

Reply
Jacky - September 13, 2016

Thank you

Reply
John - August 24, 2016

The examples show how to copy the visible cells only. I want to know how to paste the data in visible cells only which has been copied from normal cells.

Reply
oanh - August 10, 2016

This was extremely helpful.

Reply
viktor - August 5, 2016

I need to copy the formulas from the visible range of cells top to down to a visible range of cells in columns left to right

Reply
JSG - August 5, 2016

this approach does not seem to work on xlsx. ControlC results in an error message about merged cells. any other approach? Cheers,

Reply
    Jon Acampora - August 8, 2016

    Hi JSG,
    The copy will not work if part of the merged cells are hidden by a hidden row or column. With merged cells, all the columns or rows in the merged cell will need to be visible. The solution is to first unmerge the cells. I hope that helps. Thanks!

    Reply
Sanj - August 3, 2016

Hi Joe,
I have to copy data from my work sheet to summary sheet with only quarterly totals, I reference Q1 by (=’sheetname’!cellno) & then when I starch (fill the series) to Q2, Q3 …. Q10, it picks up hidden cells. How to pick up after skipping 3 next cells?

Reply
Andrew - July 21, 2016

Hi Jon,

Very Useful, Very Helpful and Very Clear

Thank you very much for your time to put this all together!

Appreciated very much!

Reply
Terena - June 29, 2016

Is it possible to set it up in the preferences somewhere so that it will always paste only visible cells?

Reply
Chris - June 23, 2016

Can Paste Buddy copy from Word and paste only in the visible cells?

Reply
Shelly - June 2, 2016

Awesome – thank you for saving me time!!

Reply
balasubramanyam - May 21, 2016

thanks for help, was very helpful

Reply
Octavio - May 15, 2016

Hi Jon

I tried to make it work in MAC but it doesn’t work.
I’m using Excel 2011 and trying to make a macro to copy the selected cells from a table (filtered) to another sheet in the same workbook, the idea is not to select the cells manually, I need that they be selected from the Macro and then copied and pasted to the other sheet, thus I select the columns instead.

Reply
Reem - May 9, 2016

Thank you Jon. This is very helpful.Love keyboard shortcuts!

Reply
vinod - April 18, 2016

thanks for guiding me to copy from hidden files.this problem was being faced by me long time finally it solved. thank you so much

Reply
Wendy - April 4, 2016

I had about 250 lines that I wanted to copy but with over 4000 hidden lines I didn’t want to copy. I knew there was a way but had no idea how. One quick google search brought me here. Thank you for the quick and easy walk through. This is going to be a very useful tool for me.

Reply
Bharat Modi - March 18, 2016

How do I copy visible data from excel table and paste on visible empty raw and cell on same excel table?

Reply
Marta - February 25, 2016

THANK YOU FOR THIS ONE!!! Couldn’t be more clear!

Reply
Dawn - February 24, 2016

Thanks so much, really helpful and well explained.

Reply
Salman - February 24, 2016

Thanks a lot.

You are doing a great job bro keep it up.

Reply
Betsy - February 10, 2016

Hi. How do I paste data to a filtered list and not paste into the hidden cells?

Reply
    Jon Acampora - February 11, 2016

    Hi Betsy,
    It’s not possible in Excel through a paste command. However, my Paste Buddy add-in had a feature called Paste Visible that will do this.

    I hope that helps. Thanks!

    Reply
      tian - January 13, 2017

      Hi Jon

      I have 20 Lacks above Data in Sheet1 and I am trying to filter the data and it is filtering but when I am trying to copy visible cells only it is showing error: Microsoft Office Excel cannot create or use the data range reference because it is too complex. Try once or more of the following:
      1. Use data that can be selected in one contiguous rectangle.
      2. Use data from the same sheet.

      Reply
        Jon Acampora - January 20, 2017

        Hi Tian,
        You might want to try sorting the data first, so there are less non-contiguous ranges to copy. Here is a technet post that describes more about that issue. I hope that helps.

        Reply
TIMUA - February 9, 2016

Thanks a lot for “ALT+;” !!!
It is marvelous. Everytime I used to do it like – CTRL+G then ALT+S then Y key…

You are my hero of the week!

Reply
Naresh Kumar - February 1, 2016

Dear Jon,

Its not working for me. My requirement is to paste the cell’s details in the required cells. you suggested that copy and pasting for all the cells. but i need only the selected cells to paste the details.

i want to paste the values in the 5th row and the 6th row. but it’s not working please help me..

Reply
Gideon - January 29, 2016

Great tip. Saved hours of my time. I work with big/ large spread sheets. I consider myself as intermediate-semi advanced in excel, but I always make it a point to learn key board short cut tricks almost one every 2-3 weeks. Thank you.

Reply

Leave a Reply: