One of the most used functions in Microsoft Excel is sort and filter. This is due to the advantages that it presents so that the user can find the information quicklytaking into account certain criteria.
Although it is not a difficult tool to handle, you have to be careful. If misused, it could misrepresent formulas and therefore generate incorrect information.
So that you become an expert in Excel, we will show you the steps you must take so that you can use this function in a professional way.. Do not miss any paragraph.
Sort and filter tool When is it convenient to use it and what are the advantages of doing so?
The sort and filter tool is very useful when you take into account some considerations. For example, if it is known that each time the filter is pressed, the data will change order, so that information can often be misrelated in relation to the other data.
Also, If this function is used incorrectly, only one column can be sorted, leaving the rest of the sheet unchanged.. This will imply that the data will end up out of order and the information will not be correct.
If these points are taken into account and the tool is handled carefully, the sort and filter function is very convenient to use, even more so when you have an average user knowledge of Microsoft Excel.
Steps to automatically alphabetize data from your spreadsheets
So you can sort, alphabetically, the data that you have in your spreadsheet you will have to do the steps that we will show you below:
ranks
Click on the header of the data range you want to filter, then follow these instructions:
- In the Home tab look for the group “Edition”.
- choose function “Sort and Filter”.
- Then click on “Filter”.
- You will see that in the place where you placed the cursor, the content of the cell appears with an arrow on the right side. If you see that it takes the data that you want to reorder, you must create a cell above the place where you chose. This will cause it to miss the first number you have with the arrow at the moment.
- When you press the drop-down menu, the option you want will appear. You can filter from smallest to largest or from largest to smallest. Click on any of the 2 and you will see how the entire column has been relocated in an orderly manner.
Boards
The steps you must take to order a table are the same as those we explained to you with a range. The difference that exists is that the cell that you will choose will be combined.
In this case, when you order from highest to lowest or vice versa, the system will take the last column of the combined cell set under the title as the default order.
Learn step by step how to master the tool in Excel documents
Next, We will show all the secrets that you must know so that you can successfully master the tool in Excel documents:
Numbers
To filter by numbers it is necessary that the column contains numeric data and not another type of format. If you want to perform this task you must follow this process:
- Select the range you want to order.
- Then go to “Edition” and click on “Sort and Filter”.
- Click on “Filter”.
- Go back to the data you have and click on the filter arrow.
- When the window is displayed you will have to select the tool “Filters by number”. And then set the condition for Microsoft Excel to return the related values.
With this tool you can filter by criteria that are related to “Equal to…”, “Greater or equal to…” , “Among…”, “Top 10 Best…”, “Above Average” or make a custom filter, among other conditions.
dates and times
If you have data related to dates and times, you can sort them by following this step by step:
- Choose the range you want to filter.
- Click on “Edition”Then in “Sort and Filter” and lastly in “Filter”.
- Now, you will have to press the filter arrow and choose the option “Date filters”.
You can choose filters “Is equal to…”, “Among…”, “Before…” Y “After…” in which an additional window will open in which you must choose the conditionals so that Excel can return the values that have those characteristics.
In addition, you can choose tools that are “Next week”, “Last month”, “This year” or choose a “Custom Filter”among many other tools.
You can perform the same steps for formats with hours.
Multiple columns or rows
When you want to sort multiple columns or rows, you will need to select the entire box, apply filter, and then choose the option “Custom Order…”.
Next, you will need to follow this guide:
- Once the popup window opens you will be able to choose by column or sort by cell values.
- You can also do it, if you have different colors, for this feature.
- Then, you will have to choose if you want the order to be from left to right or from top to bottom.
- Once you have chosen these criteria you can choose to see the results from highest to lowest or vice versa.
Cell, Font, or Icon Colors
The criteria that we will show you below are the same for these 3 themes, but you should keep in mind that it will only work if you have different colors, fonts, or icons in the cells.
The process you must follow is the following:
- When you have done the filtering, you will have to click on the arrow to display a pop-up window.
- Look for the option “Filter by color” (You will also find by fonts or icons, depending on what you need).
- The last thing you would have to do is choose the color of the cells to perform the filtering you need.
custom list
In the personalized list you will have the possibility of working with different formats that you have in the range of data that you want to order. In this way, you will be able to choose when you click on the option “Organize” and filter tool “Custom Order…”.
With this you will be able to access different options that will allow you to manage the values of a column, sort according to the cells and add or delete levels. In addition, you will be able to establish if the data contains headers or not.
Differentiating upper case from lower case
In order for you to access this type of filtering, you will have to follow the steps to access the personalized list, then you will have to click on “Options…” and check the box “Distinguishing uppercase from lowercase”.
From left to right
As with case sensitivity, you will also be able to filter from left to right. For this, you will have to access “Custom Order…”then click on “Options…” and enable the option “Sort from left to right”.
This will make all your data have a horizontal priority and not a vertical order.
By partial values in a column
Once you are in the tool “Custom Order…” you will have to locate the option “Column” and then click on the tool “Sort by”.
When you click on the arrow you will find a list with all your data, you only need to choose one of these so that Microsoft Excel can order them with this criteria.
Ranges within a larger range
Excel allows you to sort values within a larger range. This means that the first thing the program will do is establish a certain order of a range and then it will return to order within it by another condition.
To perform this “underfiltered” you will have to access the custom order and then choose the tool “Sort by”. Next, you will have to click on the arrow to choose “Cell Values”the colors (if applicable) or format icons, according to the nature of the information you want to order.
Facebook Comments
Disqus