Data filter in excel. Advanced filter in Excel: how to do it and how to use it

Microsoft Excel is a ubiquitous and handy tool for working with spreadsheets. Wide more functionality make this program the second most popular after MS Word among all office programs. It is used by economists, accountants, scientists, students and representatives of other professions who need to process mathematical data.

One of the most convenient features in this program is data filtering. Let's look at how to set up and use MS excel filters.

Where are filters in Excel - their types

Finding filters in this program is easy - you need to open the main menu or just hold down the Ctrl+Shift+L keys.

How to set a filter in Excel

Basic filtering functions in Excel:

• selection by color: makes it possible to sort data by font or fill color,
• text filters in excel: allow you to set certain conditions for rows, for example: less than, greater than, equal to, not equal to, and others, as well as set logical conditions - and, or,
• numeric filters: sort by numeric criteria, for example, below average, top 10 and others,
• manual: selection can be performed according to self-selected criteria.

They are easy to use. It is necessary to select the table and select the section with filters in the menu, and then specify by what criterion the data will be filtered.

How to use an advanced filter in Excel - how to set it up

The standard filter has a significant drawback - in order to remember which selection criteria were used, you need to open the menu. And this is even more inconvenient when more than one criterion is specified. From this point of view, an advanced filter is more convenient, which is displayed in a separate table above the data.

VIDEO INSTRUCTIONS

Setting procedure:

1. Create a table with data for further work with it. It should not contain empty lines.
2. Create a table with selection conditions.
3. Launch advanced filter.

Let's look at an example setup.
We have a table with columns Product, Quantity and Price.

For example, you need to sort rows whose product names begin with the word “Nails.” Several rows fall under this condition.

The table with conditions will be placed in cells A1:A2. It is important to indicate the name of the column where the selection will take place (cell A1) and the word for selection itself - Nails (cell A2).

It is most convenient to place it above the data or on the side. Under it is also not prohibited, but it is not always convenient, since from time to time there may be a need to add additional lines. Indent at least one blank line between the two tables.

Then you need to:

1. select any of the cells,
2. open “Advanced filter” by following the path: Data – Sorting and filter – Advanced,
3. check what is specified in the “Original range” field - the entire table with information should go there,
4. in the “Range of conditions” you need to set the values ​​of the cells with the selection condition, in this example this is the range A1:A2.

After clicking on the “OK” button, the selection will take place necessary information, and only rows with the desired word will appear in the table, in our case it is “Nails”. The remaining line numbers will turn blue. To cancel a specified filter, simply press CTRL+SHIFT+L.

It's also easy to set up filtering for lines containing the exact word "Nails" without being case sensitive. In the range B1:B2 we will place a column with the new selection criterion, not forgetting to indicate the heading of the column in which the screening will be performed. In cell B2 you must enter the following formula ="=Nails".

• select any table cell,
• open "Advanced filter"
• check that the entire table with data is included in the “Source range”,
• in the “Range of conditions” indicate B1:B2.

After clicking “OK”, the data will be filtered out.

These are the most simple examples working with filters in excel. In the advanced version, it is convenient to set other conditions for selection, for example, screening with the “OR” parameter, screening with the “Nails” parameter and a value in the “Quantity” column >40.

How to filter in Excel by columns

Information in the table can be filtered by columns - one or more. Let's look at the example of a table with columns “City”, “Month” and “Sales”.

If you need to filter out data by a column with city names in alphabetical order, you need to select any of the cells of this column, open “Sorting” and “Filter” and select the “AY” option. As a result, the information will be displayed taking into account the first letter of the city name.

To obtain information using the reverse principle, you need to use the “YA” parameter.

It is necessary to filter out information by month, and also the city with a large sales volume should be in the table above the city with a lower sales volume. To solve the problem, you need to select the “Sorting” option in “Sorting and Filter”. In the settings window that appears, specify “Sort by” - “Month”.

Next you need to add a second sorting level. To do this, you need to select “Add level” in “Sorting” and specify the “Sales” column. In the “Order” settings column, select “Descending”. After clicking “OK”, the data will be selected according to the specified parameters.

VIDEO INSTRUCTIONS

Why filters may not work in Excel

Users often have difficulties when working with tools such as filters. Usually they are associated with violation of the rules for using certain settings.

The problem with the date filter is one of the most popular. Occurs after downloading data from the accounting system in the form of an array. When trying to filter rows by a column containing dates, the filtering occurs not by date, but by text.

Solution:

1. select the date column,
2. open the Excel tab in the main menu,
3. Select the “Cells” button and select the “Convert text to date” option from the drop-down list.

Popular user mistakes when working with this program also include:

• lack of column headers (without them, filtering, sorting, and a number of other important parameters will not work),
• the presence of empty rows and columns in the table with data (this confuses the sorting system; Excel perceives the information as two different tables independent of each other),
• placement of several tables on one page (it is more convenient to place each table on a separate sheet),
• placement in several columns of data of the same type,
• placement of data on several sheets, for example, by month or year (the amount of work can be immediately multiplied by the number of sheets with information).

And another critical mistake that does not allow you to fully use the capabilities of Excel is the use of an unlicensed product.

Its correct operation cannot be guaranteed, and errors will appear constantly. If you intend to use this mathematical information processing tool on an ongoing basis, purchase the full version of the program.

The advanced filter in Excel provides greater capabilities for managing spreadsheet data. It is more complex in settings, but much more effective in operation.

Using a standard filter, a Microsoft Excel user can not solve all the tasks assigned. There is no visual display of applied filtering conditions. It is not possible to apply more than two selection criteria. You cannot filter duplicate values ​​to keep only unique entries. And the criteria themselves are schematic and simple. The functionality of the advanced filter is much richer. Let's take a closer look at its capabilities.

How to make an advanced filter in Excel?

The advanced filter allows you to filter data by an unlimited set of conditions. Using the tool, the user can:

1. set more than two selection criteria;
2. copy the filtering result to another sheet;
3. set a condition of any complexity using formulas;
4. extract unique values.

The algorithm for using an advanced filter is simple:

1. We create a table with the source data or open an existing one. For example, like this:
2. Create a condition table. Features: the header line completely matches the “header” of the filtered table. To avoid errors, copy the header row in the source table and paste it onto the same sheet (side, top, bottom) or onto another sheet. We enter the selection criteria into the table of conditions.
3. Go to the “Data” tab - “Sorting and Filter” - “Advanced”. If the filtered information should be displayed on another sheet (NOT where the original table is located), then you need to run the advanced filter from another sheet.
4. In the “Advanced Filter” window that opens, select the method of processing information (on the same sheet or on another), set the initial range (Table 1, example) and the range of conditions (Table 2, conditions). Header lines must be included in ranges.
5. To close the “Advanced Filter” window, click OK. We see the result.

The top table is the result of filtering. The lower plate with the conditions is given side by side for clarity.

How to use the advanced filter in Excel?

To cancel the action of an advanced filter, place the cursor anywhere in the table and press the key combination Ctrl + Shift + L or “Data” - “Sorting and Filter” - “Clear”.

Using the “Advanced Filter” tool, let’s find information on values ​​that contain the word “Set”.

We will add criteria to the conditions table. For example, these:

In this case, the program will search for all information on products whose names contain the word “Set”.

You can use the “=” sign to find the exact value. Let's add the following criteria to the table of conditions:

Excel interprets the “=” sign as a signal: the user will now enter a formula. For the program to work correctly, there must be an entry in the formula bar like: ="= Set of region 6th grade."

After using the "Advanced Filter":

Now let’s filter the source table using the “OR” condition for different columns. The “OR” operator is also available in the AutoFilter tool. But there it can be used within one column.

We will enter the selection criteria into the conditions table: ="=Recruitment of the region 6th grade." (in the “Name” column) and =”

Please note that the criteria must be written under the appropriate headings on DIFFERENT lines.

Selection result:

The advanced filter allows you to use formulas as criteria. Let's look at an example.

Selection of the row with the maximum debt: =MAX(Table1).

Thus, we get the same results as after performing several filters on one Excel sheet.

How to make multiple filters in Excel?

Let's create a filter based on several values. To do this, we enter several data selection criteria into the conditions table:

Let’s use the “Advanced Filter” tool:

Now, from the table with the selected data, we will extract new information selected according to other criteria. For example, only shipments for 2014.

We enter a new criterion into the conditions table and use the filtering tool. The initial range is a table with data selected according to the previous criterion. This is how you filter across multiple columns.

To use multiple filters, you can create multiple condition tables on new sheets. The method of implementation depends on the task set by the user.

How to filter by row in Excel?

Standard methods - no way. Microsoft program Excel only filters data in columns. Therefore, we need to look for other solutions.

Here are examples of string criteria for an advanced filter in Excel:

1. Convert table. For example, make a list of three columns from three rows and apply filtering to the converted version.
2. Use formulas to display exactly the data in a row that you need. For example, make some indicator a drop-down list. And enter a formula in the adjacent cell using the IF function. When a specific value is selected from the drop-down list, its parameter appears next to it.

To give an example of how a row filter works in Excel, let’s create a table:

For a list of products, create a drop-down list:

Insert an empty line above the table with the source data. We will enter a formula into the cells that will show which columns the information is taken from.

Next to the drop-down list cell, enter the following formula: Its task is to select from the table those values ​​that correspond to a specific product

Download advanced filter examples

Thus, using the Dropdown List tool and the built-in Excel functions selects data in rows according to a certain criterion.

Filtering data in Excel will allow you to display in the table columns the information that interests the user at a particular moment. It greatly simplifies the process of working with large tables. You will be able to control both the data that will be displayed in the column and the data that is excluded from the list.

If you created a table in Excel through the “Insert” tab – “Table”, or the “Home” tab – “Format as table”, then in such a table the filter is enabled by default. It is displayed as an arrow, which is located in the top cell on the right side.

If you simply filled the cells with data and then formatted them as a table, you need to enable the filter. To do this, select the entire range of table cells, including the column headers, since the filter button is located in the top column, and if you select the column starting with the cell with data, it will not relate to the filtered data of this column. Then go to the Data tab and click the Filter button.

In the example, the filter arrow is in the headers, and this is correct - all data in the column below will be filtered.

If you are interested in the question of how to make a table in Excel, follow the link and read the article on this topic.

Now let's look at how a filter works in Excel. Let's use the following table as an example. It has three columns: “Product Name”, “Category” and “Price”, we will apply various filters to them.

Click the arrow in the top cell of the desired column. Here you will see a list of non-repeating data from all cells located in this column. There will be a check mark next to each value. Uncheck the boxes for the values ​​you want to exclude from the list.

For example, let’s leave only fruits in the “Category”. Uncheck the “vegetable” box and click “OK.”

For those table columns to which a filter is applied, a corresponding icon will appear in the top cell.

If you need to remove a data filter in Excel, click on the filter icon in the cell and select “Remove filter from (column name)” from the menu.

You can make a data filter in Excel in various ways. There are text and number filters. They are applied accordingly if the column cells contain either text or numbers.

Let's apply the “Numeric Filter” to the “Price” column. Click on the button in the top cell and select the corresponding item from the menu. From the drop-down list, you can select the condition that you want to apply to the column data. For example, let’s display all products whose price is below “25”. Select "less".

Enter the required value in the appropriate field. You can apply multiple conditions to filter data using logical AND and OR. When using “AND”, both conditions must be met; when using “OR”, one of the specified conditions must be met. For example, you can set: “less” – “25” – “And” – “more” – “55”. Thus, we will exclude products from the table whose price is in the range from 25 to 55.

Table filtered by the “Price” column below 25.

The “Text Filter” in the example table can be applied to the “Product Name” column. Click on the filter button in the column and select the item of the same name from the menu. In the drop-down list that opens, we use “starts with” as an example.

Let's leave in the table products that begin with "ka". In the next window, in the field we write: “ka*”. Click "OK".

“*” in a word replaces a sequence of characters. For example, if you set the condition “contains” - “s*l”, the words table, chair, falcon, and so on will remain. "?" will replace any sign. For example, “b?ton” – loaf, bud. If you need to leave words consisting of 5 letters, write “?????”.

Filter for the Product Name column.

The filter can be configured by text color or cell color.

Let's make a “Filter by color” cell for the “Product Name” column. Click on the filter button and select the item of the same name from the menu. Let's choose red color.

Only red products remain in the table.

The text color filter applies to the Category column. Let's leave only the fruit. Select red again.

Now the example table displays only red fruits.

If you want all the table cells to be visible, but the red cell comes first, then the green, blue, and so on, use sorting in Excel. By clicking on the link, you can read an article on the topic.

Filters in Excel will help you work with large tables. We have covered the main points on how to make a filter and how to work with it. Select the necessary conditions and leave the data of interest in the table.

The advanced filter in Excel provides greater capabilities for managing spreadsheet data. It is more complex in settings, but much more effective in operation.

Using a standard filter, a Microsoft Excel user can not solve all the tasks assigned. There is no visual display of applied filtering conditions. It is not possible to apply more than two selection criteria. You cannot filter duplicate values ​​to keep only unique entries. And the criteria themselves are schematic and simple. The functionality of the advanced filter is much richer. Let's take a closer look at its capabilities.

How to make an advanced filter in Excel?

The advanced filter allows you to filter data by an unlimited set of conditions. Using the tool, the user can:

1. set more than two selection criteria;
2. copy the filtering result to another sheet;
3. set a condition of any complexity using formulas;
4. extract unique values.

The algorithm for using an advanced filter is simple:

The top table is the result of filtering. The lower plate with the conditions is given side by side for clarity.



How to use the advanced filter in Excel?

To cancel the action of an advanced filter, place the cursor anywhere in the table and press the key combination Ctrl + Shift + L or “Data” - “Sorting and Filter” - “Clear”.

Using the “Advanced Filter” tool, let’s find information on values ​​that contain the word “Set”.

We will add criteria to the conditions table. For example, these:

In this case, the program will search for all information on products whose names contain the word “Set”.

You can use the “=” sign to find the exact value. Let's add the following criteria to the table of conditions:

Excel interprets the “=” sign as a signal: the user will now enter a formula. For the program to work correctly, the formula bar must contain an entry like: ="= Set of region 6 cells."

After using the "Advanced Filter":

Now let’s filter the source table using the “OR” condition for different columns. The “OR” operator is also available in the AutoFilter tool. But there it can be used within one column.

In the conditions table we will enter the selection criteria: ="= Set of region 6th grade." (in the “Name” column) and ="

Please note that the criteria must be written under the appropriate headings on DIFFERENT lines.

Selection result:

The advanced filter allows you to use formulas as criteria. Let's look at an example.

Selection of the row with the maximum debt: =MAX(Table1[Debt]).

Thus, we get the same results as after performing several filters on one Excel sheet.

How to make multiple filters in Excel?

Let's create a filter based on several values. To do this, we enter several data selection criteria into the conditions table:

Let’s use the “Advanced Filter” tool:

Now, from the table with the selected data, we will extract new information selected according to other criteria. For example, only shipments for 2014.

We enter a new criterion into the conditions table and use the filtering tool. The initial range is a table with data selected according to the previous criterion. This is how you filter across multiple columns.

To use multiple filters, you can create multiple condition tables on new sheets. The method of implementation depends on the task set by the user.

How to filter by row in Excel?

Standard methods - no way. Microsoft Excel selects data only in columns. Therefore, we need to look for other solutions.

Here are examples of string criteria for an advanced filter in Excel:

To give an example of how a row filter works in Excel, let’s create a table.

You can display information on one/several parameters using data filtering in Excel.

There are two tools for this purpose: AutoFilter and Advanced Filter. They do not delete, but hide data that does not meet the conditions. Autofilter performs the simplest operations. The advanced filter has much more options.

AutoFilter and Advanced Filter in Excel

I have a simple table that is not formatted or declared as a list. You can enable the automatic filter through the main menu.

If you format the data range as a table or declare it as a list, the automatic filter will be added immediately.

Using an autofilter is simple: you need to select the entry with the desired value. For example, display deliveries to store No. 4. Place a check mark next to the corresponding filtering condition:

We immediately see the result:

Features of the tool:

1. The autofilter only works in a non-breaking range. Different tables on the same sheet are not filtered. Even if they have the same type of data.
2. The tool treats the top line as column headings - these values ​​are not included in the filter.
3. It is permissible to apply several filtering conditions at once. But each previous result may hide the records needed for the next filter.

The advanced filter has much more options:

1. You can set as many filtering conditions as needed.
2. The criteria for selecting data are visible.
3. Using the advanced filter, the user can easily find unique values ​​in a multi-line array.



How to make an advanced filter in Excel

A ready-made example - how to use an advanced filter in Excel:

Only the rows containing the value “Moscow” remained in the original table. To cancel filtering, you need to click the “Clear” button in the “Sort and Filter” section.

How to use the advanced filter in Excel

Let's consider using an advanced filter in Excel to select rows containing the words “Moscow” or “Ryazan”. Filtering conditions must be in the same column. In our example - below each other.

Filling out the advanced filter menu:

We get a table with rows selected according to a given criterion:

Let’s select rows that contain the value “No. 1” in the “Store” column, and “>1,000,000 rubles” in the cost column. The criteria for filtering must be in the appropriate columns of the conditions table. On one line.

Fill in the filtering parameters. Click OK.

Let us leave in the table only those rows that contain the word “Ryazan” in the “Region” column or the value “>10,000,000 rubles” in the “Cost” column. Since the selection criteria belong to different columns, we place them on different lines under the corresponding headings.

Let’s use the “Advanced Filter” tool:

This tool can work with formulas, which allows the user to solve almost any problem when selecting values ​​from arrays.

Basic rules:

1. The result of the formula is the selection criterion.
2. The written formula returns TRUE or FALSE.
3. The initial range is specified using absolute references, and the selection criterion (in the form of a formula) is specified using relative ones.
4. If TRUE is returned, the row will be displayed after the filter is applied. FALSE - no.

Let's display rows containing quantities above average. To do this, aside from the plate with the criteria (in cell I1), enter the name “Largest quantity”. Below is the formula. We use the AVERAGE function.

Select any cell in the source range and call “Advanced Filter”. We indicate I1:I2 as the selection criterion (relative links!).

Only those rows where the values ​​in the “Quantity” column are above average remain in the table.

To leave only non-repeating rows in the table, in the “Advanced filter” window, check the box next to “Only unique records”.

Click OK. Duplicate lines will be hidden. Only unique entries will remain on the sheet.

Filtration is certainly one of the most convenient and quick ways select from the largest 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 in the right 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 by any of possible ways– use the “Shift+Ctrl+L” button combination, click the “Filter” button (the “Main” tab, the large “Sorting and Filter” icon, included in the “Editing” group). 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 select one of possible options actions - filter the list right here or take the received data and copy it to another place 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.

Let's look at how you can search for information in MS Excel using filters.

To do this, open Excel and draw up a small table in it.

Select any cell in the header row, then go to the " Data" and click on the button " Filter»:

In the row with the headings of our table, “arrows” will appear in each column.

Please note that if your table does not have a row with headings, then Excel will automatically insert a filter into the first row with data:

The preparatory stage is completed. You can start searching for information.

Filter Basics

Apply filters to a table

Click the icon in the Manager column. The following menu will open:

IN this menu Using checkboxes you can mark those elements by which you need to filter the data.

If there are many values ​​in the table, then use the search string. To do this, start entering part of the word that you need to find. The list of options will be automatically shortened.

The disadvantage of this method is that you can mark only one value or several values ​​containing the search phrase, but absolutely different meanings can't be found. That is, for example, you won’t be able to immediately find managers named Sergey and Alexander in this way, but you can find all values ​​containing “Serg”: Sergey, Sergeev, Sergienko, etc.

For example, you need to mark only 2 values ​​out of several dozen. Manually unchecking each item except the required ones is quite time consuming. To speed up this process, uncheck the “(Select All)” checkbox. This will clear all other checkboxes. Now you can mark only those items that you need.

MS Excel supports multiple filters, i.e. filter on several columns at once.

For example, you need to find all orders of manager Ivanov dated January 18, 2014.

To begin, click on in the “Manager” column and select Ivanov.

Now click on in the "Shipment Date" column, uncheck "(Select All)" and select 01/18/2014 or enter 18 in the search bar and click "OK".

The table will look like this:

In the same way, you can continue to filter the data by the columns “Description”, “Quantity”, etc.

Note that in columns that have been filtered, the icon changes to .

This way you will always know by which columns the data is filtered.

Cancel filter

To remove all filters at once, go to the " Data" and click on the button " Clear».

If you need to remove the filter from only one column, leaving filters on others, then click on the icon for this column, for example, “Shipment date” and click on the item “Remove filter from<Название столбца>»:

or

If you need to completely abandon filters in the table, then go to the “ Data" and click on the button " Filter" It will stop highlighting, the icons will disappear from the title bar, and all the data will be displayed in the table.

To

After

Additional filter settings

Depending on the content type of the columns, filters have additional options.

Text filters

Click on the “Manager” column icon, hover over “Text filters”, wait for the menu to appear and select any of the selection criteria or the “Custom filter...” item. The following window will appear:

• 1. Terms "equals" or "not equal" assume that the desired expression matches the contents of the cell one hundred percent. Criterion "equals" leaves in the table only those rows that contain the selected value. Accordingly, the criterion "not equal" leaves all values ​​except the selected one. To simplify the task, you can select the desired value from the drop-down list:

• 2. Terms "more" And "less" assume that the table will contain values ​​that begin with an earlier or later letter in the alphabet. For example, if you select the value “Ivanov” for the “more” option, then only those cells that begin with the letter “Y” will remain in the table (Kartov, Yoghurtov, etc.). And with the “less” option - values ​​starting with the letter “Z” (Zakharov, Bukin).
• 3. The only difference in conditions "greater than or equal to" And "less than or equal to" from the previous paragraph in that the filter includes the selected value.
• 4. If you need to find all values ​​that begin with "Willow", then use the condition "begins with", and if you want to know how many values ​​in the table ending in "rovich", then select the option "ends with".
• 5. Accordingly, the conditions "doesn't start with" And "doesn't end with" assume that you do not need to display values ​​containing the search phrase in the table.
• 6. When choosing conditions "contains" or "does not contain" You can specify any phrase or combination of letters that you want to include or exclude from the filter. The difference between this point and points 1, 4 and 5 is that the searched phrase can be located anywhere in the cell. For example, setting “Willow” as a filter, the result will be “Alexey Ivanov”, “Sergey Ivarovsky”, “curve”, etc.

Numeric filters

Most of the conditions are the same as for text filters. Let's consider only new ones.

• 1. Condition "between". When you select this condition, the necessary criteria are immediately set in the window that appears, which makes your task easier:

All you have to do is enter the values ​​from the keyboard or select them from the list.

Filter by date

These conditions do not require special explanations, since their meaning is easy to understand from the names. The only thing worth paying attention to is that in the standard window for selecting filter conditions, a Calendar button appears to make it easier to enter the date.

And a little more about filters

There is another way to filter data. Let's transform our table a little:

As you can see, we colored it.

Now, for example, we need to find all the lines with Krasotkin. Right-click on the cell with this person and select “Filter” from the menu that appears. The new menu has several new options. In this example we need the item "Filter by value...".

If you select the condition "Filter by color...", then the table will contain rows with cells of the same color as the active cell (yellow fill).

If you click on “Filter by font color...”, then only cells with red or black font will remain in our table, depending on which cell is currently active.

The last filter item is only applicable if the table uses conditional formatting with icons.

Site administration website expresses gratitude to the author Alexander Tsarev for preparing the material.