When you have thousands of rows stored in an Excel 2020 spreadsheet, it is quite difficult to identify data especially when you need to find rows that match your specific requirement or description. Microsoft Excel has many built-in Excel tools to help with data management to sort data in Excel as well as to filter it. The ‘filter data in Excel’ tool gives you the ability to filter a column of data within a table to isolate the key components you need. While the sorting tool allows you to sort data by date, number, alphabetic order and more options
In this article, we will teach you how to sort data in Microsoft Excel and how to filter the same.
What is Microsoft Excel?
Microsoft Windows Excel is a software program that is included in the Microsoft Office suite. It is used to create excel spreadsheets, that are the documents in which data is laid out in rows and columns like a big table.
Because of its extreme versatility and power, Microsoft Excel has become one of the popular software programs in the business world since its launch in 1985.
What do you mean by spreadsheet?
An excel spreadsheet is a different way of organizing the information into rows and columns to make it simpler to read and manipulate. Below is a screenshot of a blank spreadsheet:
As we could see above, the document is consists of columns (the vertical boxes named A, B, C, D, etc. on the topmost of the window), and rows (the horizontal boxes mentioned 1, 2, 3, 4 etc. on the last of the screen). At the cross of every column and row is a cell into which a person can enter either text or number. The address of any particular cell is made with a combination letter of the cell’s column with the number of the cell’s row.
How to Sort Data in Microsoft Excel Spreadsheet?
If you want to sort data in excel then follow the steps mentioned below:
In the Windows Excel spreadsheet, you need to click inside the cell atop the column that you want to sort.
Click “Filter” from the “Data” tab available on top of the ribbon,
At the top of each column, you will now see an arrow. You need to click the arrow of the column that you wish to sort to bring up a menu that enables us to sort or filter the data.
The first and most basic way to sort data from the sheet is from smallest to largest or largest to smallest if we assume that you have numerical data.
For instance, if we are to sort, so we will sort from smallest to largest by clicking at the top option.
Similarly, we can apply the same sorting to any of the other columns in the spreadsheet, sorting by the date and month of hire, for instance, by choosing the “Sort Oldest to Newest” option in the same menu.
These sorting options are also going to work for the age and name columns. We can also sort by oldest to youngest, for instance, or arrange the employee names alphabetically by clicking the same arrow and selecting the appropriate option.
How to Filter Data in Excel?
Firstly, you need to click the arrow that is next to “Salary” to filter this column. In this case, we are going to filter out anyone who earns more than $100,000 per year.
Because our list is quite short, we can do this using couple of methods. The first technique, which works perfectly well in our example, is just to uncheck the person who makes more than $100,000 and then click on “OK.” This method will remove the three entries from our list and enables us to see and sort just those that remain in the list.
Well, hold on! There is yet another way to do this. Let us click on the arrow available next to “Salary” once more.
This time we are going to click on “Number Filters” from the filtering menu and then on “Less Than.”
Here you can also choose to filter your results, by removing anyone who makes over $100,000 per year. But this method will work much better for large data sets where you might be required to do a lot of manual clicking to remove entries. You need to type “100,000” or whatever figure you wish to take and then press on ‘OK’ which is available to the right of the dropdown box that says “is less than”.
We can choose to use this filter for several other reasons, too. For instance, we can filter out all the salaries that are shown above average by clicking on “Below Average” from the same menu (Number Filters, then on Below Average).
There is also an option to combine filters. Here we will find all salaries that are greater than $60,000, but less than $120,000. First, we are going to select “is greater than” in the first dropdown box.
Now, choose the “is less than” option in the dropdown below the previous one,
Now we will put $60,000 next to “is greater than”.
Then, add $120,000 next to “is less than”.
Now, select “OK” to filter data in excel and leaving only salaries greater than $60,000 and less than $120,000.
How can we Filter Data from Multiple Columns at Once?
In this case, we are going to filter by the date of hire and salary. We will look specifically for people who are hired after the year 2013, and with a salary of less than $70,000 per annum.
Firstly, Click on the arrow available next to “Salary” to filter out anyone who earns $70,000 or more per year.
Now, you should click on the option “Number Filters” and then select “Less Than.”
You need to add “70,000” next to “is less than” option and then click on “OK.”
Next, we are going to filter each employee by the date of hire and excluding those hired after 2013. To do so, click on the arrow available next to “Date Hired” and then select “Date Filters” and then “After.”
Now, you need to type “2013” into the field left to the right of “is after” and then press on “OK.” That is going to leave you just with people who together make lower than $70,000 per annum who were hired in 2014 or further.
Excel consists of many filtering features, and everything is as modifiable as you will want it to be. With some creativity, you can seek huge data down to just the pieces of info that are necessary.
This was our guide on how to sort data in excel and how to filter data in Microsoft excel. We believe this has been useful to you.
Latest Articles