Excel – “Insert Copied Cells” option is no longer available

microsoft excelworksheet-function

I have two spreadsheets I use regularly and have been working out of for over a year; recently one of them stopped giving me the option to "insert copied cells" while the sheet is filtered. If I unfilter then the option is available. My other spreadsheet would still allow me do this normally even if the sheet was filtered. Today, while trying to fix the one that didn't work, I was looking at my other sheet for comparison and then it quit allowing me to do this. So now neither of my sheets will allow me to "insert copied cells" while the sheet has a filter on. How do I get that option back? No columns are hidden and I have not messed with any settings. I had literally done this function about 10 minutes previous to it no longer being available.

Best Answer

This is a limitation of Excel in the handling of filtered data, which has to do with the copied data being consecutive or not. This is the real reason why it would sometimes work and sometimes not, since it depends upon source rows not having any "holes" because of the filter.

This means that:

  • If the copied rows are outside the filtered range, then you can insert it into a filtered range.
  • If the copied rows are inside the filtered range, it will depend on which rows were copied:
    • If the rows copied were consecutive, then it's possible.
    • If the copied rows have any rows skipped due to the filter, then not possible.

In later versions of Excel you may get around it only by disabling the filters for the copy, since you cannot otherwise copy from visible-only cells to visible-only cells.