You have a table of data and you hide some rows and columns and you want to copy only the visible cells into another sheet? I’m going to show you some techniques that you can use to do that. Let’s see how our original table looks like:
Now, let’s hide some rows and columns so that our table looks like this:
If you select the entire table (cells A1:E12) and just copy and paste (Ctrl+C and Ctrl+V) it into another sheet, it would copy all rows and columns and would look like the original table. That’s not what we want to do, we just want to make a copy and show only the 7 rows and 4 columns that we see on the image above. For that we need to select the visible cells only so that we can copy only those. Let’s select again cells A1:E12 but this time we’re going to select first only the visible cells, before copying. We can do this by just using the shortcut Alt+; (semicolon) or you can go to the Home menu bar, on the Editing group, and click on the Find & Select button and choose Go To Special option.
Instead of going to the menu bar, you can just press F5 or Ctrl+G to open the “Go To” dialog box and then just press the Special button on the bottom. On the Go To Special dialog box, select the “Visible cells only” option:
This will select only the visible cells, as you can see on this image:
Now you can just Ctrl+C (Copy) and Ctrl+V (Paste) it into another location. This will only copy the visible cells, like you wanted.
If you are working with Filters, you don’t need to use this techniques because, when you copy filtered data, it only copies the visible cells already.
