Extract Records From Excel Using Advanced Filter

excel logoMany businesses use Excel to manage data that employees work with on a daily basis (i.e., invoice data, shipping records, product name, etc.). In some instances, when working with this information, it becomes necessary to extract records based on different criteria.

This is a prime example of when you would use filtering. Just suppose you have a business with customers from across the United States. Now suppose you have to prepare a report for all customers in one particular state. You will need to extract that information for that particular state.

Follow the steps below to learn how:

  1. Be certain that every column in your data list has a label letting you know what is in that particular column, such a State.
  2. In Cell N1 key in the word State. This tell Excel which cell you would like to use when matching criteria.
  3. Starting in cell N2, enter the states you would like to extract from the rest of your data list. Enter one state per cell.
  4. Select a cell somewhere within cells C through J in your main data.
  5. Display the Data tab of your Ribbon.
  6. In the Sort & Filter group, click the Advanced tool to display the dialog box. You should see the address of your original data table already in the List Range box.

excel advanced filter

  1. Be certain that the Copy to Another Location option has been selected.
  2. Place your cursor in the Criteria Range box and on your worksheet select the range of cells in column N that is the list of states. Be certain you include cell N1 which is the label.
  3. Place your cursor in the Copy To box and select a cell where you want the records extracted to.
  4. If you would like to leave out any duplicate records, be certain the Unique Records Only check box is selected.
  5. Click on OK.

Excel will copy the records containing your designated state to whatever location you chose above and your original data remains unchanged!


Scroll to Top


Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!