Microsoft Excel: sorting and filtering data. Filtering data in Excel

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 the checkboxes, you can mark those elements by which you want 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 completely different values ​​cannot 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 ones you need 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.

Before

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 "more or equal" And "less or equal" 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 "begin 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, by 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.

Many employees of all kinds of organizations who have to work with Mircosot Excel in some way, be it ordinary accountants or analysts, are often faced with the need to select a number of values ​​from a huge amount of data. To simplify this task, a filtration system was created.

Regular and advanced filter

Excel provides a simple filter that can be launched from the “Data” tab – “Filter” (Data – Filter in the English version of the program) or using a shortcut on the toolbar, similar to a cone-shaped funnel for pouring liquid into containers with a narrow neck.

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

Using the advanced filter for the first time

In Excel, most of the work is done with tables. Firstly, it is convenient, and secondly, in one file you can save information on several pages (tabs). It is advisable to create several rows above the main table, leaving the top one for the header; it is in these rows that the conditions of the Excel advanced filter will fit. In the future, the filter will probably 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 row between the condition range cells and the source data cells.

Below are examples of how to use an advanced filter in Excel.

Applying a filter

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

First, you should 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).

The window that opens will display the range of selected cells in the Source Range field. According to the example above, the string takes the value "$A$4:$F$12".

The "Condition Range" 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 space 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, and 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 ​​from rows 4 and 5 will be displayed in the user-specified range. The original range will remain unchanged.

Ease of use

The described method is not entirely convenient, therefore, for improvement, the VBA programming language is usually used, with which macros are created that allow you to automate an 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 your plans. When you change the values ​​of the cells in 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 precisely specified values, the advanced filter in Excel is capable of processing complex queries. These are the entered data, where some of the characters are replaced by wildcard characters.

The symbol table for complex queries is shown below.

It's worth knowing that the * symbol can represent any number of characters. That is, when the value “n*” is entered, all values ​​will be returned, regardless of the number of characters after the letter “n”.

Sign "?" implies only one character.

Links OR and AND

You should be aware that information specified in one line in the “Range of Conditions” is considered to be combined with a logical operator (AND). This means that several conditions are met simultaneously.

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

The table of values ​​will take the following form:

Pivot tables

Another method is carried out using the command “Insert - Table - PivotTable” (Insert - Table - PivotTable in the English version).

The mentioned tables work similarly with a pre-selected data range and select unique values ​​for further analysis. In fact, it looks like working with unique fields (for example, the last name of a company employee) and a range of values ​​that are returned when a unique field is selected.

The disadvantage 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 use your imagination and develop your own knowledge, skills and abilities.

The filter itself is easy to use and learn; it’s not difficult to figure out how to use the advanced filter in Excel, but it is intended for cases when it is necessary to sift out information a small number of times for further processing. As a rule, it does not involve working with large amounts of information due to the usual human factor. This is where more sophisticated and advanced technologies for processing information in Microsoft Excel come to the rescue.

Macros written in VBA are extremely 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 is faced with the need for application will always find, if desired, enough material to develop their knowledge and search for answers to questions of interest.

Filter in Excel - This is the selection of data according to certain characteristics.Filtering in Excelunder certain conditions is carried out using the function " Filter in Excel ". If there are pictures in the cells of the table, then see the article "Insert a picture into a cell in Excel" on how to fix pictures in a cell so that they do not move during filtering.
How to filter in Excel.
First, select the column in which you need to filter the table data. Then, on the “Home” tab, click the “Sort and Filter” button and select the “Filter” function. Or go to the “Data” tab and click the “Filter” button.
A filter button has appeared in the top cell of the selected column. Click on this button and select “Numeric filters”, then “less”. In the window that appears, opposite the “less than” cell, write the number less than which we need to select data, for example, 7. Click “OK”.

You can filter data by cell color. Cells can be colored manually or with conditional formatting. How to color cells with conditional formatting by number, by words, by date, etc., "Conditional Formatting in Excel."
Click on the pink color. Click "OK". It turned out like this.

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 2010, 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 2010, 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 speak 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 job.

Microsoft Excel is a ubiquitous and convenient spreadsheet tool.

Wide functionality makes 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.

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

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 INSTRUCTION

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 itself for selection – 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 necessary information will be selected, 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 simplest 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.

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

Example 1

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.

Example 2

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 in “Sorting” - “Add level” 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 INSTRUCTION

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 you try to filter rows by a column containing dates, the filtering occurs not by date, but by text.

Solution to the problem:

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.