Mrinmoy Roy
When copying and pasting data in Microsoft Excel, you may want to skip hidden cells. This can be helpful if you’re trying to avoid copying data that are not visible to you. There are several ways to skip hidden cells when copying and pasting in Excel. In this article, I will show you 2 effective methods to skip hidden cells when pasting in Microsoft Excel. So, without any further discussion, let’s get straight into the topic.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Why Are Hidden Cells Being Pasted?
When you copy and paste data in Microsoft Excel, any hidden rows and columns within the selection are also copied. This behavior is by design and is the expected result when copying and pasting data in Excel.
Look at the following screenshot. Some of the rows are hidden while others are visible.
To show you the default phenomena of copying and pasting, I’ve copied and pasted the above dataset underneath it. Now in the following picture, you can see the pasted data. Here, one thing that you will notice is that all the hidden rows are also pasted along with the visible rows.
So, if you want to avoid hidden rows, the default system of copying and pasting will dishearten you. So get out of this issue, I will show you 2 ways to bypass this problem. Keep reading.
2 Methods to Skip Hidden Cells When Pasting in Excel
1. Using Go to Special to Skip Hidden Cells When Pasting
In the first approach, I will use the Go to Special dialog box to select only the visible rows ignoring all the hidden rows. Thus, pasting data will ignore all the invisible rows.
To use the Go to Special dialog box,
❶ First, select the entire dataset that includes some hidden rows in it.
❷ After that, press the CTRL + G buttons together.
This will open the Go To dialog box in no time.
❸ To open the Go To Special dialog box, click on the Special button at the bottom of the Go To dialog box.
Now the Go To Special dialog box will appear.
❹ Now select the Visible Cells Only option in the Go To Special dialog box.
This option will highlight all the visible rows ignoring all the hidden rows.
❺ Anyways, hit the OK button to apply the changes.
Now you can see all the visible rows are highlighted. To copy them all,
❻ Press the CTRL + C keys together.
❼ Now select any blank cell as a destination. After that, press CTRL + V to paste the data.
This time you will notice that only the visible cells are pasted. No hidden rows were pasted. So, this is how you can use the Go To Special dialog box to skip hidden cells when pasting in Microsoft Excel.
Read More: How to Skip Cells When Dragging in Excel (4 Easy Methods)
Similar Readings
- How to Skip Columns in Excel Formula (3 Easy Ways)
- Skip to Next Cell If a Cell Is Blank in Excel (5 Easy Ways)
- How to Skip Every Other Column Using Excel Formula (3 Methods)
- How to Skip Lines in Excel (4 Suitable Ways)
2. Skip Hidden Cells Using Keyboard Shortcut When Pasting
To skip hidden cells while pasting in Excel, you do not necessarily always need to use the Go To Special dialog box. If the previous process seems time-consuming to you, there’s a more efficient method for you to skip all the hidden rows. Here, I will copy only the visible rows using a keyboard shortcut trick.
Now let me show you how to do that.
❶ First of all, select the entire data table.
❷ Now press the ALT + ; keys together.
These keys will select all the visible rows in your spreadsheet ignoring all the hidden cells.
❸ Now select a destination cell to paste your copied data.
❹ After that, press the CTRL + V buttons together to paste the dataset into the destination cell.
Now, look at the following picture, here only the visible cells of the source data are pasted. So, this is how you can skip all the hidden cells when pasting in Excel.
Read More: Excel Formula to Skip Rows Based on Value (7 Examples)
Practice Section
You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the topics discussed in this article.
Conclusion
To summarize, I have discussed 2 ways to skip hidden cells when pasting in Excel. Please don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
