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.
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.
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.
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.
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.
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.
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.
There is another way to do this. Let’s click the arrow next to ‘Salary’ again.
This time we click on ‘Number filters’ in the filter menu and then on ‘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’.
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).
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.
Choose ‘is smaller than’ from the dropdown menu below the previous.
Next to “is greater than” we place $ 60,000.
In addition to “is less than”, add $ 120,000.
Click “OK” to filter the data so that only salaries over $ 60,000 and less than $ 120,000 are left.
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.
Click on ‘Number filters’ and then on ‘Less than’.
Add ‘70.000 ‘next to’ is less than ‘and then press’ OK’.
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’.
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.
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.
