Back To Basics: Data Filters in Excel 2003 and Excel 2007

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Data Filters are a very powerful way of analysing tables of data in Excel. Put simply they are a way of reducing the table down to just the items you want to see based on the selections you choose.

For example a simple table like this one, may have several hundred or thousand rows of data. However there may be only a few lines you are interested in. For example you may be only interested in people under the age of 18 or sales amounts that are negative (indicating a refund) or only see sales of red Ferraris.

Turning on the Filters

In Office 2003 Turn on the filters by selecting any cell in your table (contiguous section of data) and selecting Data, Filter, Auto Filter from the menu. (Keystroke ALT D F F)

image

The arrows will show up as in the next picture.

image

In Office 2007 the filters are quite different. They are turned on differently and behave differently also.

Turn them on by selecting  any cell in the data table and clicking Data, then Filter. (Keystroke CTRL+SHIFT+L)

image

Turning off the filters in both versions is simply repeating the same clicks or keystrokes.

Filtering the data

Once you have the filters turned on you are able to reduce the visible cells by clicking the arrow for any column and then selecting from the available items.

In 2003 this looks like this:

image

Selecting Navy reduces the data set from, in this case, 79 items to 7. Note the blue row numbers. These indicate the filtering that has hidden the rows between. Some formulas only work with visible cells. And these hidden cells will now be excluded from those formulas.

image

SUBTOTAL for example is a formula that will sum only visible cells. Select the cell two cells below the bottom of the table under a value column (Sales) and then double click the SIGMA Sum icon on the toolbar.

image

Note how the resulting formula displays only the value of the visible cells, though it is evaluating all cells in the column of the table.

image

The same effect can be achieved in Office 2007. However the selection process is somewhat different.

image

image When you select the arrow you are presented with a number of different options. The sort function is made available from this menu. The custom number and text filters are contextual and made available through a right arrow and secondary menu.

The filters based on the actual data in the column are now presented as check boxes with the default being they are all selected. You can turn off all the items you don’t want to see, or in this case you can turn off Select All and then turn on Navy.

Now Click OK and the resulting data set will display the same items – those sales where the car colour was Navy.

Unfortunately in Office 2007 double clicking the sigma function button won’t give you subtotals but just the normal SUM formula. However entering the SUBTOTAL formula in will get the same mathematical result as Excel 2003.

image

There is a lot more that can be done with filters. Next time we will look at using the custom text and number filters.

See my previous posts on this subject and similar subjects.

Office 2007 Excel Data Auto Filter

Using the Advanced Filter in Excel

Turning Excel Auto Filters on and off in VBA

Creating Subtotals in Excel

Using the Advanced Filter Function in Excel to locate unique records

Saving Autofilter Criteria

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
serdarb's picture

very nice post...

very nice post...

jethro's picture

thanks serdarb

thanks serdarb

Jenna's picture

That is a very good tip

That is a very good tip particularly to those new to the blogosphere.

Short but very precise information… Thanks for sharing this one.

A must read article!

Anonymous's picture

Thanks for the post... so

Thanks for the post... so another feature MS decided worked too well.

Anonymous's picture

i wants to counts number of

i wants to counts number of time one name is coming
i need in seq number

zakirpatait@yahoo.com

jethro's picture

use the count function for

use the count function for that.
=countif (range, criteria)
more here http://www.spyjournal.biz/taxonomy/term/273

Chris Zit's picture

Thanks, this is a very

Thanks, this is a very informative post! Excel has got a lot of secrets in it.

Goodluck Gunda's picture

nice work

nice work