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)
Contents
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.
How to copy only visible cells
- The first step is to select the data as usual. But DO NOT copy it yet.
- Type F5 or Ctrl G – which opens the Go To Special dialog.
- Click the Special button. This provides options to select specific cells depending upon the criterion you choose.
- Choose Visible Cells Only and click Ok.
- Copy the data now. Notice that only visible areas are copied
- 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
Keyboard shortcut to select visible cells only:-
Alt ;
(Alt + semi-colon)
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.
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.
Love this one.