How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)

In this tutorial, we will cover how to copy and paste visible cells only in excel. When we copy a range of cells that comprises hidden rows or columns, Microsoft Excel includes all of the selected rows or columns by default. It makes no difference whether they are visible or hidden. When you paste the selected range, both visible and hidden cells will be pasted. But we do not expect this to happen all time. That’s why we will explain the ways of how to copy and paste visible cells only.

Download Practice Workbook

You can download the practice workbook from here.

How to Copy and Paste Visible Cells Only in Excel (3 Easy Ways)

In this article, we will demonstrate 3 easy ways of how to copy and paste visible cells only. For your convenience to compare the methods we will use the same dataset for all the 3 methods.

1. Use ‘Go To Special’ Option to Copy and Paste Only Visible Cells

In the first method, we will show you how to copy and paste visible cells only by using the “Go To Special” option. Now, look at the dataset in the following image. We will explain this method with this dataset. The dataset contains both visible and hidden cells. If we notice the dataset, we will see that row numbers 6,7, & 8 are hidden.

Now, let’s see what will happen if we copy and paste this data range with the conventional method:

• Firstly, select the data range (B4:E9).
• Secondly, go to the Home tab and click on the Copy You can also do this action by pressing Ctrl + C keys.

• Thirdly, select cell B12. Then go to the Home tab and click on the Paste option.

• If we notice we will see that the above command pastes both the visible and hidden cells from the selected data range.

So, to copy and paste only visible cells we have to apply some special commands. Let’s see the steps regarding this process.

STEPS:

• First, select the data range (B4:E9).

• Next, go to the “Find & Select” option from the Home tab.
• Then from the drop-down menu, select the option “Go To Special”.

• Now, a new dialogue box will open. Check the option “Visible cells only” from that box and click on OK.

• This action selects only the visible cells from the selected data range (B4:E9).

• After that, to copy the selected data range, go to the Home tab and click on the Copy. You can also use the command Ctrl + C to copy the data range.

• Then, select cell B12. Go to the Home tab and click on the Paste option.

• Finally, the above command pastes only the visible cells of the data range (B4:B9) in range (B12:E14).

Read More: How to Copy Visible Cells Only in Excel (4 Fast Ways)

2. Copy and Paste Visible Cells Only with Quick Access Toolbar

The Microsoft Office “Quick Access Toolbar” is displayed in the upper left corner of the application. It comprises the most frequently used actions. In this method, we will use the “Quick Access Toolbar” to copy and paste visible cells only. For your convenience, we will copy and paste the same data range from the same dataset that we did in the previous example.

Let’s see the steps to copy and paste visible cells only with the “Quick Access Toolbar”.

STEPS:

• In the beginning, click on the “Customize Quick Access Toolbar” Select the option “More Commands” from the drop-down menu.

• This action will open a new dialogue box.
• Next, from the dialogue box select the option “Quick Access Toolbar”.
• Then, click on the dropdown icon of the “Choose commands from”.
• From the drop-down menu select the option “All Commands”.
• In short: Quick Access Toolbar > Choose commands from > All Commands

• One more dialogue box will appear named “Customize the Quick Access Toolbar”.
• From that box scroll down the command and choose the command “Select Visible Cells“.
• Then click on add to insert the option in “Quick Access Toolbar” and click on OK.

• So, we can see the “Select Visible Cells” icon in the “Quick Access Toolbar”.

• Next, select the data range (B4:E9) and click on the “Select Visible Cells” icon in the “Quick Access Toolbar”.

• The above action selects only the visible cells from the data range (B4:E9).

• Then, click on the Copy from the Home tab to copy the selected data range.

• After that, select cell B12 and click on the Paste option from the Home tab.

• Finally, the above command pastes only the visible cells from the copied data range (B4:E9) in the data range (B12:E14).

Read More: [Fixed!] Paste Visible Cells Only Not Working (4 Possible Solutions)

3. Excel Keyboard Shortcut to Copy and Paste Visible Cells

In this example, we will know about how to copy and paste visible cells only using the keyboard shortcut in excel. This is the easiest and fastest way to do any task in excel. We will copy and paste only the visible cells from the following dataset with the use of a keyboard shortcut. Basically, we will use a keyboard shortcut to select the visible cells from the data range. After that, we will copy and paste the selected visible cells in a conventional way. The command that we will use to select only the visible cells from a data range is:

• For Windows: Alt + ;
• For Mac: Cmd + Shift + Z

So, let’s set the steps to use a keyboard shortcut to copy and paste visible cells only.

STEPS:

• Firstly, select the data range (B4:B9).

• Secondly, press Alt + ; for windows and Cmd + Shift + Z for Mac.
• The above commands select only the visible cells from the selected data range.

• Then, to copy the selected data range click on the Copy from the Home tab.

• Thirdly, select cell B12.
• Then go to the Home tab and select the Paste option from the ribbon.

• Lastly, the above action pastes only the visible cells from the copied data range (B9:E9) in the data range (B12:E14).

Read More: How to Copy Visible Cells Only without Header Using VBA

Conclusion

In the end, this post will show you how to copy and paste visible cells only in an excel worksheet. Download the practice workbook that comes with this article to put your skills to the test. If you have any questions, please leave a comment in the box below. We’ll try our best to respond as soon as possible. Keep an eye out for more intriguing Microsoft Excel solutions in the future.

Related Articles

• Sum Visible Cells with Criteria in Excel (5 Easy Methods)
• How to Average Only Visible Cells in Excel (3 Quick Methods)
• Excel VBA: Select Visible Cells After Autofilter (4 Examples)
• Excel VBA to Select First Visible Cell in Filtered Range
• How to Count Only Visible Cells in Excel (5 Tricks)
• Sum Only Visible Cells in Excel (4 Quick Ways)