Results 1 to 2 of 2

Thread: How to Use Excel filtering to find data quickly

  1. #1
    Join Date
    Jul 2004
    Posts
    153

    How to Use Excel filtering to find data quickly

    For most people, Microsoft Office Excel spreadsheets is the tool for compiling information about customers, products, sales revenues and other data. But when the volume of data recorded in a single sheet performs a multitude of columns or rows, it can be difficult to sort them efficiently. Thus, to identify your top ten customers of a specific region in the past six months, you could spend a long time looking in the data entries. Fortunately, Excel provides a function called Auto Filter that is easy to use to display only what you want to see and hide the rest. Filtering does not alter in any way your data. Once you remove the filter, all your data reappears exactly as before. Here's how to use the AutoFilter in Excel.

    1. Make sure the data in each column are similar

    In your worksheet, the top row of each column should contain a header describing the contents of the column, for example, product no. or customer name.


    The data in each column should be the same type. For example, it should not be any text cells in a column of numbers or numbers in a column of dates.

    2. Activate the AutoFilter

    Now click inside any cell to give, then select AutoFilter as follows:
    • In Office Excel 2007, click the Data tab and then in the Sort & Filter, click Filter.

    The AutoFilter arrows now appear to the right of each column header.


    Note: If you select an entire column instead of a single cell before activating the automatic filter, only the selected column has an AutoFilter arrow.

    3. Begin to filter data

    Suppose your worksheet contains sales data for customers. Each client entry contains information on the customer's location, products purchased, transaction dates and revenues earned on each sale. Perhaps you want to see sales for the western region customers only? Excel can make your job easier.

    To view only the sales of the western region, click the AutoFilter arrow in the header of the column region. When you click an AutoFilter arrow, a list appears. This list contains all the entries in the column, sorted alphabetically or numerically, so you can quickly find the desired item. In this case, you scroll to east, then click on this entry.


    When you click on West, Excel hides all the rows of the worksheet except those that contain this word in this column.


  2. #2
    Join Date
    Jul 2004
    Posts
    153

    Re: How to Use Excel filtering to find data quickly

    4. Apply additional filters

    If you wish to further clarify the purpose of your analysis, you can make another filter on another column, then yet another and so on. You can click on the arrow in any header column to apply a filter.

    After filtering by region, you can, for example, click the arrow under column Product Number and filter it so customers see only the Western Region who bought the product No. 12-100.


    You can filter columns in any order you choose. The filters are applied progressively, in the order in which you select. Each filter limits the scope of data that you can apply to the next filter.

    5. Using advanced filtering techniques

    Excel also allows you to perform filtering operations in more complex methods. Two types are especially useful which are called Top 10 filter and custom filtering.

    Identify the top 10 (or last) in a column
    You can use the Top 10 filter on columns of numbers or dates. This filter identifies the 10 values higher or lower in a column of amounts and dates, for example. But you're not limited to only 10 items. You can specify the number of entries you wish to display, only one as many to 500. You can use the Top 10 filter to locate more expensive or less expensive items to identify employees whose hire date is the recent, or to see the highest scores or the lowest scores obtained by students.

    To apply the Top 10 filter on a column in Excel 2007, click a cell in the column, then click the AutoFilter arrow in this column. Then click Digital filters, Then select Top 10.

    The dialog Top 10 appears. In this dialog box, select Top or Low. Then select a number. Finally, select elements or Percentage.

    You can filter columns in any order you choose. The filters are applied progressively, in the order in which you select. Each filter limits the scope of data that you can apply to the next filter.

    Using custom filters
    When you filter entries by selecting an item from the AutoFilter dropdown list, you hide everything that does not match your choice. To perform filtering on multiple items in a column, you can create custom filters.
    To create a custom filter, follow these steps:
    Click Digital filters, Then Custom Filter.

    The Filter dialog box automatically displays. You can now enter two filtering criteria for the column to give. For example, you can view all customers who have purchased goods bearing the numbers 12-100 and 12-500.

    Note: Make sure to select the OR button otherwise you will get no results.


    6. Disable Filtering

    How to remove filters depends on the number of filters applied and the number of columns specified.
    • To remove a filter to a column, click the AutoFilter arrow, and then click All. This command displays the rows hidden by that filter.
    • To remove all filters simultaneously, point to Filter on the Data menu, then click Show All. This command displays all the rows hidden by all filters in the worksheet but leave AutoFilter turned on.
    • To deactivate, point to Filter on the Data menu, then click AutoFilter.
    • To remove the filters from your spreadsheet, simply deselect the Filter in Excel ribbon. All data from the worksheet are displayed again.

Similar Threads

  1. Automatic filtering in Excel
    By RenNoClue in forum Software Development
    Replies: 4
    Last Post: 27-01-2012, 08:08 PM
  2. How to find percentage of columns data in Excel
    By Kungfu Pandey in forum Windows Software
    Replies: 2
    Last Post: 07-01-2012, 06:20 PM
  3. Where can I find excel style to get data input cells
    By Krishnaraju in forum Windows Software
    Replies: 5
    Last Post: 06-07-2011, 12:05 AM
  4. packet filtering based on data, not ip
    By Harigopal in forum Networking & Security
    Replies: 3
    Last Post: 04-11-2010, 06:19 AM
  5. Excel autofilter stops filtering
    By roumieh2000 in forum Windows Software
    Replies: 3
    Last Post: 15-10-2009, 03:31 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,710,828,055.66049 seconds with 16 queries