I'm seeing the following inconsistent behavior in Excel 2016, when Copying/Cutting/Deleting data across filtered data:
- Copying Cells: Data gets "copied" from the visible cells only, and not from the filtered cells.
- Cutting Cells: Data gets "cut" from both - the visible, as well as the filtered cells.
- Pasting Cells: Data gets pasted on to both - the visible, as well as the filtered cells.
- Formatting/Deleting/Dragging Down/Other: Only the visible cells get affected, and not the filtered cells.
I see no good reason for this inconsistent behavior - is this a bug? Or is there some reason for this?
And more importantly, is there any way to get Excel to ignore hidden rows when Cutting and Pasting data, so that it is more consistent with the rest of the operations?
To try this out, please create the following table in Excel:
Next, filter out the rows with the Category = "Hide":
You should see something like:
Now, do the following:
- Select the visible cells in the Copy-From column (AAAA, BBBB, EEEE, FFFF), and Copy them (
- Go to the first empty cell under Copy-To, and Paste (
- Select the visible cells in the Cut-From column (GGGG, HHHH, KKKK, LLLL), and Cut them (
- Go to the first empty cell under Cut-To, and Paste (
- Select the visible cells in the Format column (MMMM, NNNN, QQQQ, RRRR), and make them Bold (
- Select the visible cells in the Delete column (SSSS, TTTT, WWWW, XXXX), and Delete them (
- Select the first cell in the Drag column (January), and drag it down till the end of the column (till CCCC).
You should now see:
Now, clear the filter from the Category column, and you should see:
Based on the above, we can see several inconsistencies in how the filtered cells are handled:
- Copy: ignores filtered cells
- Cut: operates on filtered cells
- Paste: operates on filtered cells
- Delete: ignores filtered cells
- Formatting: ignores filtered cells
- Dragging data down: ignores filtered cells
This behavior for filtered cells is different from how hidden cells are handled (to hide a cell, right click on the column or row header, and choose
Hide - this will hide the entire row or column). Hidden rows are included in all operations - that is, we can Copy, Cut, Paste, Delete, Format etc data if it is Hidden. The operations seem to behave inconsistently only for "Filtered" data.
Also, interestingly, when the data is hidden and not filtered, the Drag operation behaves differently as well - instead of repeating "January" everywhere, it behaves more intelligently, and shows February, March etc.