Turn Off Filters in Excel Table

Turn Off Filters in Excel Table

Now that the 30 Excel Functions in 30 Days challenge has ended, it’s time to look at a few other features. On the Contextures page on Facebook, Lee suggested AutoFilters as a topic for some February posts. Thanks Lee! Here’s how to turn off filters in Excel table headings.

Filters in Excel Tables

When you create an Excel Table in Excel 2007 or Excel 2010, autofilter arrows are automatically added to the heading cells. Most of the time, this is a welcome feature, and the arrows make it easy to sort and filter the columns.

ExcelTableFilters01

Video: Create an Excel Named Table

When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps

Multiple Excel Tables

You can even create more than one Excel table on a worksheet, and have a separate filter on each table. I use this feature occasionally, when creating small tables on a summary sheet.

Just remember to stack the tables, instead of placing them side by side. That way, the filtered rows in one table won’t affect the others.

Note: If you don’t use the Excel Table feature, you’re limited to a single AutoFilter (or Advanced Filter) per worksheet.

ExcelTableFilters04

Turn Off the Automatic Filters

Sometimes, though, you don’t want to have those filter arrows available. For example, you might set up tables because they’re a quick and easy way to create a dynamic range. Or, maybe you like the pretty colours and automatic total row.

But, you don’t want to encourage users to filter the data — all the rows should always be visible. And you know those pesky users will click the arrows, if they’re temptingly available.

To turn off the filters:

  1. Select a cell in the Excel Table
  2. On the Ribbon’s Data tab, click the Filter command, to turn off the filter.

ExcelTableFilters02

Keep the Excel Table Features

After you turn off the filters, the Excel Table is still recognized as a table, so you can continue to use all its other features. Even though the filter arrows are gone, you can add a Table Style, and show Banded Rows.

ExcelTableFilters03

Or, create formulas that use structured referencing, instead of cell references.

ExcelTableFilters05
__________

8 thoughts on “Turn Off Filters in Excel Table”

  1. The main reason I haven’t jumped on the Tables bandwagon is because of the annoying filters that obscure the column headings. Thanks for this tip. Now I can enjoy the all the benefits of using Tables without the constant annoyance staring me in the face.

  2. Hi there,
    Is there a possibility to disable the 3 sort commands as can be seen in the first picture of this topic?
    – Sort A to Z
    – Sort Z to A
    – Sort by Color
    If so, how can i do this?
    any help verry apreciated.
    thanks in advance,
    Ludo

  3. Hi sir, In the last day on words my Ms-Excel-2016, was not working properly. My work sheet have 4 sub pages, but to day i open my work sheet its apper only one sheet.there is no the remaing 3 sub pages….. So could you plz expain me wht was happen ..? and how i re open my page liske the privious by 4 sub pages.

  4. If I want to turn off the Automatic feature of the filter, how do I do that? I create hundreds of tables and I screenshot them through Excel; however, the sort arrows do obscure the table headings like mentioned in other comments, and so I have to manually turn off filters for hundreds of tables. Is there a way to disable the feature? Thank you

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.