You are here: Home / Archives for Autofilter

Hide rows based on cell value in Excel 2007

December 18, 2009 By admin Leave a Comment

To hide rows based on cell value in Excel 2007 follow these steps:

  1. Prepare your data including a header row.
  2. In the Home tab, Editing group, click Sort and Filter then select Filter from the menu.
  3. Your data will now look like this:

XL7HideRows1

In this example we want to hide rows where the sales are greater than or equal to 2000. I.e. we will only show rows where the sales are less than 2000.

  1. Click on the filter arrow next to Sales.
  2. Point to Number Filters and select Less Than.

    3. XL7HideRows2

  3. The Custom AutoFilter window will open.

    4. XL7HideRows3

  4. Enter 2000 in the Combo box to the right and click OK.
  5. Now all rows with values greater than or equal to 2000 will be hidden. Here is the final result:

XL7HideRows4

Note:

If your data is text then you will see Text Filters instead of Number Filters in the menu.

Filed Under: Data, Excel 2007 Tagged With: Autofilter, Data, Excel 2007, Excel help

How to show the top 10 items in a range

November 22, 2008 By admin

The autofilter feature in Excel allows you to show a subset of your data based on a specified criteria. The top 10 is such a criteria.

To show only the top 10 values (or any other number) for a particular column follow these steps :

1. Click Data –> Filter –> Autofilter.

2. In the column which have the data to be evaluated, click the arrow of the autofilter.

3. From the drop-down list select Top 10.

4. The Top 10 window will open.

The Top 10 Window has three combo boxes which allow a lot of flexibility in specifying the required subset of data. To avoid confusion I will take some possible scenarios as follows:

1. You want to show the top 10 items (default). Leave everything as it is and click OK. The top 10 items will be displayed.

2. You want to show the top 15 items. In the middle combo box click the bottom arrow until you reach 15, or just type 15 then click OK. The top 15 items will be displayed.

3. You want to show the bottom 10 items. In the left combo box select “Bottom” then click OK. The bottom 10 items will be displayed.

4. You want to show the top items, up to 25% of your data (i.e. the top quarter of your data). In the left combo box select “Top“, in the middle combo box type 25 and in the right combo box select “percent“, then click OK. The top quarter of your data will be displayed.

Note:

No sorting is performed here. The top/bottom items will be shown as they appear before the filtering.

Applies to: Excel 2003

Filed Under: Data Tagged With: Autofilter, Filter, Top 10

How to filter and count unique values among duplicates

October 2, 2008 By admin

If you have a column of data that contains duplicates, then you can use the advanced filter option to identify and copy the unique values to a different location. You can then easily count those values using COUNTA function or the ROWS function.

Perform the following steps to achieve this goal:

1. First make sure that your column has a header row.

2. Select your data range (including the header row).

3. On the Data menu point to Filter, then click Advanced Filter. The Advanced Filter window will open.

4. Click Copy to another location.

5. The List range text box should have your data range. If not then click on it, go to your worksheet and select the range.

6. Click on the Copy to text box. Go to your sheet and click on a blank column (or just a single cell) where you want your unique vales to be copied.

7. Select the Unique records only check box and click OK.

8. To count the unique values just copied, use the COUNTA function or the ROWS function. E.g if the unique values are in the range E1:E6 then in a separate cell enter one of the following formulas:

1. “=COUNTA(E2:E6)”

2. “=ROWS(E2:E6)”

Applies to Excel 2003

Filed Under: Data, Lists Tagged With: Autofilter, Count, Duplicates

How to hide rows based on a cell value

July 21, 2008 By admin

The autofilter feature in Excel allows you to show a subset of your data based on a list of unique values derived by the auotfilter. You can also use this feature to hide rows of data based on certain conditions you specify. here is how you can do it.

1. Click Data –> Filter –> Autofilter.


2. In the column which have the criteria for hiding rows,click the arrow of the list box.

3. From the drop-down list Select custom.
4. The Custom Autofilter window will open.

4. Suppose in this example we want to hide all rows with value less than 10, so In the list where it says “Show rows where” , select “is greater than or equal”.
5. In the list to the right select the value which qualify the row to be shown. In our example we will select 10.

This will hide all the rows which have a value less than 10.

Applies to: Excel 2003

Filed Under: Customization, Data Tagged With: Autofilter, Data, Hide, Rows