# 3. Spotting aberrant data using “Sort & Filter” or “Conditional Formatting”

If you wish to check that your dataset has been entered properly in Excel, you may use a couple of tools like `Sort & Filter` and `Conditional Formatting`.

Here is an example where local temperatures have been retrieved from a weather station at Fløyen, in Bergen for the period 1/1/2017-31/12/2017 and placed in an Excel sheet. Since temperatures are retrieved from the station every 10 minutes, there are over 52000 values in total! It is virtually impossible to “manually” check that each and every data point in the table is correct or make sense. We thus need to find a way to spot the mistake more rapidly. Note: the column C contains the data that was retrieved; the column D contains the same data, but 5 aberrant data points (temperature between 70 and 200 degrees Celsius, see highlighted cell) have been inserted for the purpose of this example.

### Using Sort & Filter

Aberrant data points such as extreme values or outliers (values that come either higher or lower than a certain range of values) may be spotted using a sorting function or tool. The goal is to reveal them by sorting the data in an ascending or descending order. Check here how to use `Sort & Filter`

Select the columns A, B, C and D and click on `Custom sort...` in `Sort & Filter`. If your table contains headers, then tick off the `My data has headers` option. The name of your variables now appears in the `sort by` field. Select the variable `Temperatures (with errors)`, then select `values` and `largest to smallest`, and click `OK`. Your selection has now been rearranged accordingly and shows the highest values on top, thus revealing extremely high values which are unlikely to match temperatures in Bergen. You may do the same with `smallest to largest` to reveal the lower values.

Similarly, you may use the `Filter` function. Select the columns A, B, C and D and click on `Filter` in `Sort & Filter`. Arrowheads appear immediately to the right in the cells of the header. By clicking on the arrowhead to the right of `Temperatures (with errors)`, you may choose to sort the data in a similar way as for  `Custom sort...` , or you can use `Number Filters` to refine/limit your display to top/bottom values for example, or to data greater or lower than a given value.

### Using Conditional Formatting

Aberrant data points or cells containing data points may be given a different format (for example a different cell color) via the `Conditional Formatting` tool. Check here how to use this tool.

In our example, select the column of interest (column D:  `Temperatures (with errors)`) and go to  `Conditional Formatting`. Choose then which cells or values you would like to highlight (Top 10 items (see picture) or Top 10%, Bottom 10 items or Bottom 10 %, or anything else that makes sense for your analysis) and give these values/cells a specific color.

Then, use `Filter` as described above on column D and choose `Filter by color`. Click on the light red box under `Filter by Cell Color`. All the values that were marked by the `Conditional Formatting` tool in the previous step are now isolated. You may now check whether the data is aberrant or not.

### Searching for empty cells or specific symbols

Aberrant data is not only aberrant values; it may also be absence of value in a cell. Depending of where/how the data has been collected, absence of data is marked with N/A or NA or any other non-numerical character. But sometimes the cell is just empty.

To spot empty cells, select the table/column of interest, use the `Go To Special` tool under `Find & Select` in the `Home` ribbon. Select then the `Blanks` option and `OK`. All empty cells are now highlighted.

To find and highlight specific strings of characters (such as N/A or NA), press `CTRL + F` or go to `Find & Select` and then choose `Find...`. Next to `Find what`, type what you are searching for (such as NA) and click `Find All`. A table is now added to the dialog box that shows all occurrences and their address in the worksheet.

Fant du det du lette etter? Did you find this helpful?
[Average: 0]