I run into a repeated problem when using Excel, which happens in two steps.

  1. I select all my data columns, and click the "Filter" button on the Data tab to enable filtering. Then I use the arrows at the top of the columns to sort my data. Since all columns were selected for the filter, all columns sort together and the relationship between the rows is preserved.

  2. I add another column of data to the right of my pre-existing data. I forget to set filtering for this column. Now, when I sort the old data, the new column isn't filtered. The other columns rearrange independently of this column and the relationship between the rows is broken. Often I don't notice this until the original arrangement of data is irretrievable.

Is this expected behaviour?

I have noticed that there is a warning to expand the selection area to adjacent columns when using Data --> Sort. Is there a similar warning for this case that I may have disabled? If so, is there a way to re-enable it?

Alternatively, is there a convenient column filtering workflow that would avoid this problem?

  • You don't need to go to the Data tab to turn on filters or sort; you can do both from the Home tab. It's on the far right (3rd from the right). As to your question, I do not see this behavior in my Excel 365 version. – BillDOe May 24 '19 at 20:45

Is there a similar warning for this case that I may have disabled?

No, there is no warning for this situation.

You can avoid that issue in two ways :

  1. When you insert autofilter select the whole row and apply to that
  2. instead of autofilter insert a table (insert tab), if you add any adjacent data your table will automatically expand
| improve this answer | |
  • 1
    3. Insert your new field using insert column within the filtered area instead of adding data outside the filtered area – Alex M May 26 '19 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.