Why do you turn off Auto Filter and then turn it on again??


I have seen many people turning OFF and then ON Auto Filter in order just to SHOW ALL values (in Excel 2003’s terms) or CLEAR all filters (in Excel 2007 or above’s terms).  Frankly, I think many people do this just for convenience (or laziness).  Some people do this because they don’t know CLEAR (in Excel 2007 or above) = SHOW ALL (in Excel 2003)… well, I was not aware of it when I first switched to Excel 2007 😛

If you are doing it and think that there should not be any problem, you probably are not aware of the risk of it…

In the screenshot below, you see that there are 27 items (A-Z and “Blank”) in Field A.

Image(Note: A specific range of table had been selected before the Auto Filter is ON)

If you turn off the Auto Filter and then turn it on again simply by clicking the Auto Filter icon:

Image (Note: you have to select a cell in the table where you want to apply Auto Filter to.)

Many items will be lost in the Auto Filter that you have just “re-created”.


oooops…. why?

Without specifying the range of cells you want for Auto Filter, Excel assumes that you wan to apply to a continuous range around the active cell, which is A1:D14 in this example.  As a result, Auto Filter is NOT applied to all data on and below row 15.


For illustration purpose, the table is so small and simple that you can  spot the mistake easily.  If you are working with a large table that is prepared from someone else, you may never know how many blank rows had been included in the table (coz not every one is as good in using Excel as you do).  So…

NEVER put yourself at risk for convenience/laziness.

Use the “Show ALL” in Excel 2003 or “CLEAR” in Excel 2007 or above.

In Excel 2003Image

In Excel 2007 or aboveImage


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

1 Response to Why do you turn off Auto Filter and then turn it on again??

  1. narayank991 says:


    Thank you.



Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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