• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Copy and Paste Visible Cells Only

You are here: Home / Excel / Excel Copy and Paste Visible Cells Only
Excel Copy and Paste Visible Cells Only
August 4, 2015 by Mynda Treacy

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:

select range of cells

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:

copy formula to range of visble cells

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:

  1. Copy cell E2 to the clipboard – just select it and press CTRL+C
  2. Select the range you want to paste to. In my case E3:E51
  3. Press CTRL+G to open the Go To dialog box and then click ‘Special’ in the bottom left:

    open the go to dialog box

  4. In the Go To Special dialog box select the ‘Visible cells only’ button and click OK.

    go to special dialog box

Notice how each group of cells are individually selected:

visible cells selected

  1. 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:
  2. pasting into visible cells

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:

copying hidden rows

We can use the same technique to only copy visible cells:

  1. Select the range A2:E9
  2. CTRL+G to open the Go To dialog box
  3. Click ‘Special’
  4. Select ‘Visible cells only’

You can see there is a subtle line between rows 2 and 4 indicating row 3 is not selected:

select visible cells

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

    paste visible cells

Filtered Tables

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.

Excel Copy and Paste Visible Cells Only

More Excel Posts

linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.


Category: Excel
Previous Post:PivotTable Calculated Items by PositionExcel PivotTable Calculated Items by Position
Next Post:Excel Fill SeriesExcel Fill Series Tool

Reader Interactions

Comments

  1. rogerio

    May 14, 2021 at 10:10 am

    Great! it worked perfectly.
    Thank you

    Reply
    • Mynda Treacy

      May 15, 2021 at 1:34 pm

      Great to hear, Rogerio!

      Reply
  2. Doug Vodicka

    February 6, 2021 at 12:47 am

    I’m having the problem where I copy a value from one cell in a filtered column, select the rest of the cells in the column, paste, and EXCEL pastes the value on hidden and visible cells. This happened yesterday, and again today, with a shutdown in between. So for right now, it’s a repeatable issue. The problem is repeatable in different spreadsheets. So, wondering if there is a way to get Microsoft to look into this?

    Reply
    • Mynda Treacy

      February 6, 2021 at 2:20 pm

      Hi Doug,

      I can’t replicate that issue in my own files. A workaround is to use Go To Special to select the visible cells before pasting. See instructions in the post above under the heading “Paste Visible Cells Only”.

      If you want me to test your workbook, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Mynda

      Reply
  3. Emily Fong

    February 2, 2021 at 11:00 pm

    Heyyy

    Good day! I’ve tried using your way of pasting visible cells only. But the problem is that the excel doesn’t allow me to do so. It says that the copy area & the paste area aren’t the same size. But I’ve checked. it’s actually the same size. Do you have any idea what’s going on?

    Reply
    • Mynda Treacy

      February 3, 2021 at 9:01 am

      Hi Emily, when you’re pasting you can only select a single cell. If you try to select non-contiguous cells it will not allow it.

      Reply
  4. Prabhjeet

    October 11, 2020 at 7:24 pm

    thanks it worked

    Reply
    • Mynda Treacy

      October 12, 2020 at 8:36 am

      Great to hear, Prabhjeet!

      Reply
  5. Kelly

    August 5, 2020 at 2:21 am

    This did not work for me. When I attempt to paste after selecting visible cells only, Excel sends me an error message:

    “This can’t be done on a multirange selection.Select a single range and try again.”

    Reply
    • Mynda Treacy

      August 5, 2020 at 8:52 am

      The error sounds like you’re trying to copy a non-contiguous range and paste it into another non-contiguous range. This isn’t possible because the ranges are probably different sizes.

      Reply
  6. Carl

    June 5, 2020 at 9:47 am

    Hey! Great article, thanks.
    +1 to what Mohit said. Might want to consider updating the post since that’s what was tripping me up and it’s kind of buried in the comments.
    Thanks!

    Reply
    • Mynda Treacy

      June 7, 2020 at 10:11 am

      Glad it was helpful, Carl.

      Reply
  7. ili maier

    March 4, 2020 at 10:22 pm

    Great! it worked perfectly.
    Thank you

    Reply
  8. Marco Mariano

    February 21, 2020 at 6:02 pm

    this is only applicable if you’ll be pasting the same data (in your case, its formula) only…

    how about pasting different sets data?

    Reply
    • Mynda Treacy

      February 22, 2020 at 11:41 am

      Hi Marco,

      You can’t paste data that has been copied from non-contiguous cells to different non-contiguous cells.

      Mynda

      Reply
      • Andy

        July 13, 2021 at 6:56 pm

        Hi Mynda. You are my go-to person for all problems Excel. Thanks for the excellent advice over the years!
        When you say that “you can’t paste data that has been copied from non-contiguous cells to different non-contiguous cells” does this apply to filtered tables?
        I am sure that I have, in the past, copied values from a filtered column in a table to an adjacent column, and this worked. But recently I have found that, sometimes, when I paste the column into an adjacent column in the same filtered table, it pastes the values into contiguous cells, including filtered non-visible ones. That can make for a real mess when you unfilter the table!
        This has cost me many hours work this week ….

        BTW Is there a parallel situation with fill down or copy down? In a filtered table, can this ever enter data in non-visible rows?

        Reply
        • Mynda Treacy

          July 14, 2021 at 11:38 am

          Hi Andy,

          Good questions. I haven’t tested Fill Down to know off the top of my head, but I suspect it doesn’t respect hidden/filtered cells.

          I’ve found the whole copying and pasting in tables to be inconsistent, so I generally avoid it where possible!

          Mynda

          Reply
          • Andy

            July 16, 2021 at 1:12 am

            Many thanks for your time, Mynda. I suppose the lesson is always sort rather than filter before copy/filling. Re: copy down, I’ll experiment some time and feedback …

          • Mynda Treacy

            July 16, 2021 at 10:46 am

            Yes, I think that’s wise 🙂

          • Andy

            October 12, 2022 at 9:09 pm

            Actually I forgot to get back to you on this. If you have, say, an advanced filtered list I have found you can fill down, but not copy/paste. That requires any list to be sorted to allow. Hope that helps. Fill-down certainly has helped me as it saves hours!

          • Mynda Treacy

            October 12, 2022 at 10:09 pm

            Good to know, Andy.

  9. Lola

    January 10, 2020 at 9:07 am

    Hola,
    could you give a tip on how to copy visible cells only and paste visible cells only??
    They should have the same size and Alt +;, copy, select past range, alt+;, paste doesn’t work…

    Reply
    • Catalin Bombea

      January 11, 2020 at 3:49 am

      Hola Lola,
      Might be the same size, but there are many other unseen things that can go wrong. Can you upload a sample file on our forum?
      You can also try an addin, Paste Buddy for example.

      Reply
  10. ABHIMANYU

    January 9, 2020 at 8:39 pm

    ITS DOSE NOT WORK

    Reply
    • Mynda Treacy

      January 9, 2020 at 9:25 pm

      Sorry you’re having trouble getting it to work. I suspect you’re trying to do something it’s not designed to do. If you’d like to post your question and sample Excel file on our forum we can see if there’s a workaround that suits your needs.

      Reply
  11. Steve

    November 20, 2019 at 7:13 am

    This didn’t work, it still pasted in the hidden areas. Thanks for nothing.

    Reply
    • Mynda Treacy

      November 20, 2019 at 9:03 am

      Sounds like something went wrong with steps 2 through 4 in the “Paste Visible Cells Only” section. If you care to post your question and Excel file on our forum we can take a look and see if we can reproduce the problem.

      Reply
  12. Shruthi

    October 22, 2019 at 7:27 pm

    Hi, I had to copy from a continuous range and paste it in visible cells only. This method does not work. If you could give me a solution it would be great.

    Reply
    • Mynda Treacy

      October 22, 2019 at 9:58 pm

      Can’t be done as far as I know, sorry. It’s because the ranges are different shapes; one is contiguous and the other is not.

      Reply
  13. Muntaser

    August 16, 2019 at 4:00 am

    I did all the above steps. However, still can’t past here are an error always appear wihich is : This can’t be performed on multiple selection. I think there are add on feature should be add to my excel program anyone can give me the link to download this add on feature for Ms Excel 2016?

    Thank you in advance for your cooperation.

    Reply
    • Mynda Treacy

      August 16, 2019 at 1:05 pm

      Hi Muntaser,

      There’s no add-in required. The selected ranges must include the same rows or columns. For example, you can’t copy cells A1:B5 and D6:E10 in the one action. But you could copy A1:B5 and D1:E5.

      Mynda

      Reply
  14. derek

    May 28, 2019 at 1:48 am

    “You can’t paste this here because the copy area and paste areas aren’t the same size” Yum…yes they are…. Thats it I’m moving to Google Sheets!

    Reply
  15. orgilmas

    May 26, 2019 at 5:12 pm

    this is not working

    Reply
    • Mynda Treacy

      May 27, 2019 at 7:57 am

      Perhaps you can post your question and Excel workbook on our Excel Forum where you can describe the steps you are taking and we can help you further.

      Mynda

      Reply
  16. Mohit

    May 18, 2019 at 12:06 am

    Hi Mynda:

    I just faced this same issue when copying a formula from one cell and pasting values only on a filtered column. It pasted on all rows of the column and not on the visible cells only.

    That should replicate the scenario that you mentioned above. Thanks for the post

    Reply
    • Mynda Treacy

      May 18, 2019 at 1:47 pm

      Thanks, Mohit.

      Reply
  17. jackie

    May 26, 2018 at 12:25 am

    That doesn’t work when I go to paste on non-contiguous cells in another column. I get a error msg which says “That command cannot be used on multiple selections”

    It allows me to copy as you indicate above but doesn’t allow the paste to non-contiguous cells

    Reply
    • Mynda Treacy

      May 26, 2018 at 8:00 am

      Hi Jackie,

      Unfortunately, that’s a limitation of this technique. i.e. you can’t paste to non-contiguous cells.

      Mynda

      Reply
  18. thomas

    September 2, 2017 at 12:43 am

    Pasting something only into the visible cells of a filtered list is not possible and that’s a real shame. The workaround I do is using a plugin called Power-user that does that among other stuff: https://www.powerusersoftwares.com/copy-paste-visible-cells-only

    Reply
  19. Jim

    July 29, 2017 at 3:15 am

    In Excel 2010, in a spreadsheet with hidden rows, can I copy a columnal range of cells to another columnal range of cells without affecting the hidden cells? For example, copy b2,b4,b6 to e2,e4,e6 without overwriting hidden cells e3 and e5?

    Reply
    • Mynda Treacy

      July 29, 2017 at 4:25 pm

      Hi Jim,

      No, sorry.

      Mynda

      Reply
  20. Brandon

    June 3, 2017 at 6:22 am

    I just discovered from my excellent coworker a much faster way to select visible cells – after selecting the range needed, just press “ALT + ;” and it switches to only highlighting the visible cells, the same as the multiple steps above.

    Posting this in the hopes that it helps others running into this same issue!

    Reply
    • Mynda Treacy

      June 3, 2017 at 2:04 pm

      Nice tip, cheers Brandon.

      Mynda

      Reply
  21. David

    March 4, 2017 at 3:28 am

    This is an absolutely fantastic time saving feature. AWESOME!!

    Reply
    • Mynda Treacy

      March 4, 2017 at 7:09 am

      Thanks, David 🙂 Glad you’ll find it useful.

      Reply
  22. chichi

    December 15, 2016 at 6:36 pm

    I can’t do these steps, it appears that the nformation cannot be pasted because the copy are not the same size and shape.
    please help.

    Reply
    • Catalin Bombea

      December 15, 2016 at 8:13 pm

      Looks like you have merged cells in the paste range?
      There is not much to do about this, you have to make sure the copy and paste range have the same size.
      Catalin

      Reply
      • Monica

        February 1, 2017 at 11:23 pm

        i counted manually the cells have the same size not merged, it does not work, should I have an add installed to make it work????

        Reply
        • Catalin Bombea

          February 2, 2017 at 12:07 am

          Maybe you can upload a sample file so we can see your situation. Open a new topic on our forum, and upload a file with that problem.
          It will be easier to help you, thanks for understanding.
          Catalin

          Reply
          • Clare

            May 12, 2017 at 1:31 am

            I’ve had a similar problem and I think its because there are some hidden cells in the selection i.e. I am using filtered data and it will only let me use this function on subsequent rows 1-5 e.g., when the row then jumps to 8 (because it is filtered) it is still counting the rows not included in the filter so is saying it isn’t the same size as the data I want to paste. Haven’t found a solution for this yet :/

          • Catalin Bombea

            May 14, 2017 at 2:03 am

            Well, not every problem can be solved exactly as we like, unfortunately. But there are so many ways to get to the same result, you will just have to find another way. If you need more help, maybe uploading a sample file to our forum will clarify the problem.
            Cheers,
            Catalin

  23. jim

    August 6, 2015 at 10:42 pm

    I would always use the select, type formula, ctrl-enter option rather than type, enter, copy, select, paste
    not only fewer steps, but avoids copying formatting, validation and comments

    Note that if you copy and paste a non-contiguous selection, then formulae are converted to values

    As well as using alt-; as a shortcut for visible cells, Grouping is much quicker with shortcuts alt-shift-right to group and alt-shift-right to ungroup. I haven’t used the subtotal tool for years, it certainly used to be flawed when used beyond 1 level and now with pivot tables, why would you?

    Jim

    Reply
  24. Cathy

    August 6, 2015 at 1:34 am

    I love your e-mails and tips! They are always informative and easy to follow. With the “Excel Copy and Paste Visible Cells Only” tip I encountered a small challenge that I have yet been able to resolve. That challenge is actually with the subtotal grouping. In all honesty I don’t use this function much so I’m guessing that I’m missing some really simple step. So here’s my problem, I recreated your table with the subtotals but the grouping does not let me hide just the “subtotals”. I have only been able to get it to hide the data. My “outline” shows 1-3 where your example only had 1 and 2.

    Any assistance you can provide would be appreciated.

    Thanks,

    Cathy

    Reply
    • Mynda Treacy

      August 6, 2015 at 10:06 am

      Hi Cathy,

      The Subtotal tool will automatically insert groups to hide the detail rows, I manually inserted groups to hide just the subtotal rows. You’ll find the Group tool beside the Subtotal tool on the Data tab of the ribbon.

      Kind regards,

      Mynda

      Reply
  25. pmsocho

    August 5, 2015 at 9:54 pm

    Thanks for the article.
    You forgot to add that we can select visible cells only with the shortcut:
    left Alt + semicolon

    Reply
    • Mynda Treacy

      August 6, 2015 at 9:58 am

      Wow, I didn’t know that! Awesome tip.

      Cheers,

      Mynda

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.