Copying Collapsed Rows/Columns in Excel Excludes Hidden Data

Excel doesn’t copy collapsed columns or rows, which can be excruciatingly painful to figure out.

When copying Excel data into a text editor (or other program), if you have a collapsed row or column, the data will not paste properly.

So, imagine you have 51 rows of data. That’s what Excel tells you anyways like shown in the screenshot on the right here.

Now, you paste that into your text editor, but only get 50 rows! WTF? Well, go back and check for a collapsed column like shown in the next screenshot on the left. Notice how row 17 is gone, and how the first column skips from 14 to 16 in the next cell. Copying those three cells, i.e. 16, 17 and 18 gives us:

14

16

And that’s pasted directly into this page. Where’s 15? Hidden by Excel.
It’s not “bad” behavior, but it is certainly annoying if you are expecting to have all your data pasted and not anticipating a hidden row.

For those of us that normalize our data and use Excel as a spreadsheet and not a document authoring tool, it’s painful. I suppose that’s what you would expect if you’re used to authoring documents in Excel though.