How to make a filter in Excel by rows. How to make a filter on each column in excel? Where are filters in Excel - their types

The advanced filter in Excel gives you more control over your spreadsheet data. It is more complex in settings, but much more effective in action.

With the help of a standard filter, a user of Microsoft Excel can solve far from all the tasks. There is no visual display of applied filtering conditions. It is not possible to apply more than two selection criteria. You cannot filter for duplicate values to leave only unique entries. And the criteria themselves are schematic and simple. The advanced filter functionality 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. With the tool, the user can:

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

The algorithm for applying an advanced filter is simple:

The top table is the result of filtering. The bottom plate with the conditions is given for clarity next to it.

How to use the advanced filter in Excel?

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

Using the "Advanced Filter" tool, let's find information on the values that contain the word "Set".

Let's add the criteria to the condition table. For example, these:

The program in this case will search for all information on products that have the word "Set" in their names.

You can use the "=" sign to search for an exact value. Let's add the following criteria to the condition table:

Excel takes the "=" sign as a signal that the user is now entering a formula. For the program to work correctly, the formula bar must contain an entry of the form: ="=Set of region 6 cells."

After using the "Advanced Filter":

Now let's filter the source table by the "OR" condition for different columns. The "OR" operator is also in the "AutoFilter" tool. But there it can be used within a single column.

In the table of conditions, we will enter the selection criteria: ="=Set of region 6 cells." (in the "Name" column) and ="

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

Selection result:

The advanced filter allows you to use formulas as a criterion. Consider an example.

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

Thus, we get the results as after running multiple filters on the same Excel sheet.

How to make multiple filters in Excel?

Let's create a filter by several values. To do this, we introduce several data selection criteria into the conditions table at once:

Apply 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 in the table of conditions and apply the filtering tool. The initial range is a table with data selected according to the previous criterion. This is how you filter on 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 in Excel by rows?

Not in the standard way. Microsoft Excel selects data in columns only. Therefore, other solutions must be sought.

Here are examples of advanced filter string criteria in Excel:

To give an example of how the filter by rows works in Excel, let's create a table.

Sometimes the data entered into an Excel document can have thousands of lines, and it is rather difficult to find the necessary lines among them. But if the data you are looking for satisfies any specific criteria, thanks to which you can weed out unnecessary information, then you need to use a filter in Excel.

All tables in Excel created through the menu "Table" tab "Insert" or that have been formatted like a table and already have a built-in filter.

If the data in the form of a table does not have a filter, then it must be installed. To do this, select the table header or simply select a cell inside the table, go to the tab "Data" and choose from the menu "Filter". The same menu item can be found on the tab "Home" on the menu "Sort and Filter". After connecting the filter in the header of the table on the right side of the heading of each column, the corresponding sign appears, a square with an inverted triangle.

The simplest use of a filter is to hide all values in a list, except those that are selected. Just click on the filter icon and leave a checkmark in the list of values opposite the one you need. After the filter is applied, the icon in the filtered column also changes, which allows you to visually determine whether the filter has been applied to the data. To remove a filter, click on the filter icon and select "Remove filter from column...".

You can also configure more complex filters. Our first column has only text data, so when you click on the filter icon, this column has an active item "Text Filters" where you can select some conditions. In the case of numerical data, as in the second column, the item will be available "Numeric Filters".

When you select any custom option, a custom filter window opens, where you can select two conditions at once with a combination of "AND" and "OR".

It is also possible to set a filter by text color and cell color. In this case, only the text and cell colors applied in this column will be displayed in the list.

In the latest versions of Excel, it is possible for tables to create slices of data. To do this, activate any cell in the table and go to the tab that appears "Constructor", and select the menu "Insert Slice". A window appears in which you need to select the column to which the data slice will be applied. After selecting a column, a floating window with controls appears, in which all slicer items will be listed. By choosing any value, we will get the corresponding data slice.

Many employees of various organizations who have to work with Mircosot Excel in any way, whether they are ordinary accountants or analysts, are often faced with the need to select a number of values from a huge array of data. To simplify this task, a filtering system was created.

Regular and advanced filter

Excel presents the simplest filter, which is launched from the tab "Data" - "Filter" (Data - Filter in the English version of the program) or using a shortcut on the toolbar that looks like a cone-shaped funnel for pouring liquid into containers with a narrow neck.

For most cases, this filter is the best option. But, if it is necessary to filter by a large number of conditions (and even by several columns, rows and cells), many are wondering how to make an advanced filter in Excel. In the English version it is called Advanced filter.

First use of advanced filter

In Excel, most of the work is done with tables. Firstly, it is convenient, and secondly, you can save information on several pages (tabs) in one file. Above the main table, it is desirable to create several lines, the topmost of which is left for the header, it is in these lines that the conditions of the advanced Excel filter will fit. In the future, the filter will certainly be changed: if more conditions are needed, you can always insert another line in the right place. But it is desirable that there be one unused line between the cells of the range of conditions and the cells of the source data.

How to use the advanced filter in Excel, examples, consider below.

	A	B	C	D	E	F
1	Products	Name	Month	Day of the week	City	Customer
2	vegetables				Krasnodar	"Auchan"
3
4	Products	Name	Month	Day of the week	City	Customer
5	fruits	peach	January	Monday	Moscow	"Pyaterochka"
6	vegetables	tomato	February	Monday	Krasnodar	"Auchan"
7	vegetables	cucumber	March	Monday	Rostov-on-Don	"Magnet"
8	vegetables	eggplant	April	Monday	Kazan	"Magnet"
9	vegetables	beet	May	Wednesday	Novorossiysk	"Magnet"
10	fruits	Apple	June	Thursday	Krasnodar	"Bacal"
11	greenery	dill	July	Thursday	Krasnodar	"Pyaterochka"
12	greenery	parsley	August	Friday	Krasnodar	"Auchan"

Applying a filter

In the above table, lines 1 and 2 are for the range of conditions, lines 4 to 7 are for the range of initial data.

First you need to enter in line 2 the appropriate values, from which the advanced filter in Excel will be based.

The filter is launched by selecting the cells of the source data, after which you need to select the "Data" tab and click the "Advanced" button (Data - Advanced, respectively).

In the window that opens, the range of selected cells will be displayed in the "Source range" field. According to the given example, the string takes the value "$A$4:$F$12".

The "Range of conditions" field should be filled with the values "$A$1:$F$2".

The window also contains two conditions:

filter the list in place;
copy the result to another location.

The first condition allows you to generate the result in the place allocated for the cells of the original range. The second condition allows you to generate a list of results in a separate range, which should be specified in the "Place result in range" field. The user selects a convenient option, for example, the first one, the "Advanced Filter" window in Excel closes.

Based on the entered data, the filter will generate the following table.

When using the "Copy result to another location" condition, the values of 4 and 5 rows will be displayed in the range specified by the user. The original range will remain unchanged.

The convenience of use

The described method is not entirely convenient, therefore, for improvement, the VBA programming language is usually used, with the help of which macros are compiled to automate the advanced filter in Excel.

If the user has knowledge of VBA, it is recommended to study a number of articles on this topic and successfully implement their plans. When you change the values of the cells of row 2, reserved for the Excel advanced filter, the range of conditions will change, the settings will be reset, immediately restarted, and the necessary information will be generated in the required range.

Complex Queries

In addition to working with exact values, the advanced filter in Excel can handle complex queries as well. These are the entered data, where some of the characters are replaced by wildcards.

The symbol table for complex queries is shown below.

	Request example	Result
1	P*	returns all words starting with the letter P: peach, tomato, parsley (if entered in cell B2); Pyaterochka (if you enter it in cell F2).
2	=	the result will be to display all empty cells, if any, within the given range. It can be very useful to use this command to edit the source data, because tables can change over time, the contents of some cells can be deleted as unnecessary or irrelevant. The use of this command will allow you to identify empty cells for their subsequent filling or restructuring of the table.
3	<>	all non-empty cells will be displayed.
4	yu	all values where there is a letter combination "yu": June, July.
5	=?????	all cells in a column that have four characters. Characters are considered to be letters, numbers and spaces.

It is worth knowing that the * symbol can mean any number of characters. That is, with the entered value "n*", all values will be returned, regardless of the number of characters after the letter "n".

Sign "?" means only one character.

Links OR and AND

You should be aware that the information specified by one line in the "Range of conditions" is considered to be written in a bunch of logical operator (AND). This means that several conditions are met at the same time.

If the data is written in one column, the advanced filter in Excel recognizes them as a connected logical operator (OR).

The table of values will take the following form:

	A	B	C	D	E	F
1	Products	Name	Month	Day of the week	City	Customer
2	fruits
3	vegetables
4
5	Products	Name	Month	Day of the week	City	Customer
6	fruits	peach	January	Monday	Moscow	"Pyaterochka"
7	vegetables	tomato	February	Monday	Krasnodar	"Auchan"
8	vegetables	cucumber	March	Monday	Rostov-on-Don	"Magnet"
9	vegetables	eggplant	April	Monday	Kazan	"Magnet"
10	vegetables	beet	May	Wednesday	Novorossiysk	"Magnet"
11	fruits	Apple	June	Thursday	Krasnodar	"Bacal"

Pivot tables

Another way is by using the command "Insert - Table - PivotTable" (Insert - Table - PivotTable in the English version).

The mentioned tables similarly work with a pre-selected range of data and select unique values in order to further analyze them. In fact, it looks like working with unique fields (for example, the names of an employee of a company) and a range of values that are issued when a unique field is selected.

The inconvenience of using pivot tables is the need to manually adjust the source data when they change.

Conclusion

In conclusion, it should be noted that the scope of filters in Microsoft Excel is very wide and varied. It is enough to apply imagination and develop your own knowledge, skills and abilities.

The filter itself is easy to use and learn, it is easy to figure out how to use the advanced filter in Excel, but it is designed for cases where you need to screen out information for further processing a small number of times. As a rule, it does not provide for working with large amounts of information due to the usual human factor. Here, more thoughtful and advanced technologies for processing information in Microsoft Excel come to the rescue.

Macros written in VBA are very popular. They allow you to run a significant number of filters that help select values and display them in the appropriate ranges.

Macros successfully replace many hours of work on compiling summary, periodic and other reports, replacing the long time of analyzing huge arrays with just a one-second click.

The use of macros is justified and inconvenient. Anyone who has faced the need for application will always find, if desired, enough material to develop their knowledge and find answers to their questions.

Many PC users are familiar with the Microsoft Office suite of products for working with various kinds of documents. Among the programs of this company is MS Excel. This utility is designed to work with spreadsheets.

The program has a so-called advanced filter in Excel. It is designed for ease of use and creation of tables. It will be discussed in our article. We'll show you how to make it and how to use it.

What is this function? Description

What does advanced filter mean in Excel? This is a function that allows you to differentiate the selected data (by columns in Excel) relative to the entered requirements.

For example, if we have a spreadsheet with information about all the students in the school (height, weight, grade, gender, etc.), then we can easily select among them, say, all boys with a height of 160 out of 8 class. You can do this using the "Advanced Filter" feature in Excel. We will talk about it in detail later.

What does autofilter mean?

What are the features of a regular and advanced filter? Let's talk about the first one first. Autofilter (normal) is designed to distinguish between data according to previously known criteria, the number of which is determined. It is used when there is a spreadsheet of small size, by columns and given parameters. If we need to set an unlimited number of criteria for filtering, we must select advanced.

How to do it right?

How to make an advanced filter in Excel? To make it clear how the procedure occurs and how it is done, consider an example.

Instructions for advanced spreadsheet filtering:

You need to create a place above the main table. This is where the filtering results will be placed. There must be enough space for the finished table. It also requires one more line. It will separate the filtered table from the main one.
In the very first line of the freed space, copy the entire header (column names) of the main table.
Enter the required data for filtering in the desired column. Note that the entry should look like this: = "= filtered value".
Now you need to go to the "Data" section. In the filtering area (funnel-shaped icon), select "Advanced" (located at the end of the right list from the corresponding sign).
Next, in the pop-up window, you need to enter the parameters of the advanced filter in Excel. "Range of conditions" and "Initial range" are filled in automatically if the cell of the beginning of the worksheet has been selected. Otherwise, you will have to enter them yourself.
Click on Ok. You will exit the settings of advanced filtering parameters.

After the steps taken, the main table will contain only records for the given delimiting value. To cancel the last action (filtering), you need to click on the "Clear" button, which is located in the "Data" section.

Working with advanced filters in Excel

How to use the advanced filter in Excel? In order to understand, let's look at this function again. Suppose we have a column with cities in a spreadsheet. Before that, we have already done advanced filtering for the city of Rostov. Now, in the same document, we want to add to those received for the city of Rostov those records that correspond to the city of Samara.

For this you need:

Place delimitation conditions (="-Samara") under the previous query (="=Rostov").
Call the advanced filter menu (the "Data" section, the "Filtering and sorting" tab, select "Advanced" in it).
Press OK. This will close advanced filtering in Excel. And a ready-made table will appear on the screen, consisting of records in which the city of Samara or Rostov is indicated.

When working, the following very important point should be taken into account. All entered criteria should be only in those columns by which the filtering is performed. Above the main table.

After you have studied the basic advanced filtering method, you can start exploring all the possibilities of this function. It means the use of formulas.

Advanced filtering. Basic rules for using Excel

Selection criteria are the results of the original formula.
The result can only be two values: "TRUE" or "FALSE".
Absolute references specify the source range of the filtered table.
The formula results will only show rows that total to TRUE. Row values that are "FALSE" as a result of the formula will not be displayed.

Using these rules, you can create any formulas with valid operands and values.

Example in Excel 2010

Let's look at an example of an advanced filter in Excel 2010 and its use of formulas. For example, let's delimit the values of some column with numerical data by the result of the average value (more or less).

Instructions for working with an advanced filter in Excel by the average value of a column:

The first step is to select a formula for the average of the column data. To do this, you need to select a range of results from the start record to the end record. Sum them and divide by the number of rows (number of records).
Then select "Advanced" in the "Filtering and sorting" section. Enter the necessary data there (by ranges).
After that click OK. The result is a table that has records that do not exceed the average value for the given column.

Thus, you can use advanced filtering in quite a variety of ways. This can be done by resorting to the compilation of various kinds of formulas.

The main thing is not to forget to enter the filtering criteria above the main table in the column by which the distinction will be made.

Attention! During operation, records that have not passed the filtering criteria are not deleted. They just don't show up (don't show up). Advanced filtering is designed to be able to copy the result and paste it somewhere else. After that, you need to click on "Clear" in the "Filtering and sorting" section. Then your original table will return to the screen.

Autofilter. Usage example

Autofilter is a common tool. It can be applied only by setting the exact parameters. For example, print all table values that are greater than 1000 (< 1000), или показать точные данные, как было рассмотрено в примере с городами.

It is used much more often than the extended one. However, the filtering options offered by the autofilter are often not enough.

Pros and cons of the advanced filter in Excel

Since advanced filtering is an addition to regular filtering, its use has its advantages and disadvantages. We will now consider them.

Advantages of advanced filtering:

formulas can be used.

Cons of advanced filtering:

to do this operation, you need to spend more time on the proceedings with the introduction of the range and the original data;
to use advanced filtering using formulas, you need to know and be able to use the rules of logic and formulating MS Excel equations.

Despite the presence of minuses, this function still has more possibilities than just autofiltering. Although with the latter there is no need to enter something of your own, except for the criterion by which the values will be identified.

Filtering by two separate criteria. How to do it right?

Advanced filtering allows you to use different formulas in an existing spreadsheet. If you need to distinguish it first by one criterion, then by another (separately for each other), you must:

Create a place to enter a filter parameter. It is most convenient to leave this space above the main table and do not forget to copy the header (column names) so as not to get confused in which column to enter this criterion.
Enter the desired indicator for filtering. For example, all records whose column values are greater than 1000 (> 1000).
Go to the "Data" tab. In the "Filtering and sorting" section, select "Advanced".
In the window that opens, specify the ranges of the considered values and the cell with the value of the considered criterion.
Click on Ok. After that, a table filtered by the specified criterion will be displayed.
Copy the result of the delimiter. Paste the filtered table somewhere to the side on the same Excel sheet. You can use another page.
Select "Clear". This button is located in the "Data" tab in the "Filtering and sorting" section. After pressing it, the filtered table will return to its original form. And you can work with her.
Next, you need to allocate free space again for the table that will be filtered.
Then you need to copy the header (column names) of the main field and move them to the first line of the space freed for the filtered structure.
Go to the "Data" tab. In the "Filtering and sorting" section, select "Advanced".
In the window that opens, select the range of records (columns) to filter by.
Add the address of the cell in which the delimitation criterion is written, for example, "city of Odessa".
Click on Ok. After that, filtering by the value "Odessa" will occur.
Copy the filtered table and paste it either on another sheet of the document, or on the same page, but away from the main one.
Click on "Clear" again. Everything is ready. You now have three tables. The main one, filtered by one value (>1000), as well as the one filtered by another value (Odessa).

A small conclusion

Despite the fact that at first glance this function seems complicated, in fact, everything is quite simple. All you need to know is the range of advanced filter conditions in Excel. However, the following should not be forgotten. Don't confuse regular and advanced filters. It is also recommended to learn the rules of logic and Excel formulas, then working with the filter becomes easy.

For the convenience of working with a large array of data in tables, they constantly need to be sorted according to a certain criterion. In addition, for specific purposes, sometimes the entire array of data is not needed, but only individual lines. Therefore, in order not to get confused in a huge amount of information, a rational decision would be to arrange the data and filter from other results. Let's find out how data is sorted and filtered in Microsoft Excel.

Sorting is one of the most convenient tools when working in Microsoft Excel. With it, you can arrange the lines of the table in alphabetical order, according to the data that is in the cells of the columns.

Sorting data in Microsoft Excel can be performed using the "Sort and Filter" button, which is located on the "Home" tab on the ribbon in the "Editing" toolbox. But, first, we need to click on any cell of the column by which we are going to sort.

For example, in the table below, sort employees alphabetically. We become in any cell of the "Name" column, and click on the "Sort and Filter" button. To sort the names alphabetically, select "Sort A to Z" from the list that appears.

As you can see, all the data in the table is placed according to the alphabetical list of surnames.

In order to sort in reverse order, in the same menu, select the Sort from Z to A button.

The list is rebuilt in reverse order.

It should be noted that this type of sorting is indicated only for text data format. For example, a number format specifies sorting from "Least to Largest" (and vice versa), while a date format specifies "Oldest to Newest" (and vice versa).

Custom sorting

But, as you can see, with the specified types of sorting by one value, data containing the names of the same person are arranged inside the range in an arbitrary order.

But what if we want to sort the names alphabetically, but for example, if the name matches, make sure that the data is sorted by date? To do this, as well as to use some other features, all in the same "Sort and Filter" menu, we need to go to the "Custom sorting ..." item.

After that, the sort settings window opens. If your table has headers, then pay attention that in this window there must be a checkmark next to the "My data contains headers" parameter.

In the Column field, enter the name of the column by which the sort will be performed. In our case, this is the "Name" column. The "Sort" field indicates which type of content will be sorted by. There are four options:

Values;
cell color;
Font color;
Cell icon.

But, in the vast majority of cases, the "Values" item is used. It is set by default. In our case, we will also use this item.

In the “Order” column, we need to indicate in what order the data will be located: “From A to Z” or vice versa. Select the value "From A to Z".

So, we set up sorting by one of the columns. In order to configure sorting by another column, click on the "Add Level" button.

Another set of fields appears, which should be filled in for sorting by another column. In our case, by the "Date" column. Since the date format is set in these cells, in the “Order” field we set the values not “From A to Z”, but “Old to New”, or “New to Old”.

In the same way, in this window, you can configure, if necessary, sorting by other columns in order of priority. When all the settings are done, click on the "OK" button.

As you can see, now in our table all the data is sorted, first of all, by the names of the employee, and then, by the dates of payments.

But, this is not all the possibilities of custom sorting. If desired, in this window you can configure sorting not by columns, but by rows. To do this, click on the "Settings" button.

In the sorting options window that opens, move the switch from the “Rows of Range” position to the “Range Columns” position. Click on the "OK" button.

Now, by analogy with the previous example, you can enter data for sorting. Enter the data and click on the "OK" button.

As you can see, after that, the columns are swapped, according to the entered parameters.

Of course, for our example table, the use of sorting by changing the location of the columns is not particularly useful, but for some other tables this kind of sorting can be very appropriate.

Filter

In addition, Microsoft Excel has a data filter function. It allows you to leave visible only the data that you deem necessary, and hide the rest. If necessary, hidden data can always be returned to visible mode.

To use this function, we stand on any cell in the table (and preferably in the header), again click on the "Sort and Filter" button in the "Editing" tool block. But, this time, in the menu that appears, select the "Filter" item. You can also just press Ctrl+Shift+L instead of these actions.

As you can see, in the cells with the names of all columns, an icon appeared in the form of a square, in which a triangle inverted down is inscribed.

We click on this icon in the column, according to which we are going to filter. In our case, we decided to filter by name. For example, we need to leave the data of only an employee of Nikolaev. Therefore, we uncheck the names of all other employees.

When the procedure is completed, click on the "OK" button.

As you can see, only the rows with the name of the worker Nikolaev remained in the table.

Let's complicate the task, and leave in the table only the data that relate to Nikolaev for the III quarter of 2016. To do this, click on the icon in the "Date" cell. In the list that opens, uncheck the months "May", "June" and "October", since they do not belong to the third quarter, and click on the "OK" button.

As you can see, only the data we need remains.

In order to remove the filter for a specific column and show hidden data, again click on the icon located in the cell with the title of this column. In the menu that opens, click on the item "Remove filter from ...".

If you want to reset the filter for the whole table, then you need to click the "Sort and Filter" button on the ribbon, and select the "Clear" item.

If you need to completely remove the filter, then, as when you started it, select the "Filter" item in the same menu, or type the keyboard shortcut Ctrl + Shift + L.

In addition, it should be noted that after we have enabled the “Filter” function, when you click on the corresponding icon in the cells of the table header, the sorting functions that we talked about above become available in the menu that appears: “Sort from A to Z” , Sort Z to A, and Sort by Color.

smart table

Sorting and filtering can also be activated, turning the data area you are working with into a so-called "smart table".

There are two ways to create a smart table. In order to use the first of them, select the entire area of the table, and, being in the "Home" tab, click on the button on the "Format as Table" ribbon. This button is located in the "Styles" toolbox.

After that, a dialog box opens in which you can change the coordinates of the table. But, if you previously selected the area correctly, then nothing else needs to be done. The main thing to note is that there is a checkmark next to the “Table with Headers” parameter. Next, just click on the "OK" button.

If you decide to use the second method, then you also need to select the entire area of \u200b\u200bthe table, but this time go to the "Insert" tab. Being here, on the ribbon in the "Tables" tool block, you should click on the "Table" button.

After that, like last time, a window will open where you can adjust the coordinates of the table placement. Click on the "OK" button.

Regardless of which method you use when creating a “smart table”, you will end up with a table whose header cells will already have the filter icons we described earlier.

When you click on this icon, all the same functions will be available as when you start the filter in the standard way through the "Sort and Filter" button.

As you can see, sorting and filtering tools, when used correctly, can make it much easier for users to work with tables. The issue of their use becomes especially relevant if a very large array of data is recorded in the table.