Filtered Rows and Hidden Rows
In Excel, Rows that we hide manually and those Rows get hidden while applying Filters, work in different ways.
When we copy-paste a value into the filtered cells they won’t be copied into the hidden cells.
At the same time, if we hide some rows manually and copy-paste a value into that dataset, the value will get pasted into the hidden rows too.
See the following example where I have manually hid the Rows 6, 7, 9 & 10.
Now when I copy the value in the cell J2 and paste it into the visible cells from C5:C11, the value ‘Toblerone’ goes into the hidden cells too.
Paste into Visible cells only
To paste a value into visible cells only, we should select the visible cells before applying the Paste option.
To paste the value in the cell J2 (Toblerone) into the visible cells from C5 to C11. . .
Copy the value in the cell J2 > Select the cells from C5 to C11 > Press Alt + ;
Alt+ ; (Semi-colon) is the shortcut to select Visible Cells in Excel
Now that visible cells in the range C5 to C11 are selected, press Ctrl + V.
When you unhide the rows, you can see that the content of the cell J2 is copied, only into the cells that were visible earlier.
You can also use the Go To Special dialog for selecting visible cells.
In the Home tab > Find & Select > Go To Special… > Visible cells only
