When you have a filter in place in Excel you typically only affect the visible cells when you edit multiple cells. There is a case when you are affecting all cells not just the visible ones.In general, you can only have one filter in place on a sheet. This changes when you have multiple formatted tables on a sheet, since formatted table have filters built in. Having multiple filters on a sheet can cause issues.

Take the example below.

We have a formatted table on the left and a list on the right. Both have filter icons.

If you filter for NSW in the formatted table and then select the range C3:C8 and press Delete, you only delete the entries in the visible cells. The hidden cells are unaffected. See image below.

This is a useful technique when working with individual tables.

Let’s see what happens when we undo all that and remove the filter in the formatted table and apply a filter to the list on the right.

We select the range C3:C11 in the formatted table and press Delete – see image below.

We are working with visible cells in the filtered list on the right. However, in the formatted table on the left, without the filter in place, we are working with all the selected cells, both visible and hidden.

Warning

When you are working with filters, the filter only applies to the list or table where the filter is in place. Although the other tables in the same sheet are affected by the filter, they are not included in the filter’s visible only editing effects.

Be careful when using filters on multiple tables in the same sheet. It is common to include multiple formatted tables in the same sheet. In terms of filtering make sure you remove all filters from all the formatted tables if you are making changes via a filter to one of the tables.

You know if a filter is in place on a sheet because the row numbers turn blue instead of black – see image below.

The keyboard shortcut to remove filters is Alt A C pressed in sequence. You must have ac ell selected in the table to use this. There also needs to be a filter in place to remove it.

The icon to clear filters is.

This is in the Data ribbon tab. It will be greyed out if no filters are in the place in the table you are in.

To remove the filter icons you can use Ctrl + Shift + L. This is a toggle, so it turns on filters if you haven’t got them on as well. Again, you need to have a cell selected in the table to use this.