How to Select and Copy only Visible Cells, Columns or Rows in MS Excel 2007 and 2003?
If you are new here, Subscribe to Full Feeds or by Email or follow us in Twitter and receive free daily updates on Office and Windows Tips, Tricks and Tweaks..
When you have some cells, columns or rows hidden in Microsoft Excel from Office 2007 or 2003and you try to select or copy only the visible rows or columns but even the hidden rows or columns are selected and copied. (Note: When the data has been filtered using filter tool, it won’t select the filtered cells, rows or columns). There is a hidden tool in the Excel which allows selecting only visible cells, rows or columns. This command can be added to the toolbar by customizing it.
So how to select only visible cells, or in other words how to not select hidden cells, rows or columns while copying?
In MS Excel 2007, to add the Select Visible Cells Command, do the following steps:
Step 1: Right click on the toolbar on top and click Customize Quick Access toolbar.
Step 2: Under Choose Command From drop down menu, select Commands Not in the Ribbon.
Step 3: Scroll down and click on Select Visible Cells.
Step 4: Click on Add and OK.
Now you should be able to see the command in the quick access toolbar.
(If you are interested in this black color layout for Office 2007, read from How to Change the Color Scheme or layout for Office 2007?)
In MS Excel 2003, to add the Select Visible Cells Command, do the following steps:
Step 1: Right click on the toolbar on top and click Customize.
Step 2: Go to Commands tab and select Edit under Categories.
Step 3: Scroll down the list under Commands and click on Select Visible Cells.
Step 4: Now drag and drop the command to the toolbar.
How to use Select Visible Cells Command?
To use this command, hide all the cells, rows or columns that you want to hide. (You can do that by choosing the rows or columns -> Right Click and choose Hide or you can use the keyboard shortcut to hide rows or columns by Ctrl + 9).
Now select the area that you want to copy (If you want to copy the whole sheet, press Ctrl + A) -> click on Select Visible Cells command -> press Ctrl + C and you should be able to see the blinking mark around the hidden rows or columns. Do Ctrl + V where you want to paste. You should be able to note that it had copied only visible cells.
Update: One of the useful tip where you can use this feature is to delete empty rows in between in Excel.
Really helpful. Thanks
I had really hard time with this – Thanks a lot! Great solution!
[...] Step 5: Use Select Visible Commands, to select only rows that are visible and Press Ctrl + C to copy the entire worksheet. This doesn’t copy the hidden rows. (Select Visible Command is a hidden option in Excel, if you don’t know how to use it or where it is, read ‘How to select only visible cells in Excel?’) [...]
Top notch! This is exactly what I needed!
In older Excel versions only the visible rows were selected by default…
Thanks for that spent a good part of the day tryinhg to sort this, Sorted………..
u r tips works
on small sheets
but when the no. of rows \
are more then 20000 then it shows
this messege
Thanks very much for this. Have been struggling with this one for a while, particularly since 2003 behaves differently
Great work. Thanks so much.
Thank you very much, it is really helpful.
This icon was added by a fellow colleauge but lost it when upgraded to Office 2007 and had been a pain until now. Many thanks for the publisehed info, will save me time
Thank you very much!!
They changed this in Office 2007 and I couldn’t even find this in help. Thanks! This was a huge benefit.
Thanks so much for wonderful tips!
[...] Important Note: If you had written formula to manipulate the data in your Excel and the formulas call for data from hidden rows and columns, then deleting those information will cause incorrect result. In that case, you may want to copy only visible rows and columns to another Excel sheet. [...]
thanks so much it’s really helpfull, great work
This doesn’t work for me, I’m able to add the button and click it, but the copy + paste places ALL rows and cells not just visible into the new sheet.
THANKS BUDDY FOR YOUR INPUT
It was really helpful.Thank for the information.
A simpler way is to use a short cut. Select the cells and press (”Alt” and “;” at the same time) that does the trick…
realy good help