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:
- 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.
- The tool treats the top line as column headings - these values are not included in the filter.
- 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 capabilities:
- You can set as many filtering conditions as needed.
- The criteria for selecting data are visible.
- 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:
- The result of the formula is the selection criterion.
- The written formula returns TRUE or FALSE.
- The source range is specified using absolute references, and the selection criterion (in the form of a formula) is specified using relative ones.
- 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.
Sometimes tables can contain quite a large amount of data, and this data will often be presented in the form of a list. In this case, tools such as sorting lists and filtering them are very helpful. However, the list must be formatted in a certain way, otherwise the sorting and filtering tools will not work.
Typically, a list consists of records (rows) and fields (columns). The columns must contain the same type of data. The list must not contain empty rows or columns. If there are headings in the list, they should be formatted differently than the rest of the list elements.
Sorting lists
Sorting or arranging lists makes it much easier to find information. After sorting, records are displayed in the order determined by the column values (alphabetical, ascending/descending by price, etc.).
Make a short list to practice with.
Select it.
Click the button "Sort and Filter" on the panel "Editing" tapes "Home".
Select "Sorting from A to Z". Our list will be sorted by the first column, i.e. according to the full name field.
If you need to sort the list by several fields, then the item is intended for this .
Complex sorting involves ordering data across multiple fields. You can add fields using the button .
As a result, the list will be sorted according to the established complex sorting parameters.
If you need to sort a field in a non-standard way, then the menu item is intended for this "Custom list.." drop down list "Order".
You can move sorting levels using the buttons "Up" And "Down".
Don't forget about the context menu. From it, you can also configure the sorting of the list. In addition, there are such interesting sorting options related to the selection of one or another element of the table.
The main difference between a filter and ordering is that during filtering, records that do not satisfy the selection conditions are temporarily hidden (but not deleted), while when sorting, all records in the list are shown, only their order changes.
There are two types of filters: regular filter(it is also called an autofilter) and advanced filter.
To apply an autofilter, click the same button as when sorting - "Sort and Filter" and select "Filter"(of course, a range of cells must be selected before this).
Buttons with arrows will appear in the list columns, by clicking on which you can configure the filter parameters.
Fields that have a filter on appear with a funnel icon. If you move the mouse pointer over such a funnel, the filtering condition will be shown.
To create more complex selection conditions, the paragraph is intended "Text filters" or "Numerical filters". In the window "Custom AutoFilter" you need to configure the final filtering conditions.
When using an advanced filter, selection criteria are specified on the worksheet.
To do this you need to do the following.
Copy and paste the list header into the free space.
In the appropriate field(s), specify filtering criteria.
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 specific 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.
The advanced filter is called that because it has expanded and truly limitless and unique capabilities compared to the autofilter. Continuing our acquaintance with MS Excel tools for working with database tables, in this article...
We will take a closer look at how the advanced filter works. Like an auto filter, an advanced filter in Excel is designed to hide part of the information from the user's view in order to make the remaining part easier to perceive or to print only selected database records.
With the advanced filter you can filter anything in the table!!! The limit to the capabilities of this tool is only the user's imagination!
You are reading the fourth post in a series of articles about creating databases in MS Excel and organizations information processing.
You can read about how in the previous articles in the series.
Filter No. 2 – advanced filter!
To get started, we need to create another table above the database in which we will indicate the data that is the selection criteria for the advanced filter. (Sometimes it is more convenient to place such a table on a new sheet.)
Remember when we created the example database, we left a few blank rows at the top of the table, saying that they would be needed later when analyzing the data? That time has come.
1. Open the file in MS Excel.
2. When creating a table of criteria for selecting an advanced filter, I recommend that you first act in a formulaic manner, without thinking about the tasks to be solved. Copy All column field headers from cells A7...F7 to cells A1...F1 – workpiece Ready for the criteria table! You can start the main work.
For stable and error-free operation of the filter, there must be at least one empty line between the selection criteria table and the database table!
Let’s continue to study the advanced filter in Excel, solving practical problems using the example of working with the DB2 database “Production of metal structures by site No. 2.”
Task #5:
Show information about the production of beams for all orders for the entire period.
1. We write down the filtering parameter - the word “beam” in the “Product” column of the upper table of selection criteria.
2. We activate (“click with the mouse”) any cell inside the database table - this will automatically fill the “Source Range” window in the “Advanced Filter” drop-down dialog box.
3. We enable the advanced filter in Excel 2003 through the main menu of the program. Select: “Data” - “Filter” - “Advanced filter”.
4. In the “Advanced Filter” window that pops up, fill in the windows as shown in the screenshot below this text.
The advanced filter allows you to filter the list in place, but can also copy the filtering result to another user-specified location.
5. The result of the advanced filter is shown in the following screenshot. The advanced filter showed all database records that contain the word “beam” in the “Product” column - task completed.
Letter case does not affect filtering results!
Rules for the joint “work” of several selection conditions
The advanced filter in Excel allows you to filter by various very complex combinations of filtering conditions. You can set several different conditions for several columns, you can set several conditions for one column, or you can set selection parameters using a formula - there are a lot of options!
But there are only two main rules! All other cases are different combinations of these two rules.
1. Selection conditions posted on one line in one or more different columns of the criteria table, instruct the advanced filter to show rows for which all without exception these conditions. (Selection parameters are connected by logical operators “AND”.)
2. Selection conditions posted on different lines in one or more columns of the criteria table, instruct the advanced filter to show all rows for which at least one from these conditions. (Selection parameters are connected by logical operators “OR”.)
We will illustrate with examples the operation of the above rules and show how the advanced filter in Excel works when solving complex problems.
Since we examined in detail the sequence of user actions when enabling an advanced filter in the previous example, only the selection conditions and the final filtering results will be shown below.
Task #6:
Filter information about plates weighing less than 0.1 ton across the entire database.
As a result of the advanced filter, all database plates with a mass of less than 0.1 tons are shown (illustration of rule No. 1).
Task No. 7:
Display information about all plates available in the database, as well as about any other products weighing more than 1 ton.
The problem is solved - records are shown for all database plates and for all products heavier than 1 ton (illustration of rule No. 2).
To cancel the action of an advanced filter, you must execute the command in the main menu of the programExcel“Data” - “Filter” - “Display all”.
I draw your attention to the need to carefully monitor the correctness of specifying the initial database range and the condition table range in the “Advanced Filter” drop-down dialog box!
Specifically, in the last example you need to specify: “Condition range: $A$1: $ F$3 »!
If there are completely empty rows in the range of conditions or in your database, the advanced filter will not work!
Results.
You can use common wildcards and mathematical symbols when entering search terms:
- * - any number of any characters
- ? – any one symbol
- = - equal
- < — меньше
- > - more
- <= — меньше или равно
- >= - greater than or equal to
- <>- not equal
The advanced filter in Excel is flexible and informative to use. The selection criteria are always before the user's eyes in the form of a table of selection criteria, which is undoubtedly very convenient.
If there are a lot of records, and in real databases there are thousands of unique records, then filtering out the ones you need becomes problematic using an autofilter, since you have to scroll in the drop-down list. The extended filter does not have this drawback.
“Dig deeper” into the capabilities of this tool yourself - it’s worth understanding! The increased efficiency of your work will many times cover the time spent on studying!
Continuation of the topic storing and managing large amounts of information- in the following articles of the series.
I beg respectful author's work subscribe for article announcements in the window located at the end of each article or in the window at the top of the page!
Dear readers, write questions and comments in the comments at the bottom of the page.
Filtering Excel data helps you quickly set conditions for those rows that need to be displayed, and hide other rows that do not meet these conditions.
The filter is installed on table headings and subheadings; The main thing is that the cells on which the filter will be installed are not empty. And it is located in the Excel workbook menu on the “Data” tab, “Sorting and Filter” section:
By clicking on the “Filter” icon, the top cells of the range will be defined as headings and will not take part in filtering. Headings will be marked with an icon. Click on it to see the filter options:
Filters in Excel allow you to sort. Remember that if you have not selected all the columns of the table, but only some of them, and apply sorting, the data will be lost.
“Filter by color” allows you to select rows in a column that have a specific font or fill color. You can only choose one color.
“Text filters” make it possible to set certain conditions for strings, such as “equal to”, “not equal to” and others. By selecting any of these items, a window will appear:
You can set the following conditions here:
- The conditions “equal” and “not equal” do not require explanation, because everything is very clear with them;
- “greater than,” “less than,” “greater than or equal to,” and “less than or equal to.” How can strings be compared to each other? To understand this, remember how Excel performs sorting. Those. The further a row is in the sorting list, the greater its value. The following statements are true (correct): A<Б; АА>A; A<=Я; 5 яблок < апельсин.
- “begins with,” “does not begin with,” “ends with,” “does not end with,” “contains,” and “does not contain.” In principle, the conditions are self-explanatory and can take a character or a set of characters as values. Pay attention to the hint in the window located below all the conditions (explanations will follow).
If necessary, you can set 2 conditions using logical “AND” or “OR”.
If “AND” is selected, all conditions must be met. Make sure that the conditions do not exclude the friend, for example "<Значение И >Meaning", because nothing at the same moment can be both more and less than the same indicator.
When using "OR", at least one of the specified conditions must be met.
There is a hint at the very end of the custom autofilter window. Its first part: “The question mark ““?” means any one character...”. Those. when setting conditions when it is impossible to accurately determine a character in a specific place in a line, substitute “?” in its place. Examples of conditions:
- Starts with “?va” (starts with any character followed by the characters “va”) will return the results: “Ivanov”, “Ivanova”, “quartz”, “matchmaker” and other strings that match the condition;
- Equals "???????" – will return as a result a string that contains any 7 characters.
The second part of the hint: “The sign ""*"" denotes a sequence of any characters." If it is impossible to determine in the condition which characters and in what quantity should be in the line, then substitute “*” instead. Examples of conditions:
- Ends with “o*t” (ends with the characters “o”, followed by any sequence of characters, then the character “t”) will return the result: “sweat”, “cake”, “turnover” and even this one - “rvnshchuoooviunisvrunct”.
- Equal to "*" – will return a string that contains at least one character.
In addition to text filters, there are “Numeric filters”, which basically accept the same conditions as text ones, but also have additional ones related only to numbers:
- Above Average and Below Average - Returns values that are above and below the average, respectively. The average is calculated based on all numeric values in the column list;
- “First 10...” – clicking on this item brings up a window:
Here you can set which elements to display first from the largest or first from the smallest. Also, how many elements to display if the “list elements” item is selected in the last field. If the “% of the number of elements” item is selected, the second value specifies this percentage. Those. if there are 10 values in the list, the highest (or lowest) value will be selected. If there are 1000 values in the list, then either the first or the last 100.
