Filters in Excel FAQ

Microsoft Excel is the world's most popular spreadsheet application and is used in countless business and homes around the world. Excel is a very useful tool for maintaining financial records, displaying management information, managing business accounts and much more. One very useful feature in Excel is the capability to filter data. This can make it much quicker and easier to view and analyze a lot of data held in a spreadsheet. Here are some important facts about using filters in Excel.

Setting up a spreadsheet for filters

You need to turn filters on in a spreadsheet in order to benefit from the different filtering functions. Generally speaking, filters are applied to all the columns in your spreadsheet and will appear in the first row of data. As such, you need to ensure that the first row contains the headings for each column. If the first row contains data, then this may make it harder to use the filter. Note also that filters will stop where the data stops, so if you want to apply filters to an entire column, you need to ensure that there is a value in every cell in that column.

Turning on Autofilter

The main filter function in Excel is called Autofilter. To turn this feature on, simply complete the following steps.

  • Open the workbook and click on the tab for the spreadsheet that you want to apply filters to.
  • Highlight the row that you want the filter to start from.
  • Click Data, then Filter.
  • Click Autofilter. A small drop-down box will appear in the first row of your spreadsheet.

Using Autofilter

Autofilter will display a drop-down list of all the values in the column selected. Each unique value will appear once. You can select a value, and Excel will show only the rows containing the selected value. You can use Autofilter to greatly refine the data held in a spreadsheet. Each column can be filtered separately, allowing you to focus on a very low level of detail.

For example, assume you have a spreadsheet that shows the personal data for all 10,000 customers in your database. It could be very difficult to find certain customers. You could use a filter, however, to filter the column 'Gender' so that you only view female customers. You could then filter the 'State' column, to show those female customers that live in California. As you apply each filter, you can see how the data could reduce rapidly. When a filter has been applied, you can easily turn it off by clicking the filter box and setting the filter back to 'All.'

Using Advanced Filter

Advanced Filter is a very useful facility that allows you to refine the detail in more detailed ways. You may want to use this when you need to apply some very specific, complex filters on a large sheet of data. Click Data, Filter than Advanced Filter. You can choose to have the filter applied on the sheet or for the filtered data to display elsewhere (useful if you want to create a chart from the data). You can filter certain rows, rather than the whole data range, as per Autofilter. You can apply different criteria, too, so you could filter to choose residents of both California and Iowa, and you can also filter to show only unique records.

© 2014 Life123, Inc. All rights reserved. An IAC Company