The result would be as follows: Example #4 Need to open the ‘Advanced Filter’ dialog box.We have to display the data if any of the conditions are met, and both the conditions are related to different columns. We need to specify both the criteria in different rows and different columns. Now we want to find sales in Qtr 1 or made in North India. Now we will click on the ‘Advanced’ command in the ‘Sort & Filter’ group under the ‘Data’ tab.įrom the ‘ Advanced Filter‘ dialog box, we will choose ‘Copy to another location’ and then define the A5: D26 as List Range, A1: D2 as Criteria Range, and F5: I26 as ‘ Copy To’ range. That is why we have mentioned the criteria below both column headings in the same row. Criteria Range is as below:Īs we have here ‘AND’ condition, i.e., we want to display the records where both the conditions are met. Now we want to get all the sales transactions of Qtr 1 and South India. We can see all the records having Name as ‘Suresh’ or ‘Taran’ are filtered out and displayed separately in a different cell range. Specifying the List Range as $A$5:$D$26, Criteria Range as $A$1:$D$3, and ‘ Copy to’ Range as $F$5:$I$26.We can use any of the options according to our needs, but we will be using the 2 nd option more often. Copy to another location: This option copies the desired data according to the criteria to the specified range.After analyzing, we can remove the filter using the ‘Clear’ command in the ‘Sort & Filter’ group under ‘Data.’ Filter the list in place: This option filters the list at the original place, i.e., on the list range itself.There are two actions in an advanced filter. We want to get all records having the name ‘Suresh’ or ‘Taran.’ The Criteria Range would be like below:įor ‘OR’ conditions where we want to display the records which satisfy any of the condition, then we need to specify the criteria in different rows.To specify the criteria, we can use the comparison operator, which are as follows: For Criteria, we need to copy the column headings on the top row and define the criteria below the field heading.As we click on ‘Advanced,’ a dialog box ‘Advanced Filter’ will open for asking List Range to filter, Criteria Range for defining the criteria, and Extract Range for copying the filtered data (if desired).Click on Data tab – Sort Filter group – Advanced command.To apply an advanced filter, first, we need to select any of the cells in the data range.Below are the steps for applying advanced filter in excel –
0 kommentar(er)
