When working with tables, there is often a need to “gather into a pile” a number of similar data, for example, all people with the last name “Ivanov”, or all prices in the range from 500 to 1000 rubles. Naturally, MS Excel offers a number of tools for such operations, but first of all, when we're talking about about sorting and filtering, we remember about MS Excel data filters.
How to enable data filter in MS Excel?
To set a filter on table columns, select the table header with the mouse, and then on the "Home" tab select , and further "Filter". Downward arrow buttons will appear in the right corner of the column header cells. Click on it and you will see a very solid list of various built-in filters.
Let's try to understand this diversity.
- Filter by color. It only works if some of the table cells are marked with color (for example, if cells with income are marked green and expenses are marked red, they can be very conveniently filtered by color).
Result of applying a filter by color
MS Excel numeric and text filters
- Number/Text Filters (depending on whether the column contains numbers or text). The largest and most multifunctional group of filters. When you select any item, the “Custom Auto Filter” tool will launch, offering filtering options for every taste and color. It’s quite easy to understand them, just look at the examples below:
Filter all values in the table with a price less than 160.
Filter all values in the table with a price less than 160 AND at the same time more than 80
Filter out all values in the table that are not equal to 180 and 200
- Simple filter. There’s nothing to think about here - uncheck the boxes next to the data that needs to be filtered and click “Ok”. As a result, only the necessary information remains on the sheet.
The simplest filter in MS Excel - what you select is filtered
How to disable filter in MS Excel table?
Resetting a filter in MS Excel is even easier than setting it - again click the arrow on the right side of the table header cells, and in the appeared context menu select item "Remove filter from column".
If you want to completely remove the filter from the table, just repeat the same steps as at the very beginning - on the "Home" tab select Sort & Filter tool where click again "Filter".
Congratulations, now data filters are in Excel spreadsheet completely disabled.
Filtration Excel data helps to quickly set conditions for those rows that need to be displayed, and hide the remaining rows that do not meet these conditions.
The filter is installed on table headings and subheadings; The main thing is that the cells on which the filter will be installed are not empty. And it is located in the menu Excel workbooks on the “Data” tab, “Sorting and Filter” section:
By clicking on the “Filter” icon, the top cells of the range will be defined as headings and will not take part in filtering. Headings will be marked with an icon. Click on it to see the filter options:
Filters in Excel allow you to sort. Remember that if you have not selected all the columns of the table, but only some of them, and apply sorting, the data will be lost.
“Filter by color” allows you to select rows in a column that have a specific font or fill color. You can only choose one color.
“Text filters” make it possible to set certain conditions for strings, such as “equal to”, “not equal to” and others. By selecting any of these items, a window will appear:
You can set the following conditions here:
- The conditions “equal” and “not equal” do not require explanation, because everything is very clear with them;
- “greater than,” “less than,” “greater than or equal to,” and “less than or equal to.” How can strings be compared to each other? To understand this, remember how Excel performs sorting. Those. The further a row is in the sorting list, the greater its value. The following statements are true (correct): A<Б; АА>A; A<=Я; 5 яблок < апельсин.
- “begins with,” “does not begin with,” “ends with,” “does not end with,” “contains,” and “does not contain.” In principle, the conditions are self-explanatory and can take a character or a set of characters as values. Pay attention to the hint in the window located below all the conditions (explanations will follow).
If necessary, you can set 2 conditions using logical “AND” or “OR”.
If “AND” is selected, all conditions must be met. Make sure that the conditions do not exclude the friend, for example "<Значение И >Meaning", because nothing at the same moment can be both more and less than the same indicator.
When using "OR", at least one of the specified conditions must be met.
There is a hint at the very end of the custom autofilter window. Its first part: “The question mark ““?” means any one character...”. Those. when setting conditions when it is impossible to accurately determine a character in a specific place in a line, substitute “?” in its place. Examples of conditions:
- Starts with “?va” (starts with any character followed by the characters “va”) will return the results: “Ivanov”, “Ivanova”, “quartz”, “matchmaker” and other strings that match the condition;
- Equals "???????" – will return as a result a string that contains any 7 characters.
The second part of the hint: “The sign ""*"" denotes a sequence of any characters." If it is impossible to determine in the condition which characters and in what quantity should be in the line, then substitute “*” instead. Examples of conditions:
- Ends with “o*t” (ends with the characters “o”, followed by any sequence of characters, then the character “t”) will return the result: “sweat”, “cake”, “turnover” and even this one - “rvnshchuooovyunisvrunct”.
- Equal to "*" – will return a string that contains at least one character.
In addition to text filters, there are “Numeric filters”, which basically accept the same conditions as text ones, but also have additional ones related only to numbers:
- “Above Average” and “Below Average” – returns values that are above and below the average, respectively. The average is calculated based on all numeric values in the column list;
- “First 10...” – clicking on this item brings up a window:
Here you can set which elements to display first from the largest or first from the smallest. Also, how many elements to display if the “list elements” item is selected in the last field. If the “% of the number of elements” item is selected, the second value specifies this percentage. Those. if there are 10 values in the list, the highest (or lowest) value will be selected. If there are 1000 values in the list, then either the first or the last 100.
Sometimes tables can contain quite a large amount of data, and this data will often be presented in the form of a list. In this case, tools such as sorting lists and filtering them are very helpful. However, the list must be formatted in a certain way, otherwise the sorting and filtering tools will not work.
Typically, a list consists of records (rows) and fields (columns). The columns must contain the same type of data. The list must not contain empty rows or columns. If the list contains headings, they should be formatted differently than the rest of the list elements.
Sorting lists
Sorting or arranging lists makes it much easier to find information. After sorting, records are displayed in the order determined by the column values (alphabetical, ascending/descending by price, etc.).
Make a short list to practice with.
Select it.
Click the button "Sort and Filter" on the panel "Editing" tapes "Home".
Select "Sorting from A to Z". Our list will be sorted by the first column, i.e. according to the full name field.
If you need to sort the list by several fields, then the item is intended for this .
Complex sorting involves ordering data across multiple fields. You can add fields using the button .
As a result, the list will be sorted according to the established complex sorting parameters.
If you need to sort a field in a non-standard way, then the menu item is intended for this "Custom list.." drop down list "Order".
You can move sorting levels using the buttons "Up" And "Down".
Don't forget about the context menu. From it, you can also configure the sorting of the list. In addition, there are such interesting sorting options related to the selection of one or another element of the table.
The main difference between a filter and ordering is that during filtering, records that do not satisfy the selection conditions are temporarily hidden (but not deleted), while when sorting, all records in the list are shown, only their order changes.
There are two types of filters: regular filter(it is also called an autofilter) and advanced filter.
To apply an autofilter, click the same button as when sorting - "Sort and Filter" and select "Filter"(of course, a range of cells must be selected before this).
Buttons with arrows will appear in the list columns, by clicking on which you can configure the filter parameters.
Fields that have a filter on appear with a funnel icon. If you move the mouse pointer over such a funnel, the filtering condition will be shown.
To create more complex selection conditions, the paragraph is intended "Text filters" or "Numerical filters". In the window "Custom AutoFilter" you need to configure the final filtering conditions.
When using an advanced filter, selection criteria are specified on the worksheet.
To do this you need to do the following.
Copy and paste the list header into the free space.
In the appropriate field(s), specify filtering criteria.
Filtering is certainly one of the most convenient and fastest ways to select from a huge list of data exactly what is needed at the moment. As a result of the filter process, the user will receive a small list of the necessary data, which can be easily and calmly worked with. This data will be selected according to a certain criterion, which you can configure yourself. Naturally, you can work with the selected data, making full use of all other features of Excel 2010.
When working with tables, data can be selected in two ways - use a custom autofilter in Excel, or focus on a small set of basic functions and formulas. The second option is much simpler and easier, which means it is with this that we will begin our acquaintance with the capabilities of the updated table processor.
So, you have a table with an array of various data that you received at work, and now you need to select some specific values. Fortunately, you can use an autofilter in Excel, which will leave on the screen only the information that is necessary.
Here we see the main filtering functions located on the Home tab. You can also take a look at the “Data” tab, where we will be offered a detailed filtering control option. To organize the data, you need to select the required range of cells, or, alternatively, simply mark the top cell of the required column. After this, you need to click the “Filter” button, after which a button with a small arrow pointing down will appear on the right side of the cell.
Filters can be easily “attached” to all columns.
This will greatly simplify the sorting of information for future processing.
Now let's look at the drop-down menu for each filter (they will be the same):
— sorting in ascending or descending order (“from minimum to maximum value” or vice versa), sorting information by color (the so-called custom);
— Filter by color;
— ability to remove the filter;
— filtering parameters, which include numeric, text and date filters (if such values are present in the table);
— the ability to “select all” (if you uncheck this box, then completely all columns will simply no longer be displayed on the sheet);
— “column names”, where all the columns used in the table are visible. If you uncheck the box next to certain items, then they will also no longer be displayed, but here this can be done selectively.
If you apply any filter, the button image will change and take the form of the filter you selected.
If we talk about numerical filters , then here the program also offers a fairly large number of different options for sorting the available values. These are: “Greater than”, “Greater than or equal to”, “Equal to”, “Less than or equal to”, “Less than”, “Not equal to”, “Between specified values”. We activate the “First 10” item and a window appears
Here you can choose how many first values we are interested in (just a number that you can select using the arrows, or specify yourself) and how they need to be sorted (Smallest, largest, or even a percentage of the total number of elements).
If you select the “Above (or below) average” item, then you will be presented with those rows whose values will match the request. The arithmetic mean is calculated automatically, based on the data collected from the column.
Custom AutoFilter in Excel 2010, as was said, it gives expanded access to filtering options. With its help, you can set a condition (consists of 2 expressions or “logical functions” OR / AND), according to which the data will be selected.
Text filters were designed to work exclusively with text values. Here, the following parameters are used for selection: “Contains”, “Does not contain”, “Starts with...”, “Ends with...”, as well as “Equal”, “Not equal”. Setting them up is quite similar to setting up any numerical filter.
Let's apply simultaneous filtering by different parameters to different columns of our report regarding warehouse performance. So, let “Items” begin with “A”, but in the column “Warehouse 1” we indicate that the result should be greater than 25. The result of such a selection is presented below
Filtering, when it is no longer needed, can be canceled in any of the possible ways - use the “Shift+Ctrl+L” button combination, press the “Filter” button (the “Main” tab, the large “Sorting and Filter” icon, included in the “ Editing"). Or simply click the “Filter” button on the “Data” tab.
Of course, filtering is a fairly convenient element of data selection in Excel 2010, but sometimes there may be a need to make a more detailed selection of information that is simply not provided for by standard functions.
Let's say now we need to filter using a certain condition, which, in turn, is a combination of conditions for filtering several columns at once (there may be more than 2). In this case, only an advanced custom filter can be used, in which conditions can be combined using AND/OR logical functions.
Let's look at an example to better understand the filter's capabilities. Let's say we have a table with search queries in the Yandex and Google systems. We need to determine which of the existing queries has a position less than 10 in each of the specified systems. Since there can be a lot of requests, you need to perform a number of simple steps.
In separate and completely free cells we copy the names of the columns by which we are going to filter the data. As mentioned above, this will be “Visibility in Yandex” and “Visibility in Google”. The names can be copied to any adjacent cells, but we will choose B10 and C10.
It is under these cells that we will enter the conditions for the upcoming filtering. If it is necessary (and this is necessary for us) to take into account both conditions at once (this logical function is called AND), then the filtering conditions should be placed in one line. If only one thing needs to be taken into account (the OR function), then the conditions must be placed on different lines.
Now we look for the “Data”, “Sorting and Filter” tab and click the small “Advanced” icon and see this window
“Advanced filter” allows you to choose one of the possible options for action - filter the list right here or take the received data and copy it to another location convenient for you.
In the “Source Range” you must specify the address (if the program does not do this on its own) of the entire range of cells whose data needs to be filtered. This can be done either by manually specifying the required addresses, or simply by selecting the boundaries of the required table with the mouse.
The “range of conditions,” as you guessed, contains the addresses of those cells in which the filtering conditions and column names are stored. For us it will be “B10:C12”.
If you decide to move away from the example and select the “copy result...” function, then in the 3rd column you need to indicate the address of the range of those cells where the program needs to send the data that has passed the filter. So we will also select this option and specify "A27:C27".
We confirm the program and, if everything was done without errors, and the specified conditions are met, then we will see this result
Good luck in your work.
Sometimes when working with a large database, it becomes necessary to select information according to a certain criterion (for example, show only those people who were born in 1980). To perform tasks of this kind, use a filter in Excel.
Video on filtering and sorting data in Excel
Types of filters in Excel
Filtering is the selection of the necessary data from a list for subsequent work with it. The result of this procedure will be certain lines that fit the selection criteria. The remaining entries are temporarily hidden and not used until the user disables the filter. You can perform standard actions with the selected data: editing, formatting, printing, creating graphs, charts, etc.
There are 2 filtering methods in Excel: AutoFilter and Advanced Filter. You can launch them through the menu bar by clicking “Data - Filter”. Using the first option, you quickly select the necessary information with simple search criteria. In autofilter mode, the title line of the table in each column will contain a button with an arrow, by clicking on which you can specify the selection criteria. For each column you can set your own settings. In this mode you can set the following parameters:
To remove a filter from one column, you need to click on the “All” item in the list of elements. If you need to cancel it for the entire table, you must select “Data - Filter - Display All” in the menu bar. Removing an autofilter is carried out in the same way as launching it.
Advanced filter
An example of creating an advanced filter - you need to specify the range of source data, filters and the range where the filtered data is placed
The second filtering option gives the user more options for selecting the necessary data. To run the advanced option, you need to create a copy of the table headers (i.e. just copy the header). This will be the range of conditions. Then you need to fill this range with selection criteria. But here it is important to adhere to the rules: if you need values to be selected according to two parameters (for example, the student’s last name and grade), then the conditions are written in one line; if the criteria are selected in the “OR” mode (car make or engine size), then they are recorded in different lines.
Let's say there is a table with 2 columns - product name and quantity. There are 3 products in total - bananas, oranges, tangerines, and the quantity is 10, 20 and 15 pieces, respectively. After the header has been copied, you can create a condition, for example, to show products whose quantity is less than or equal to 15. That is, under the copied header in the “Quantity” column you need to write<=15. Затем надо запустить расширенный фильтр, указать исходный диапазон (исходная таблица), диапазон условий (таблица, где указано «кол-во <=15») и нажать «ОК». Исходная таблица изменится: теперь тут будут отображены только бананы (10 штук) и мандарины (15 штук).
Thus, the principle of filtering should be clear. In this way, you can select any elements from the database, no matter how large it is. This procedure will help simplify the user's work with large amounts of data.
