Guide to Automatic Filter and Filter detailed data in excel

And to perform this operation, we can do in two ways that are 'Automatic Filter and Detailed Filter'

For excel 2007, 2010 and 2013 the data extraction and filtering is almost the same, so I would like to do it on excel 2007 and will note the differences compared to 2010 and 2013 (if any).

1. How to filter data automatically in excel

- Applicable to simple cases where the filter data is filtered on only one column and there are at most two conditions in the same column.If you want to filter more, you must perform filtering many times.

- How to filter data automatically

Step 1: Select the data area to be filtered (including the header)

Guide to Automatic Filter and Filter detailed data in excel Picture 1

Step 2: Select Data - filter

Guide to Automatic Filter and Filter detailed data in excel Picture 2

The moment you see the Filter icon lit up, there is also a small square (button) on each right corner of the table in the title bar, which indicates that you have successfully selected the filter / filter.

Guide to Automatic Filter and Filter detailed data in excel Picture 3

Step 3: Filter data

To filter data, you just need to click on the button, if you want to filter the column, then click the button of that column

Example: Filter out products that are '1 liter bottles of lubricant'

First press the select button in the Product box, when the window appears you tick the box '1 liter of viscosity bottle' and then ok.When the Select all box is ticked, no data is filtered.

Guide to Automatic Filter and Filter detailed data in excel Picture 4

Here is the result:

Guide to Automatic Filter and Filter detailed data in excel Picture 5

You see that our new table only has data that satisfies the '1 liter bottle' condition.Besides, at the button of the Product box, there is a funnel to show that this is a cell that has been filtered.

In addition, Filter also supports the following features:

Guide to Automatic Filter and Filter detailed data in excel Picture 6

a.Allows you to sort data in alphabetical order .Sort data in order from A to Z (Sort A to Z) or sort data from Z to A (Sort Z to A).

b.Allows you to filter data by color, if our data uses different colors, this function will help us to filter the colors as you like.When you click on Filter Color , a list of colors will appear to choose, but if not using colors, only the Custom sort button will appear.

Guide to Automatic Filter and Filter detailed data in excel Picture 7

c.Text Filter / Number Filter

The Text Filter window appears when the column you filter has Text values

Guide to Automatic Filter and Filter detailed data in excel Picture 8

- Equals .: Filter values ​​containing information similar to .

- Does Not Equal .: Filter values ​​that contain information that are not the same as .

- Begins with .: Filter values ​​starting with .

- Ends with .: Filter values ​​ending with .

- Contains .: Filter values ​​including .

- Does not contain .: Filter values ​​do not include .

- Custom Filter: Set other conditions

Guide to Automatic Filter and Filter detailed data in excel Picture 9

You note: from excel 2010 onwards, excel support more filtering data search / Search.You only need to enter the filter condition in the Search box, excel will automatically filter out the results satisfying the condition.

Guide to Automatic Filter and Filter detailed data in excel Picture 10

Similar to the Text Filter , the Number Filter window appears when the column to filter contains the Number values

For example , when you want to filter for the quantity column, the Number Filters window will appear as follows:

Guide to Automatic Filter and Filter detailed data in excel Picture 11

Then we also perform the same filter as in the Text Filter .Only a few features change to suit the filtering of numeric data

2. Filter data details by Advanced Filters

- Apply in cases of complex filtering and filtering on many different column conditions

- The requirement of detailed filtration is to have standard filter area.Filter criteria condition region must have at least two lines.The first line is the title, the following lines contain input conditions

Guide to Automatic Filter and Filter detailed data in excel Picture 12

- How to filter data details by Advanced Filters

Step 1: Select a worksheet area

Step 2: Data-Advanced

When the Advanced Filter window appears, we see the following:

- Filter the list, in-place : Filter and produce results in the filter table itself, the data does not meet the conditions will be lost

- Copy to another location : Filter data and copy the results to another area

- List range : Address of the container containing the data you want to filter

- Criteria range : Address of sub-table

- Copy to : Address you want to set the result after filtering

- Unique Records only : Check this box to ensure that the group exporting the Copy To location is unique

You choose Copy to another location then enter the address for the sub table and the area you want to set the filter results (you can select any cell) and then click the ok button.

Guide to Automatic Filter and Filter detailed data in excel Picture 13

And this is the result:

Guide to Automatic Filter and Filter detailed data in excel Picture 14