Warning when Excel is not sorting adjacent columns

Question

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?

Answer 1

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