In this tutorial, you will learn about visible cells and how to copy and paste the visible cells only in Excel.
So for that, you need to know our dataset as shown in the below image.
Here we have twelve-month data from January to December of all four zones (I, II, III, and IV).
And for some reason, we want to find the figure of our first and the last quarter i.e. Jan to March and Oct to Dec dataset.
So, we need to hide the rest of the dataset that is not required.
Here we are using the dataset as shown in the image below:-
And you can see that we need to select or copy only the bordered area of 1st Qtr. and 4th Qtr.
What are the Visible Cell in Excel?
First of all, we need to know that what are the visible cells in Excel?
So for that, we need to follow the below procedure step by step.
First, select the entire rows or dataset which you want to hide.
And press Right-Click from mouse and click on the ‘Hide’ option from right-click properties.
See that our selected rows are disappeared now and see below the new dataset.
After hiding selected rows within the dataset, the remaining part of the dataset is known as Visible Cells in Excel.
Now we need to copy this dataset to any other place.
So for copying the dataset, we will select all the visible cells and press the (Ctrl + C) command from the keyboard.
Then go to the destination area where we want to paste the copied dataset.
Press the (Ctrl + V) command as the keyboard shortcut.
See that not only the copied dataset pasted but all the hidden dataset is also posted here.
So this is not what we want to paste.
We want to paste only the selected or visible dataset in Excel. i.e., without any hidden rows.
Here we have three (3) simple and easiest methods to get rid of this problem.
Select the Visible Cells Only – Keyboard Shortcut (Alt +;)
- Select all the visible cells in the dataset to be copied.
- Press keyboard shortcut (Alt +;) (Keep pressing ALT key and then press (;) semicolon) from the keyboard for locking the selected area.
3. Then press the (Ctrl + C) command from the keyboard shortcut to copy this dataset.
4. Then go to the destination area where you want to paste it.
5. Press (Ctrl + V) as a keyboard shortcut from the keyboard to paste this.
See all the visible cells only are pasted here.
Select Visible Cells Only – by Go to Special Method
First of all the select the visible cells only in the given dataset.
Then go to the Home tab, and then the Editing group, and then find the ‘Find & Select’ option.
Click the drop-down here, under ‘Find & Select’ and click on the ‘Go to Special’ radio button from the given options.
You will find the below ‘Go-To Special’ dialogue box.
Select the ‘Visible Cells Only’ option from the given options.
Press the “OK” button at the bottom.
See that visible cells are locked in the selected dataset.
Then Copy it by pressing the (Ctrl + C) command as the keyboard shortcut.
And again go to the destination area and press the keyboard shortcut (Ctrl + V) to paste the dataset.
See all the selected visible cells only are pasted.
Select the Visible Cells Only – Adding Command Button in QAT
In this method, we will add a command button in the Quick Access Toolbar (QAT) area.
For adding a command button, click on the drop-down arrow in the QAT ribbon.
And select the ‘More Command’ option from the list here.
See image:-
Once you click on the ‘More Command’ option, you will get the following “Excel Option” dialogue box.
And in the ‘Excel Option’ dialogue box, click on the ‘Choose command from’ drop-down and select ‘All Command’ here in place of ‘Popular Commands’.
When all commands appear in the below area, then scroll down to search for the “Visible Cell Only’ command option.
Once you find the “Visible Cells Option” command then add this command to the ‘Customize Quick Access Toolbar’ column.
See the image below:-
Once the ‘Select Visible Cells’ option is added to the QAT (Customize Quick Access Toolbar) column, click on the “Ok” button at the bottom.
And see that the “Select Visible Cells” command button is now located in the QAT area.
Once this feature is added in the QAT area, then select the visible cells in the dataset, and click on this icon.
By clicking on this, the only selected visible cells are locked now.
Then press the (Ctrl + C) command from the keyboard shortcut to copy it.
Go to the destination area where you want to paste it.
Press the (Ctrl + V) command from the keyboard shortcut to paste it.
See that all the only visible cells are posted here without any hidden rows or columns.
If you want to learn more about this, please visit Microsoft Office Support.
I hope you find this tutorial helpful.
Please feel free to put your comments and suggestion in the comment box below.
Thanks
Narendra
Related Post
How to Highlight Every Other Row or Column in Excel
How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel
5 Ways to Delete Blank Rows in Excel (With VBA Macro)
How to Change the Background Colors in Excel VBA