How to filter in excel by columns. Advanced filter in Excel and examples of its capabilities
Probably all users who constantly work with Microsoft Excel know about such a useful function of this program as data filtering. But not everyone is aware that this tool also has advanced capabilities. Let's look at what the Microsoft Excel advanced filter can do and how to use it.
It is not enough to immediately launch the advanced filter; for this, one more condition must be met. Next, we will talk about the sequence of actions that should be taken.
Step 1: Create a table with selection conditions
To install an advanced filter, you first need to create an additional table with selection conditions. Its header is exactly the same as the main one, which we, in fact, will filter. For example, we placed an additional table above the main one and colored its cells orange. Although you can place it in any free space and even on another sheet.
Now we enter into the additional table the information that will need to be filtered from the main table. In our specific case, from the list of wages issued to employees, we decided to select data on the main male personnel for 07/25/2016.
Step 2: Run the advanced filter
Only after the additional table has been created can you proceed to run the advanced filter.
Thus, we can conclude that the advanced filter provides more options than regular data filtering. But it should be noted that working with this tool is still less convenient than with a standard filter.
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 prepared 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 numeric filters. They are applied accordingly if the column cells contain either text or numbers.
Using a filter
Numerical
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.
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.
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.
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:
- 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 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, we will 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.
Sometimes the data entered into an Excel document can consist of thousands of lines, and it is quite difficult to find the necessary lines among them. But if the data you are looking for meets any certain criteria, thanks to which you can filter out unnecessary information, then you need to use a filter in Excel.
For all tables in Excel created through the menu "Table" on the tab "Insert" or to which formatting has been applied, like a table, already has a built-in filter.
If the data, formatted as 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 and go to the tab "Data" and select from the menu "Filter". The same menu item can be found on the tab "Home" on the menu "Sorting and Filter". After connecting the filter, a corresponding sign appears in the table header on the right side of the title of each column, a square with an inverted triangle.
The simplest use of a filter is to hide all values in the list except the selected ones. Just click on the filter icon and leave a checkmark in the list of values opposite the required one. After applying a filter, the icon in the filtered column also changes, which allows you to visually determine whether the filter is applied to the data. To remove a filter, click on the filter icon and select "Remove filter from column...".
You can also customize more complex filters. Our first column only has text data, so when we click on the filter icon, there is an active item in this column "Text filters", where you can select some conditions. In the case of numeric data, as in the second column, the option will be available "Numerical filters".
When you select any custom option, a custom filter window opens, where you can select two conditions at once with a combination "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 data slices. To do this, activate any table cell and go to the tab that appears "Constructor", and select the menu "Insert Slice". A window appears in which you must select the column to which the data slice will be applied. After selecting a column, a floating window with controls appears, in which all the slice items will be listed. By choosing any value we will receive the corresponding data slice.