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 to select the 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.

269 comments

Your email address will not be published. Required fields are marked *

  • Thanks for the tips, Jon.

    If anyone is comfortable with macros this one works for pasting into only visible cells.

    Select the cells to copy, then run this code:

    Sub Copy_Filtered_Cells()
    Set from = Selection
    Set too = Application.InputBox(“Select range to copy selected cells to”, Type:=8)
    For Each Cell In from
    Cell.Copy
    For Each thing In too
    If thing.EntireRow.RowHeight > 0 Then
    thing.PasteSpecial
    Set too = thing.Offset(1).Resize(too.Rows.Count)
    Exit For
    End If
    Next
    Next
    End Sub

  • Thank u so much for this tips…
    Really very useful…
    I would like to ask the vice versa of this copy and paste to visible cells only.
    like the normal column values to visible cells. how to do that one. plz give a solution.

  • Thanks Jon for the Tip of how to Copy & Paste Visible cells, that was a great help and would love to come back to you for more such useful tips. good work.

  • Hi.. I have to compile many files into one file, while using filter if I select one cell, and if I want to copy it to in different cell, I have observed it is being copied to hidden cell as well.. This is happening only in one particular file..

    Can some one please help me..

  • end(xlup)end(xlup)How can the macro from FRANCOIend(xlup)nd(xlup) DEKKER be modified to paste the copied filtered cells to a column where the data is sourced from that may be hidden due to filtering?

    For example say I had data in columns A to G (headings in row 1)1 and rows 2 to 10 were hidden due to being filtered how could I paste the copied visible cells to cell J2 down (which are hidden)?

    If I had a heading in J1 could end(xlup) be used to find the first blank cell in a selected column

    Regards,
    David

    • Hi David,
      I believe the Paste Visible feature of my Paste Buddy Add-in does what you are looking for. It allows you to copy and paste to a filtered range. This is not possible in Excel without a macro.

      Please let me know if you have any questions. I’m not sure I fully understand your question. Thanks! 🙂

  • Dear professor
    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.

  • Thanks a ton Jon. Your video was quite helpful in learning how to paste visible cells only.
    Thanks once again.

    • Hi Lisa,
      If you are pasting to a range with hidden rows, then the other cells are being pasted into those hidden rows. So you are probably only seeing the top item because the rest of the items are in hidden rows. For example, let’s say your visible cells that you copy are in cells A1, A5, and A6. All other rows are hidden. You copy those cells, select cell C1 and paste. You only see the contents of cell A1 in C1, and don’t see the contents for A5 or A6. Those cells were pasted in cells C2 and C3, which are currently hidden.

      This is a limitation of Excel. My Paste Buddy Add-in has a feature called Paste Visible that helps solve this issue by allowing you to paste to the visible cells.

      Please let me know if you have any other questions. Thanks Lisa!

  • Hi Jon,

    I’m glad i found your website. Do you have any idea how to paste on visible cell that contains read-only cell?

  • Thanks a lot, i work as a data analyst and for a long time now i have being spending a lot of time to do this. Now i can save myself and other colleagues the time and struggle, plenty thanks and u have been so helpful, keep up the good work.

  • Thanks Jon for this invaluable tip; further to your explaining about adding the Alt + ; shortcut to the QAT, I looked a little further and saw that there were two other useful commands I use frequently to add to QAT. Protect sheet and when you have cell(s) protected it then becomes unprotect command and a big plus for me is the Open Recent File; I have several files I keep pinned, so that is great to just click on that to get to them.

    These tips are a real eye opener for me and I look forward to your emails.

    • Hi Jim,
      Thanks for sharing! Those are great commands to add. I also use the recent files, but never thought of adding the protect sheet. That’s a good one!

  • Hi Jon! Thanks Thanks Thanks Thanks and Million of thanks for you. You helped me saving over 2 hours of work on a huge amount of data. Very useful. I really appreciate it.

  • from Excel i have Filter some data . While copying the data to E- mail Outlook Body all Colum are pasting . please Help ,

    thanks
    Maddy

  • Hi Jon! I like the tip on adding it in the quick command. Any idea how to make the Excel to always select visible cells only? I hated it when I always have to do the setting again & again, & when there’s one time I didn’t realized that I forgot to copy visible cells only, there goes my data accuracy… Thanks Jon!

    • Hi May,
      I don’t believe there is a setting for this in Excel. For me, the Alt+; keyboard shortcut has become second nature when copy/pasting from a filtered range. But it does take time and practice. This could probably be done with some VBA code, but not sure that it would be worth the overhead.
      Please let me know if you have any questions. Thanks! 🙂

  • Thanks for this tip. I added the command to my toolbar as I’ve needed this functionality in the past. Great time saver.

    • I am having the same problem. When I copy the visible cells to another sheet, it does appear to only past the visible cells, but when I look at my selection in the status bar, it still shows thousands of rows, rather than the small selection I just pasted, so the invisible data is still there in the background somehow, even though it doesn’t appear to show in the rows, and I can no longer make the data visible. This really slows down excel, because when I’m working with multiple sheets, where I’ve copied a selection of the raw data and pasted to a new sheet, I’m actually copying all the raw data too, so by the time I get to the 3rd sheet or so, excel can no longer handle the amount of data I’m asking it to deal with.

    • Hi Joe,

      The copy/paste visible does work between worksheets. Are you receiving the following error message: “that command cannot be used on multiple sections”? If so, that is because the sheet you are copying to has hidden rows/columns in the range you want to paste to. This is a limitation of Excel, but I do have an add-in that does this. It is the Paste Visible feature for the Paste Buddy add-in. You can download a free trial version to try it out. Please let me know if you have any other questions. Thanks!

  • work fine with I remembered it 2 hours ago, however when I copy a range of visible cells and paste at the bottom of the table it works fine. however if I paste in a different column it does not paste all the rows Very confusing

    I also want to past a visible range over a section of the table that has hidden rows and columns but it does not work any help. i.e. just like putting a post it on the page.

    thanks

    robin

    • Hi Robin,
      Great question. It is not possible to paste a visible range over a section of hidden rows or columns in Excel. You will get an error message saying, “that command cannot be used on multiple sections.”

      However, I just finished creating an add-in that will allow you to do this. I hope to have it available next week.

      There is also a trick you can do where you select the visible cells you want to paste to, then type an = formula to point back to the cells you want to copy, press Ctrl+Enter to fill all the selected cells with the formula. This isn’t a true copy paste, but will suffice for some cases.

      I will have an article and video with more details. Please let me know if you have any questions.
      Thanks!

      • Hi Jon

        First, thank you so much for saving my time too, though I already spent hours and hours finding a way to copy and paste only visible cells.

        However, I also come across same situation, where Robin is facing. I want to copy some visible cells and paste same format in another place where same hidden lines are exists.

        Waiting to see your new article and video soon.

        Rgds,
        Chaminda

  • Jon,

    This is a life saver in the scope of work I do I am always in the filter column and used to always do the control select each individual line. THANK YOU!

  • Jon,

    This has been solved a while back. here is the code (for an add-in):
    Sub Copy_Paste_Filtered_Cells(control As IRibbonControl)

    Dim rngSource As Range
    Dim rngDestination As Range
    Dim cell As Range
    Dim cc As Long
    Dim i As Long
    i = -1

    On Error Resume Next
    Application.DisplayAlerts = False

    Set rngSource = Application.InputBox(“Select the filtered range to copy. “, “Select Filtered Cells”, Type:=8)
    If rngSource Is Nothing Then Application.DisplayAlerts = True: Exit Sub ‘User canceled

    Set rngDestination = Application.InputBox(“Select the destination cell to paste to. “, “Select Paste Destination”, Type:=8)
    If rngDestination Is Nothing Then Application.DisplayAlerts = True: Exit Sub ‘User canceled

    On Error GoTo 0
    Application.DisplayAlerts = True
    cc = rngSource.Columns.Count

    For Each cell In rngSource.Columns(1).SpecialCells(xlCellTypeVisible)
    Do
    i = i + 1
    Loop Until Not rngDestination(1).Offset(i).EntireRow.Hidden
    rngDestination(1).Offset(i).Resize(1, cc).Value = cell.Resize(1, cc).Value
    Next

    End Sub

    • Awesome! Thanks for sharing Francois!

      The only issue I see is that it doesn’t account for hidden columns if you are pasting into an area with more hidden columns than the original copy area. But that can easily be resolved.

      Thanks again!

    • Hi Yaser,
      Yes, you use the shortcut on a pivot table. Typically the pivot table won’t have hidden rows unless you are applying a filter from outside the pivot table area, but you can still use the shortcut to select visible cells.

  • Hi, nice tip, but has anyone notice that if you select / copy visible cells only, those cells can not be pasted in visible cells of a next column (while you remain in “filtered mode”)…?

    Any workaround for the “paste” issue…?

  • I needed this last week. I knew it existed, but just could not remember what it was. Thank you sooooo much. I agree with all the wonderful comments. This is truly an awesome site, and I am going to learn everything you have to offer. Thanks.

  • Hi, thank you for sharing. However I need to paste back the rows and although I can now copy visible cells only I still don’t seem to be able to paste them exactly as they were – they are pasted into the consecutive rows rather than into visible rows as they were. Any tips?

    • Hi Kate,
      As you have probably found, this is not possible in Excel and you get an error message when you attempt to do so. However, I have received this question a few times since posting that article about selecting visible cells. I am working on a way to do this. It will require an add-in or macro to accomplish this, but I am going to make something that is very easy to use and also very fast. I agree that this process can take a lot of time. I’ll let you know when I have something.

  • Jon, I find your site remarkable. The instructions are well laid out; presented clearly and concisely.

    Every business having employees using Excel should require those employees to train using excelcampus.com. Imagine the $100,000s of dollars a company could save annually in recovered time wasted where people struggle with Excel, and the employee stress relief.

    • Wow Pat, thank you so much for the kinds words. That really means a lot to me, and gives me great assurance that I’m in the right place. I am so glad I can help others learn Excel and relieve some stress in the process. It’s my new mission to help put an end to the Excel struggle. 🙂
      Thanks again!
      Jon

  • I am so happy I am crying!! WOW…great tip. I want to keep exploring your site and learn more. It’s like an addiction now. Sharing it with coworkers too! Thank you

    • That’s awesome Andrea! You made my day. 🙂 It’s definitely a shortcut I like to show my co-workers as well.

      If you ever find yourself doing a time consuming task, and think there should be a faster way, feel free to shoot me an email. jon@excelcampus.com

      I don’t have all the answers, but enjoy learning and sharing with others. Thanks again!

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly