Excel Functions Data Filter| Learn how to filter data of different categories and copy paste the filtered data into separate sheets.

MS Excel Data Filter Learn from the beginning 

What is Filter?

According to the information we can view any data separately, calculate or copy, paste it in a separate sheet. If we have a date wise cash receipt register and we want to see only the data of a specific date, then we can filter the entire table and see only the report of the selected date. You can also use multiple filters.

How to apply filter in Excel Sheet?

There are different ways to apply filters. By clicking Sort & Filter on the right side in the Home tab, we can apply the filter by clicking the filter option in the drop down. Ctrl + Shift + L can also use this shortcut key. The filter is also applied by pressing Alt >> H >> S >> F on the board.
Home >> Sort & Filter >> Filter 


After the filter is applied, the symbol of the filter will appear on every headings.

If for some reason the filter is not applied on the headings, first select the headings and then apply the filter.

The option of Filter is also available in the Data tab. You can also apply filters and remove them by clicking on the Filter symbol of the Data tab.
Data >> Filter 

Filter Data by Selecting specific information

After the filter is applied, click on the drop down symbol of the header of the column whose information is to be filtered. The window of the filter will open. The first 2 options are Sort A to Z & Sort Z to A. Click here to learn options for sorting. The third option is sort by color. We can also apply filters by the color of the cell and the color of the text.
Here we selected the first two names and clicked on OK.

Only those data which have been Filtered, have started appearing on the screen.

Filter by Searching Text or Number

Filter is also applied by typing the Text or Number in the search bar..

Column D contains location code. Filter option by number is available.

After selecting the number of any location code, we will see the result of that number.

Multiple Filter 

We have to see the report of how many and who are the employees of any one designation in the selected department with multiple filters. We have to filter two columns here. First the department has to be selected. Employees of the selected department will be seen. After that the designation has to be filtered.

In the image shown below, first the Development Department has been filtered.

 Employees of all the development departments started appearing and the rest became hidden. After that select Android Developer from the column of Designation.

 Android Developer’s is visible.

Date wise Filter

If the date is in the column, then the filter separates Automatic Year, Month & Day. We can easily select whole year or month.

With the + sign, we can select the Year & Month by Minimize and Maximize.

Copying and pasting the data into a separate sheet

Apply the filter first. Select the filtered data and copy it. 

Click and paste in another sheet. Only filtered data will be different.

Calculation of filtered data

Apply filter and select Numerical cells. Only Visible filter data will be calculated on the status bar of Excel.

Data sorting using Filter Option

Data sort by date

 Sort Oldest to Newest 
Sort Newest to Oldest 
The drop down option of filter provides the option of data sort.
If there are dates in the column, then the old dates in the top cell can be placed under the new dates, such an order by selecting option Sort Oldest to newest, or the reverse order by selecting option Sort Newest to oldest.

Sort data smallest to Largest
Sort data largest to smallest
If the cell has numerical data, then automatic 1.Sort Smallest to largest 2.Sort Largest to Smallest option. Sort Smallest To Largest With This Option Can Arrange Data In Ascending Order, And Sort Largest To Smallest With This Option Can Arrange Data In Descending Order.

Alphabetical order
Reverse Alphabetical Order
Similarly, the data of the text can be alphabetically and reverse alphabetically.

Learn Basic Data Sorting in MS Excel Data sort by Alphabetical Order & Reverse Alphabetical Order A to Z & Z to A

Leave a Comment