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:
- Be certain that every column in your data list has a label letting you know what is in that particular column, such a State.
- In Cell N1 key in the word State. This tell Excel which cell you would like to use when matching criteria.
- Starting in cell N2, enter the states you would like to extract from the rest of your data list. Enter one state per cell.
- Select a cell somewhere within cells C through J in your main data.
- Display the Data tab of your Ribbon.
- 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.
- Be certain that the Copy to Another Location option has been selected.
- 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.
- Place your cursor in the Copy To box and select a cell where you want the records extracted to.
- If you would like to leave out any duplicate records, be certain the Unique Records Only check box is selected.
- Click on OK.
Excel will copy the records containing your designated state to whatever location you chose above and your original data remains unchanged!
