Filtering data in Excel. Advanced filter in Excel and examples of its capabilities

Filtering data in Excel will allow you to display 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 what is excluded from the list.

How to add

If you compiled information through the “Insert” tab - “Table”, or the “Home” tab - "Format as table", then the filtering option will be enabled by default. The required button is displayed in the form of an arrow, which is located in the top cell on the right side.

If you simply filled the blocks with data and then formatted them as a table, you need to enable the filter. To do this, select the entire range of cells, including the row with headings, since the button we need will be added to the top row. But if you select blocks starting from the cell with data, then the first row will not relate to the filtered information. Then go to the Data tab and click the Filter button.

In the example, the button with an arrow is in the headers, and this is correct - all data located 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.

How does it work

Now let's look at how a filter works in Excel. For example, let's use the following data. We have three columns: "The product's 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.

How to delete

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

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

Using a filter

Numeric

Apply “Numeric...” 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 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 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 whose price is in the range from 25 to 55.

In the example I did it like this. All data with a “Price” below 25 are displayed here.

Text

"Text filter" in the example table, can be applied to the column "The product's name". Click on the button with the arrow at the top and select the item of the same name from the menu. In the drop-down list that opens, for example, use “starts with”.

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 will remain: table, chair, falcon, and so on. "?"

will replace any sign. For example, “b?ton” - loaf, bud, concrete. If you need to leave words consisting of 5 letters, write “?????” . This is how I left the ones I needed.

"Product Names"

By cell color

The filter can be configured by text color or cell color. Let's do it"Filter by color" "The product's name" column cells

. Click on the arrow button and select the item of the same name from the menu. Let's choose red color.

As a result, only red products remained, or rather all the cells that were filled with the selected color.

By text color

Now in the example used, only red fruits are displayed.

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

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.

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 “ Select any cell in the header row, then go to the "" 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 “ Select any cell in the header row, then go to the " Data " and click on the button "" 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 retain 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, 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.

Excel data filtering includes two filters: AutoFilter and Advanced Filter. Suppose you have a large data set, but from the entire array you need to look at or select data that relates to a specific date, a specific person, etc. There are filters for this. For those who are encountering this tool for the first time, the filter does not delete, but hides records that do not meet the filtering conditions that you set for them.

The first is an auto filter, designed for the most simple operations - highlighting records with a specific value (for example, only selecting only records related to LeBron James), data lying in a certain range (or above average or top ten) or cells/fonts of a certain color ( By the way, it’s very convenient). Accordingly, it is very easy to use. You just need to select the data that you want to see filtered. Then the command “Data” / “Filter”. A list box will appear on each top cell of the top table, it’s already easy to understand each command, it’s simple to master and I hope there’s no need to explain further, just the nuances of using the autofilter:

1) Works only with a non-breaking range. It is no longer possible to filter two different lists on one sheet.

2) The top line of the table is automatically assigned as a title and is not involved in filtering.

3) You can apply any filters in different columns, but keep in mind that depending on the order in which the filters are applied, some conditions may not be applied, because previous filters have already hidden the required entries. There is no problem here, these entries would have been hidden anyway, but if you want to use several sets of filters, then it is better to start with those conditions that have the least application.

Practical application at work: for example, you work through this list to find an error or check data. After applying the autofilter, you can go through the entire table one by one, sequentially marking the data that has already been viewed. The “Clear” and “Reapply” buttons determine the appearance of the table after applying the conditions. Then, after finishing working with the table, you can return the fonts back to their original form without changing the data itself. By the way, some people are confused by the fact that all records in the table disappear after applying any conditions. Well, take a closer look, you have set conditions under which there are no records that satisfy these conditions. The fact that the table is filtered is when the table row numbers are highlighted in blue.

Now, let's move on to the advanced filter. It differs from the autofilter in that it has more fine-grained settings, but also a larger selection when filtering data. In particular:

1) Sets as many conditions as necessary.

2) Allows you to select cells with unique (non-repeating) data. This is often needed when working with data and the option copes with the problem perfectly.

3) Allows you to copy the filter result to a separate location without touching the main array.

So, the main difference in working with this filter is that we first need to prepare a table of conditions. It's easy to do. The headers of the main table are copied and pasted into a place convenient for us (I suggest above the main table). There should be so many rows in this table that after defining the conditions you won’t be able to get into the main table.

Examples of conditions:

1) ‘L*’ – cells starting with L

2) ‘>5’ - data greater than 5

If you delete rows from a filtered table, they will be deleted without taking their neighbors with them. Those. if the table is filtered and shows rows 26-29 and 31-25, selecting all rows and deleting them will not result in deleting row 30. This is convenient; I personally often use this when writing macros. What advantage does this give - often we get tables that need to be brought into working form, i.e. remove, for example, empty lines. What we do is apply a filter to the table, showing only those rows that we don't need, then delete the entire table, including the header. Unnecessary rows and header are removed, while the table has no spaces and forms a single range. And a header line can be added by simple copying operations from a previously prepared area. Why is this important when writing macros? It is not known from which row the unwanted data begins and it is not clear from which row to start deleting it; deleting the entire table helps to quickly solve this problem.

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.