Copy & paste visible cells only [Excel Trick]

Posted on August 24th, 2017 in Keyboard Shortcuts , Learn Excel - 15 comments

Here is something annoying with Excel.

Open any Excel file with few columns of data. Hide some of those columns (select the columns and press CTRL+0). Now, copy a few rows of data. Paste it else where. Excel will paste the values in hidden columns too. We thought Excel would omit the values in hidden columns.

paste-includes-values-in-hidden-columns-too

 

What the filter Excel?!? I thought we were friends, but you annoy me with some of these quirks.

 

How to copy and paste just visible cells only?

There are two ways to do this.

1. Select visible cells, Copy and Paste

  1. Select the entire range you need to copy.
  2. Press F5, this opens Go to dialog. Click on Special button.
  3. Now select visible cells option. You can use the short cut sequence – F5 ALT SY to do all this.
  4. Now press CTRL + C
  5. Then go to target sheet and press CTRL + V to paste.

While this method works, it is too long and if you need to copy and paste several times, then it can be annoying.

Related: More about Go to special

This is where the second trick comes handy.

2. Cheat Excel in to copying just visible cells

If your source data is filtered, then when you copy, Excel copies visible cells only. But how to filter when you need to see all the data. Simple, we cheat.

  1. Go the end of your data and type something like “X” in the blank cell beneath.
  2. Now apply filters to the data. Press CTRL+Shift+L to do this.
  3. Then filter the new value you added in step 1.
  4. Copy now.
  5. When you paste, Excel will paste just visible cells. Viola!!!

copy-paste-visible-cells-only-trick

 

How do you copy paste visible cells?

We are almost at the end. Let me confess. I never had this problem. I know about Go to Special > Visible cells and use it if needed. But Jo (my wife) faced this issued yesterday and called me to ask how to copy visible cells. When I told her the option about visible cells thru Goto special, she is not all that thrilled, so I got thinking. I think the filter approach is better because you just do what you normally do – which is Ctrl C, Ctrl V.

What about you? Have you ever had to paste visible cells only? How did you deal with that? Share you tips and experience in comments section.

And oh yeah, Check out our quick tips section for some knock-your-socks-off level of awesomeness.

Introducing our Online Power BI Class:

Introducing Online Power BI Training from chandoo.org - check it out today

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

Click here to know more and join us.

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

15 Responses to “Copy & paste visible cells only [Excel Trick]”

  1. select all the cells.
    press alt+semi colon (;).
    only visible cells will be selected.
    copy.
    paste elsewhere.
    voila - only visible cells are pasted.

  2. Aquayum says:

    I think, the "x" option at the bottom is a really cool cheat option that I wasn't aware of, although I normally use the Alt semi Colon option which Nishkarsh mentioned above.

  3. B says:

    Alt + HFDSY all day

  4. Jason says:

    Is there a way to copy and paste visible cell values?

    • Salahuddin says:

      Assuming you are referring to spreadsheet which has formula and it derives values using those formula. And, you just want to copy the values and not the formula. In this case, select your range, then press ALT+ (Semicolon) together, this will select visible cells only. then go to the sheet/cell where you want to paste the data, there, instead directly pasting the data, perform paste special values. there are many ways to do that, easiest one is press ALT+Cntrl+V together, this will show a pop up window, select Values and press ok (or simply type v and enter). this should do 🙂

      • Jason says:

        Appreciate the feedback. Is there a way to copy paste values on top of the formulas working with only the visible cells?

  5. DME says:

    I agree with Nishkarsh, alt + semi-colon is a great time saver for a task like this!

  6. Lyn says:

    Hi I just learnt yesterday that if you Use ALT + ; it will select only visible cells. I am new to excel 2016 and finding it really hard to find my way around all the ribbons.

  7. Akhter says:

    This was very informative & i usually do this by using ctrl+G 🙂

  8. Nicolas says:

    I daily copy/paste visible cells from filtered sheets to another (master) sheet. I do it with a macro because copying visible cells is quite easy, however I never achieved finding an excel function to paste onto visible cells.

  9. GraH says:

    This is really one of the mysteries of Excel to me. I never had to do one of the above tricks. For ages I can just go and select visible cells and paste them elsewhere, completely hassle-free.
    I only learnt last year that this is not the standard Excel behaviour and an eye-opener to me, why with macros I always need to write that stuff with SpecialCells in the syntax.
    But it makes no sense to me why most of my colleagues can copy visible cells only, like I do and why only a minority cannot. They experience the stuff explained in the post. Though we all are using the same Office installation.
    I've been looking for a setting somewhere, but I haven't found it.

  10. Alec McQueen says:

    I am confused guys... there's a specific function icon you can use to do exactly this. I have it in my quick access menu at the top of excel. You just highlight what you want to copy, click the icon, and this copies only visible cells. No need for macros or shortcuts or trickery. Just exactly what you want with a single extra click on an icon.
    Cheers
    alec

  11. Chandoo says:

    @Alec, Nishkarsh, DME & Salahuddin: Thanks for sharing more ways to deal with this problem. I have written a follow up article explaining this. 🙂

  12. Rishi Kumar says:

    use Alt + ; for selecting visible cells only

Leave a Reply