Top Tips to Sorting and Filtering in Excel

Aug 22, 2019 | Excel Hints and Tips

Sorting and filtering are 2 of the most used analysis tools in Microsoft Excel.  By arranging the data in a logical order it is easier to see any patterns and using filters to extract specific details tailors our view to exactly what we want to see eliminating all unnecessary clutter.

Sorting

To sort data it is best to arrange your data with headings in the top row and avoid blank rows or columns.  With the quick sort options this is vital or you may find that part of your data is sorted and the other part isn’t leaving your data jumbled and unusable.

Quick Sort

Image of Quick-sort-icons

With the layout of the data as described above click on 1 cell in the column to be sorted and use the sort A to Z or sort Z to A icons.  These do not only sort alphabetically but also work with number or date fields.  If there are no blank rows or columns the data whole range of data is sorted otherwise it stops at the blank rows or columns.  Individual blank cells are not a problem.

Sort

Image of the Sort-icon

This still works if there are no headings or if there are blank rows or columns.  If there are no headings the “My data has headers” tick box must be unticked.  If this is done then the sorting is by column A, column B etc rather than the more meaningful column names we normally assign to our data.  Where there are blank rows or columns then the data must be selected before sorting begins.  Without blank rows or columns an individual cell can be selected and the current region is automatically included.

Image of Sort-dialogue-box

Multiple sorting levels can be included by using the Add Level button.  Sorting can even be performed based on the background colour, font colour or conditional formatting icon in a cell by amending the “sort on” option to the appropriate choice.

Filter

Image of Filter-drop-down

The filter works in the same way as Sort regarding blank rows and columns.  When turned on filter arrows appear on each column heading.  A list of each unique entry in this column appears with tick boxes that can be used to determine which data you want to see.  You can see if the data is filtered because the drop-down arrow shows with a filter symbol, the row numbers turn blue and the number of records currently displayed out of the total number stored shows on the status bar at the bottom of the screen.

There is also a search box which is perfect if the list is long with many unique entries.  As you start typing a shorter tick box list appears showing only data that contains this specific information.

There is another option from the drop-down which changes depending on whether the data you are filtering is text, numbers or dates.  Text filter is available if the data is text with options such as begins with, ends with and contains.  Number filter appears for numerical data with choices such as greater than, less than and between.  Date filter is the option if the data consists of dates and as well as looking for dates before, after or between date ranges there are also options of this week, last week, next week and similar for months or years.

Filtering can also be performed based on the text or fill colour of the cell.  Only colours already applied appear in the drop-down list.

Sorting and filtering in Excel are invaluable tools to start the process of analysing the information you are storing.

Want to learn more about Excel then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.

MEMBERSHIPS

FOLLOW LARA