Lesson 19: Data sorting and filtering in excel

How to do Multiple Level Data Sorting and Filtering in Excel

This video is a part of free Excel Training Singapore, where I show you how to use Data sorting and filtering in Excel from the basics and also cover a lot of advanced Features.

Data sorting and filtering in excel allows users to sort and filter data according to the user’s needs.  Data can be sorted in various forms such as in alphabetical order, decreasing order, partial sorting, sorting based on color, from left to right and many others.

Benefits of sorting and filtering data –

• Systematic representation – sorting enables users to represent the data in a very systematic manner that enables fine presentation.
• Better understanding of data – when data is presented in a sorted and filtered manner then then data decoding and understanding becomes very easy. The not required data is removed and only the important data is used for working.
• Easy in analyzing – sorting and filtering enables users to easily analyse the results so occurring from a given set of data. When the data is presented in a well organized and sorted manner then it’s analysis yields absolute useful results.

How to sort data in excel? 

Sorting data in excel means to arrange or organize the given data in any form. Excel allows users to sort data in alphabetical order, decreasing order, sorting based on color and icon and so on. To learn how to sort data in any order, follow the following mentioned steps –

• Start by preparing a series of data having various entries comprising of numeric data as well as text.
• On the top most region of the work space, click on data. An option appears sort and filter. The left side shows the sorting and right side shows the filtering option
• To sort data, there are three options available, click on the very first option to sort data in alphabetical order from A-Z. To sort data in the reverse order from Z-A Click on the option below it.
• From the given data set, select the region of data that you wish to sort and hit the sort option. The data so selected will be sorted accordingly
• To sort data with headers, select the sort option. A dialogue box appears, hit the option that says my data has headers. Fill in the required fields as per the users choice and hit OK
• The data will be sorted according to the selected inputs.

How to do data filtering in excel ?

Data filtering in excel simply mean to remove the unwanted data and get the data only for a specific person or type. Excel is smart enough to distinguish between the numeric and alphabetical data, thus is shows differing filter option for both of them. To learn how to filter data , follow the following mentioned steps –

• Start by preparing a set of data having a combination of both text and numbers. Click on data option and select the sort and filter option. Select the filter option.
• As soon as the filter option is selected, arrow icons appear next to the header of the rows. This indicates that filtering is applied.
• Click on the inward arrows that appear. A menu appears that has options such as sort the data ,text filters such as equal, not equal and so on
• To filter data for any one of the names mentioned in the data, select the equals option from the text filter data. Another pop up menu appears, enter, for example BOB there and hit enter.
• The entire data will be filtered and data for only BOB will be displayed. To clear the filter so applied, click on the clear option that shows next to the filter option

How to work with advance filter function? 

Advance filter function comes handy when the users wishes to carry out multiple data filtering, each by fulfilling a condition. To learn how to do this, follow the following mentioned steps –

• Start by preparing a set of data having combination of numeric and alphabetical data.
• To use the advanced sorting filter, copy the headers of the initial data to other cells. Beneath those cells, specify the condition of the data that the user wants to extract
• Select the entire data set, and select on advance option from the various filter options. This will open up the advance filter box.
• Select the second option which is copy to another location. The below mentioned fields will be auto filled by excel. Just specify the location for copy to option and hit OK
• The output so received will be data from the initial data range but, if the initial data range is changed or edited, the output will not change

Learn Microsoft Excel Advanced course in Singapore!

Microsoft excel has time and again proved its usefulness and importance in almost all fields of work. To equip yourself with such a powerful software and to make the most out of it, join Excel Advanced Course in Singapore today!

This Lesson Explain all the basic and advanced concepts about data filtering and data sorting in Excel.

It starts with the basic initial level and covers advanced examples.

It explains the following topics:

• Data Sorting (by name, text, color, icon and custom list)
• Multi-Levels of Sorting
• Data Filter (by text, number, date, and color)
• Advanced Filter in Excel
• Filtering and Sorting in Excel Data Tables

Excel has a lot of sorting options such as sorting from left to right, sorting based on color and icon and doing multi-level sorting. All these sorting options can be accessed from the single sorting dialog box.

Similarly, you have a lot of filtering options – such as filter based on numbers, text, dates and even colors.

Excel has many data analysis functionalities and a good knowledge of filtering and sorting data in Excel will help you analyze a huge amount of data easily.