How to separate data in Excel based on criteria
By
SpreadCheaters
You can watch a video tutorial here.
When working with a dataset in Excel, you may need to split the dataset or extract a subset of the data. This can be done using the filters in Excel. Filters are a popular way of creating a subset of the 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 filter data you need to specify one or more criteria and only the rows that match those criteria are displayed. These rows can then be copied to a new sheet.
Step 1 – Enable the in-column filter
– Go to Data > Sort & Filter
– Click the Filter button
Step 2 – Set the first criterion
– Expand the filter dropdown in the ‘name’ column
– In the search box type ‘Maruti’
– Click OK
Step 3 – Set the next criterion
– Expand the filter dropdown in the ‘year’ column
– Select ‘2018’
– Click OK
Step 4 – Copy the filtered rows
– Select the filtered rows
– Right-click and select Copy from the context menu or press Ctrl+C
Step 5 – Paste the data
– Go to a new sheet
– Right-click and select Paste from the context menu or press Ctrl+V
Step 6 – Check the result
– The data is separated from the parent dataset
