How To Extract Data From Excel Based On Criteria

You can watch a video tutorial here.

In Excel, filters are a popular way of extracting data to analyze it or to perform data cleaning operations. As long as the data is organized in columns, the filter can be applied to any column, even if the column does not have a header. To extract data you need to specify one or more criteria and only the rows that match those criteria are displayed. The rows that do not match the results are hidden. When you remove the criteria, all the rows are displayed again. In Excel, you can create in-column filters to filter and extract data. In this example, we will extract a list of all Maruti cars from 2012.

Step 1 – Enable the in-column filters

– Go to Data > Sort & Filter
– Click the Filter button

Step 2 – Apply the first condition

– Click the filter button in the ‘name’ column
– In the search box type: Maruti
– All values that start with ‘Maruti’ are displayed in the box
– Click OK

Step 3 – Check the list

– Only those rows that have ‘Maruti’ in the ‘name’ column are displayed
– A filter symbol appears on the in-column filter in the ‘name’ column to indicate that a filter has been applied
– The row numbers of the filtered rows appear in blue font to indicate that they are part of the filter results
– The number of records returned based on the filter condition is displayed in the status bar

Step 4 – Apply the second condition

– Click the filter button in the ‘year’ column
– Uncheck Select All
– Select ‘2012’
– Click OK

Step 5 – Check the final list

– Only those rows that have ‘Maruti’ in the ‘name’ column and ‘2012’ in the ‘year’ column are displayed
– A filter symbol appears on the in-column filter in the ‘name’ and ‘year’ columns to indicate that filters have been applied to these columns
– The row numbers of the filtered rows appear in blue font to indicate that they are part of the filter results
– The number of records returned based on the filter conditions is displayed in the status bar