PASTE VISIBLE CELLS ONLY
Copying cells and pasting them in to visible cells can be a problem. This is a problem when you drag the cell contents down as well as copying and pasting cell values only (i.e. not affecting the formatting).
When rows are hidden in a spreadsheet, copying and pasting in to the visible cells on a worksheet will copy the data in to all the hidden rows too. Additionally, it can also cause an issue when you have filtered data or have a table of data.
Usually, a straight copy and paste (Ctrl +C and the Ctrl +V) will only paste in to visible cells, however to ensure that you do not copy data, particularly if you do not wish to copy the formatting, in to all cells the safest way to perform this action is to:
Highlight and click copy as values the cells to be copied
Highlight the cells where the copied data is to be pasted
Then press F5, then Special and Visible cells only and OK
In the screenshot below, data in cells B2 and B3 has been copied and is going to be pasted as values in to all instances for Alpha Ltd, which has been filtered in Column A.
The result is as follows:
However if F5 and 'paste visible cells' had not been selected the following data would have been pasted.
Therefore if you are ever in doubt about copying data in to visible cells only it will always be safer to use F5 / Special / Visible cells only
It may also save you hours of time having to recreate your data.
