قالب وردپرس درنا توس
Home / Tips and Tricks / Sort and filter data in Excel

Sort and filter data in Excel



The Excel application icon on a gray background

Sorting and filtering data provides a way to cut through the noise and find (and sort) only the data you want to see. Microsoft Excel has no shortage of options to filter huge data sets to exactly what is needed.

Sort data in an Excel spreadsheet

In Excel, click in the cell on top of the column you want to sort.

In our example, we click cell D3 and sort this column by salary.

the d3

On the ‘Data’ tab at the top of the ribbon, click ‘Filter’.

You will now see an arrow on top of each column. Click on the arrow of the column you want to sort to open a menu where we can sort or filter the data.

sorting arrow

The first and most obvious way to sort data is from small to large or large to small, assuming you have numeric data.

In this case we are sorting salaries, so we sort from smallest to largest by clicking the top option.

sort small to large

We can apply the same sorting to each of the other columns, sorting by the rental date, for example by selecting the option “Sort oldest by newest” in the same menu.

sort oldest to newest

These sort options also work for the age and name columns. For example, we can sort by oldest to youngest in age or alphabetize employee names by clicking the same arrow and choosing the appropriate option.

sort a to z

Filter data in Excel

Click the arrow next to ‘Salary’ to filter this column. In this example, we’re going to filter anyone who earns more than $ 100,000 per year.

sorting arrow

Since our list is short, we can do this in a number of ways. The first way, which works great in our example, is to turn off any person who earns over $ 100,000 and then hit “OK.” This removes three entries from our list and allows us to see (and sort) only the remaining entries.

uncheck the boxes

There is another way to do this. Let’s click the arrow next to ‘Salary’ again.

sorting arrow

This time we click on ‘Number filters’ in the filter menu and then on ‘Less than’.

number filters less than

Here we can also filter our results and remove anyone who earns more than $ 100,000 per year. But this way works much better for large data sets where you may have to manually click a lot to delete entries. To the right of the drop-down list that says ‘is less than’, enter ‘100,000’ (or whatever number you want to use), then press ‘OK’.

100,000 okay

We can also use this filter for a number of other reasons. For example, we can filter out all salaries that are above average by clicking “Below average” in the same menu (figures> below average).

filter below average

We can also combine filters. Here we find all salaries in excess of $ 60,000, but less than $ 120,000. First, we select ‘is greater than’ from the first drop-down list.

filter greater than

Choose ‘is smaller than’ from the dropdown menu below the previous.

filter less than

Next to “is greater than” we place $ 60,000.

60,000 okay

In addition to “is less than”, add $ 120,000.

120,000

Click “OK” to filter the data so that only salaries over $ 60,000 and less than $ 120,000 are left.

press OK

Filter data from multiple columns at once

In this example, we are going to filter by date hired and salary. We specifically look for people hired after 2013 who have a salary of less than $ 70,000 per year.

Click the arrow next to “Salary” to filter out anyone who makes $ 70,000 or more per year.

sorting arrow

Click on ‘Number filters’ and then on ‘Less than’.

number filters less than

Add ‘70.000 ‘next to’ is less than ‘and then press’ OK’.

70,000

Next, we will filter by the date each employee was hired, except those hired after 2013. To start, click the arrow next to ‘Date Accepted’, then choose ‘Date Filters’, then ‘After’.

date filter after

Type ‘2013’ in the field to the right of ‘is after’, then press ‘OK’. This leaves you alone with employees who both earn less than $ 70,000 per year and who were hired in 2014 or later.

year 2013 press ok

Excel has a number of powerful filter options, and each option is as customizable as you need it to be. With a little imagination, you can filter huge data sets down to just the bits of information that matter.




Source link