How to sort and filter data in Excel?

In this walkthrough, we will illustrate how to sort and filter data in Excel. Followed by:

  • Take an in-depth look at Excel Filter Option &
  • Copying Excel Cells through the Fill handle

Aimed At:

  • People who know nothing about Excel and want to know the Basics

Prerequisites:

Please Read :

Cool Stuff:

  • Learning About one of the most powerful options Excel Offers, Filters

Filtering data

You can use the Filter option In Excel to filter out the Data of your requirement in a long and complex table. The search results are based upon the conditions you specify and only those records which pass that certain condition( or conditions) are shown in the results. Sounds Complex? It isn’t. Let us show you how it works…

Let us start with a Basic Table which shows a product List of an IT Shop. If you followed our previous tutorials, setting something like this would require you less than 10 minutes.

Taking a look at Sort option

Now, Suppose you are required to sort the table in such a way that it shows the product with the highest price as the first record and the lowest as the last. With Excel Sort option, You don’t have to make the table again from Scratch. just use the Sort option.

1) Click anywhere in the Price Column, through which you want to sort the table. ( in this case, click on a cell whose address starts with E).

2) Go to the Editing group in the Home tab and Click Sort and Filter Option, As highlighted below.

The drop down menu will show different options through which you can sort. In this case:

– Smallest to Largest

– Largest to Smallest. ( Click this one, to sort from largest to smallest)

Boom! Excel sorts the whole table based on your option. the Product with the Largest Price is now on top and bottom in the last. You can search Alphabetically too through the Product column. ( if that is your goal, click anywhere in the product column instead of Price and repeat the Process)

Taking a look at Filter option

Taking the same approach a little futher. We can also show just some limited no. of records from the whole table which meets our set criteria. E.g:

  • We can set criteria from the Products column to Laptop and Excel will only show Laptops. same goes for monitor and Desktop.
  • We can set a condition on the Price Column to:

Show only records which have a price equal or below $2500

Show only records which have a price greater than 2500.

Show only records which are equal to $2500.

Show only the top 10 records etc…

To make it a little more useful you can apply 2 filter at once. One on the Product tab and the other on the Price tab. E.g to show only those laptops whose price is below $2500,

Well ! Lets go through this example to make it a little more obvious.

1) Click anywhere inside the column you want to apply the filter to. in this case The Product Column

2) Go to the Editing group in the Home tab and Click Sort and Filter Option, As highlighted below.

3) The drop down menu will show different options. Click Filter

4) Excel Recognizes that you want to Create Filter for your tables and automatically puts the drop down icon inside the header cells. Click the drop down button in the Product Column.


5) Click the “Select All” checkbox to deselect this Then Click “Laptop” to show the laptops only.

6) Now, Click the drop down button in the Price column to specify another filter based on the Price. Click on Less than and Enter 2500. You can also specify any other condition other than this as stated above.

Excel filters the table and shows only two records which fulfill both these 2 conditions ( Product = Laptop and Price < $2500) Which are

Copy Excel Cells through the Fill handle

Excel allows you to Copy the Cells to one or more columns through the fill handle option. The fill handle is actually quite powerful when it comes to copying cells, it recognizes patterns in the cells and makes your work Easier . Lets go through the following example.

Suppose you want to fill the Cells below January with the remaining months. What you would do is.

1) Move the mouse to the lower left of the cell you want to paste from. A plus icon will replace the mouse, Click and drag the mouse to the cell to paste the months till and release the mouse button.

Excel Recognizes the Pattern and fills the Remaining cells with respective Months.

That’s it for today. I hope you learnt something through this post and will be following us in the future.
Wanna Learn some more. Check out our Previous Posts.

Leave a Comment