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.

(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:

 (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 2003

In Excel 2007 or above