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)
Step 2: Select Data - filter
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.
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.
Here is the result:
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:
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.
c.Text Filter / Number Filter
The Text Filter window appears when the column you filter has Text values
- 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
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.
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:
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
- 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.
And this is the result: