Excel: How to copy only Visible Cells

This is a frequent frustration. You copy some data which contains hidden rows and columns. You blindly assume only the visible cells will be pasted.  But when you paste it, even the hidden cells appear. This is non-intuitive and disturbing. Fortunately, there is an easy solution available.  Read on to find out how (Estimated reading time 3 min)


The problem

The problem is that by default, the selection ignores the hidden status. Therefore, we will need to ask Excel to ignore (not select) the hidden cells explicitly.

Here we have data with few rows hidden. Copying and pasting the rows from 1 to 6 will paste all the cells. We want Excel to select only the visible cells.

Hidden cells in Excel


How to copy only visible cells

  1. The first step is to select the data as usual. But DO NOT copy it yet.
  2. Type F5 or Ctrl G – which opens the Go To Special dialog.
  3. Click the Special button. This provides options to select specific cells depending upon the criterion you choose.
  4. Choose Visible Cells Only and click Ok.Go To Special dialog with Visible Cells Only selected
  5. Copy the data now. Notice that only visible areas are copiedCopying only visible cells
  6. Now when you Paste, it will work as expected. That’s it!

This method is also useful if you want to copy only the collapsed sub-totals.

The keyboard shortcut is Alt ;   (semicolon).
By the way, Ctrl ; gives you todays date and Shift Ctrl ; gives you current time.

Learn more about GoTo Special

Go To Special  called “special” for a reason. It is absolutely in your interest to look at all the powerful yet little known options in the Go To Special dialog and utilize them whenever required. Read these two articles and then you will have the power!

How to learn confusing options easily
Applied Knowledge: Go To Special – Practical Usage

4 Responses

    1. Hi Khushnood… Thanks for pointing this out. Forgot to put the shortcut. Wrote the article in a hurry during a flight. I have updated the article now.

    2. It’s my pleasure and privilege Doc.

      No need to explain why the shortcut was not included. 🙂
      You write so much, and so often to share your knowledge, so I feel that it’s a small thing to add my 2-bits to your post.

Queries | Comments | Suggestions | Wish list