Excel Sorting and Filtering

Sorting data

Excel data sheets are often large and cumbersome; the use of sorting and filtering the data allows users to quickly identify required data.

How to sort data

In order to sort a data the series must contain useful headers, highlighting the full data set and selecting the Data ribbon presents the SORT option.

If we use the following data as an example: –

When the Sort option is clicked we are presented with a pop up box.

The columns can then be sorted by any of the table headers.

In the above example the data has been sorted by “Pizza Type” but you can see the formatting is not ammended to reflect the changes the sorting has applied.

We can add further levels to the sorting through the “Add level” button located at the top of the “Sort” pop up box.

By adding a second sorting level the data has now been sorted by a further criteria.  The upper level Pizza Type” is the dominant sorting filter meaning that the data will be sorted by this first.

Secondly the data is then sorted by “Location” .  Toggling the up and down arrows on the Sort pop up box changes which criteria is the dominant criteria.

Filtering data

Whislt sorting data changes the order in which the data is presented, filtering data simply hides any data that is not required whilst retaining the original structure of the data.

Filtering can be used to easily identify single or numerous data quickly.

Using the same original data as the sorting data examples.

How to filter data in Excel

In order to filter data we require a dataset that has unique headers for each column.  Select the header cell only and click on the “Filter” button under the Data ribbon.

When the filtering option is selected we suddenly see little drop down arrows on the headers: –

These drop down arrows can then be selected and a further fly out menu becomes visible.

The first three options of the fly out menu relate to sorting the data as explained above, this is simply a further method of accesing the sort options.

The filter options

  • Clear filterf from xxx
  • Filter by colour
  • Text filter

Will all be discussed shortly.

The final third section of the flyout menu has a series of checkboxes which allow us to select either all or a selection of the data based on the variables within the column to be filtered.  In this example I have the ability to select specific locations to be visible.

By selecting two variable “Birmingham” and “Nottingham” we are presented by the data relating to just thee two variables: –

The filter icon at the right of the cell C1 highlights that the data has been filtered on column C and allows users to be aware that there is hidden data due to filtering.

The filter can then be cleared using the Clear Filter From xxxx option as highlighted above, this then returns the original data.

 

Using Number filters

If we decide to filter the dataset based on a column containing numerical data then we can use the number Filters options.  Number filters are accessed in the manner as other filters however there is a further fly out menu containing many different options .

The flyout options for number filtering: –

Equals – return all data that is equal to a specific amount

Does not equal – returns all data that is not equal to a specified criteria

Greater than – returns all data greater than a specified number

Greater than or equal to – returns all data that is greater than or equal to the specified amount

Less than – returns all data with values less than the specified amount

Less than or equal to – returns all data equal to or less than the specified amount

Between – returns all data between two values

Top 10 – returns the top 10 values from the total data set

Above average – Calculates the average amount and returns all values above this value

Below average – Calculates the average amount and returns all values below this value

Custom filter

If we require the data to be filtered by multiple criteria or  by different criteria then we can use the custom filtering function.

The drop down boxes have the same options as the previously discussed filter options however there are additional options.

  • Begins with
  • Does not begin with
  • Ends with
  • Does not end with
  • Contains
  • Does not contain

These can be combined to narrow down very large data sets.