Excel: Filter, Sort, and Remove Duplicates

This post will cover three data management techniques of Excel: filter data, sort data, and remove duplicates.

Filter Your Data

Filtering your data will essentially hide all values except the chosen filter criteria. For example, if you want to see all students with the last name as Smith, you can filter for Text Equals Smith.

  1. Select the data that you want to filter. Or press Ctrl+A to select the whole worksheet.
  2. On the Data tab, in the Sort & Filter group, click Filter.

excel1

  1. Click the arrow in the column header to display a list in which you can make filter choices.

excel2To select by values, in the list, clear the (Select All) check box. This removes the check marks from all the check boxes. Then, select only the values you want to see, and click OK to see the results.

Sort Your Data

To Sort or rearrange your data for organizational purposes, do the following:

  1. Select a range of data. Or press Ctrl+A to select the whole worksheet.
  2. On the Data tab, in the Sort & Filter group, click to perform an ascending sort (A to Z or smallest number to largest). Or click to perform a descending sort (Z to A or largest number to smallest).

excel1

Sort Warning- you will want to select Expand the Selection in order to keep the data in the adjacent columns paired with the first column during the sort.

MORE OPTIONS-

To sort by specific criteria, do the following:

  1. Select a single cell anywhere in the range that you want to sort.
  2. On the Data tab, in the Sort & Filter group, click Sort. (The Sort dialog box appears.)

excel3

  1. In the Sort by list, select the first column on which you want to sort.
  2. In the Sort On list, select either Values, Cell Color, Font Color, or Cell Icon.
  3. In the Order list, select the order that you want to apply to the sort operation.
  4. To conduct a Multiple Sort (for example, first you Sort Last Names by Alphabetical Order, but then you want to sort GPAs by ranking number) Click Add Level and repeat steps 3-5.
  5. Click OK

Remove Duplicate Entries is handy if you know you have duplicate data entries, but don’t want to remove them by hand.

Note- Data entries must match completely for the Remove Duplicates function to work properly. Any different spaces or punctuation between duplicate data entries will give the appearance that the entries are unique entries and therefore, will not be removed.

  1. Select the data that you want to work with. Or press Ctrl+A to select the whole worksheet.
  2. On the Data tab, in the Data Tools group, click Remove Duplicates.excel4
  3. In the Remove Duplicates dialog box, make sure all columns are selected and check mark My data has headers. Click OK.excel5
  4. A confirmation will appear, alerting you to how many duplicates were removed.

View information on how to create header rows, format with every other row shadowed, and other duplicate formatted cells in Excel.