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.
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.
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.
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.
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.
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
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 firstname.lastname@example.org to discuss how I can help or have a look at the Excel Courses I run.