For the convenience of working with tables with a large amount of data, filtering is an extremely convenient tool. This function allows you to filter data according to various parameters, such as name, price or date. Before correcting the situation with a working filter, let’s look at the basic principles of its operation.
How to set up a filter
To set up filtering, it is enough to perform a sequence of actions:
- Activate one of the cells with data.
- Go to the “Sort and Filter” option on the “Main” tab – the “Editing” tool block.
- Also, the “Filter” button can be found in the “Data” tab.
- When the “Filter” is activated, arrows will appear in the cells with headings.
- When you click on the arrow in one of the columns, such as Date, a pop-up menu will appear.
- Here, uncheck the box next to “Select All” and activate the options that you want to display. After clicking “OK”, only the lines corresponding to the filter will remain in the table.
If there is a “Date” column, you can use a special filter that works only with calendar data.
- Again, click on the arrow in the “Date” cell.
- In the menu that appears, put a tick again – “Select all”.
- A little higher in this menu there is a category “Filters by date”, in which we select the required time period. In the case in the figure, the selected period is “Last year”.
Important! When using this filter, please note that it only works with today’s date.
Sort and filter
The Sort and Filter option helps you sort and filter not only dates, but also any other data. Here you can use grouping in alphabetical order, ascending or descending.
- First you need to select the column in which sorting will occur. The parameter works in one column, but entire lines with data are moved.
- If “Sort A to Z” is selected, the data in the “Name” column is in alphabetical order.
- To sort in reverse order, just select Sort Z to A.
- Depending on what kind of data is contained in the column, the sort type may change. For example, to sort information by date, other options will appear in the “Sort and Filter” menu – “Oldest to Newest” and “Newest to Oldest”.
- In the case of working with numerical data, sorting will be carried out according to the criteria “Sort in ascending order” or “Sort in descending order”.
Custom sorting
In some cases, standard data sorting is not enough. For example, in the case of identical values in one column. This can be the name of the customer, the price of the item, or the amount of the purchase. In such a situation, Excel developers have created another type of sorting, the parameters of which are set by the user himself.
Pay attention! The “Custom Sort…” option can be found in the “Editing” toolbox on the “Home” tab.
Select the table in which you want to sort the data. When you activate the “Custom sorting …” option, a window will appear with a number of conditions.
You can sort by one of the columns or by several. Here you are asked to select a column, sort type and sort order. In the “Value” parameter, you can select one of the sort types:
- Value.
- Cell color.
- Font color.
- Cell value.
The Order option provides several options:
- in alphabetical order – from A to Z;
- in reverse alphabetical order – from Z to A;
- Ascending;
- descending;
- from old to new;
- from new to old;
- custom list.
The type of parameter depends on the values that are in a particular column. For example, for text values, the order will be alphabetic, the number format will be sorted in ascending or descending order, and the dates will be sorted from newest to oldest or vice versa.
Why is the filter not working?
If there is a lot of data, but it is impossible to filter or sort them, then first of all you need to make sure that the appropriate value format is set in each cell of the column. That is, if you need to set a filter on a column with dates, then the format should be “Date”, and not “Text” or “Numeric”.
When working with “Custom Sort”, you can select one column, the values of which will be arranged in the required order. But at the same time, the permutation will be carried out in other columns. You don’t have to worry about the values getting mixed up – the rows move along with the other cells in the row. A similar situation occurs when the “Filter” is turned on. When you select a specific filter parameter in one of the columns, the values of all columns of the corresponding line are displayed.