When you copy and paste from set of cells that include hidden data, like hidden rows or columns, the hidden data will be included when you paste. This can be a problem–after all, you hid that data for a reason.
To help with this, Excel has a feature that allows you to only select visible cells. The catch to this is that it will select ALL visible cells in the worksheet, and you will not be able to paste all the visible cells in the same worksheet. Instead, you can paste into a new or existing worksheet. If necessary, you can then copy and paste from this sheet without having to select only visible cells.
Select Visible Cells
Follow the instructions below to select ALL visible cells (even the empty ones in row 100,000).
- On the Home tab of the ribbon, click on the Find and Select button.
- From the menu that pops down, select Go to Special …
- Click the button next to Visible cells only.
- Click OK.
- All visible cells will be selected. If you want to copy them, click the Copy button on the Home tab of the ribbon or press Ctrl+C on your keyboard.
To Paste Visible Cells
If you copy all visible cells and try to paste cells in the same worksheet you copied them from, you’ll get an error message. Try this, instead:
- Go to a new or existing worksheet by clicking on another worksheet tab or clicking on the New Worksheet tab (the last tab at the far right with the yellow gleam).
- Select cell A1 (or another cell that will give you enough room to paste your data).
- Click the Paste button on the Home tab of the ribbon, or press Ctrl+V on your keyboard.