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, 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, 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.
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
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" column cells "The product's name". 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.
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, a square with an inverted triangle, appears in the table header on the right side of the title of each column.
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.
When working with tables, there is often a need to “gather into a pile” a number of similar data, for example, all people with the last name “Ivanov”, or all prices in the range from 500 to 1000 rubles. Naturally, MS Excel offers a number of tools for such operations, but the first thing we think of when it comes to sorting and filtering is MS Excel data filters.
How to enable data filter in MS Excel?
To set a filter on table columns, select the table header with the mouse, and then on the "Home" tab select , and further "Filter". Downward arrow buttons appear in the right corner of the column header cells. Click on it and you will see a very solid list of various built-in filters.
Let's try to understand this diversity.
- Filter by color. It only works if some of the table cells are marked with color (for example, if cells with income are marked green and expenses are marked red, they can be very conveniently filtered by color).
Result of applying a filter by color
MS Excel numeric and text filters
- Number/Text Filters (depending on whether the column contains numbers or text). The largest and most multifunctional group of filters. When you select any item, the “Custom Auto Filter” tool will launch, offering filtering options for every taste and color. It’s quite easy to understand them, just look at the examples below:
Filter all values in the table with a price less than 160.
Filter all values in the table with a price less than 160 AND at the same time more than 80
Filter out all values in the table that are not equal to 180 and 200
- Simple filter. There’s nothing to think about here - uncheck the boxes next to the data that needs to be filtered and click “Ok”. As a result, only the necessary information remains on the sheet.
The simplest filter in MS Excel - what you select is filtered
How to disable filter in MS Excel table?
Resetting a filter in MS Excel is even easier than setting it - again click the arrow on the right side of the table header cells, and in the context menu that appears, select the item "Remove filter from column".
If you want to completely remove the filter from the table, just repeat the same steps as at the very beginning - on the "Home" tab select Sort & Filter tool where click again "Filter".
Congratulations, data filters are now completely disabled in your Excel spreadsheet.
Use AutoFilter or built-in comparison operators such as greater than and top 10 in Excel to show the data you want and hide the rest. After you filter data in a range of cells or table, you can either reapply the filter to get the latest results, or clear the filter to redisplay all the data.
Use filters to temporarily hide some data in a table and see only the data you want.
Filtering a data range
Filtering data in a table
Filtered data displays only rows that match the specified criteria and hides rows that you don't want to display. Once you've filtered your data, you can copy, find, edit, format, chart, and print a subset of the filtered data without moving or changing it.
You can also filter by multiple columns. Filters are additive, meaning that each additional filter builds on the current filter and further reduces a subset of the data.
Note: When using the dialog box Search Filtered data searches search only the data that appears in the list. Data that is not displayed is not searched. To find all data, clear all filters.
More information about filtering
Two types of filters
With AutoFilter, you can create two types of filters: by list value or by criterion. Each of these filter types is mutually exclusive for each cell range or column table. For example, you can filter by a list of numbers or a condition, but not both; You can filter by icon or custom filter, but not both.
Reapplying a filter
To determine if a filter is applied, look at the icon in the column header.
Applying a filter repeatedly produces different results for the following reasons:
Data has been added, changed, or deleted to a cell range or table column.
The values returned by the formula have changed and the worksheet has been recalculated.
Don't use mixed data types
For best results, do not mix data types, such as text and number, or numbers and dates, in the same column because only one type of filter command is available for each column. If a mixture of data types is used, the command displayed is the data type that is called most often. For example, if a column contains three values stored as a number and four values stored as text, the command is displayed text filters .
Filtering data in a table
As you enter data into a table, filtering controls are automatically added to the column headers.
Filtering a data range
If you don't want to format your data as a table, you can also apply filters to a range of data.
Select the data you want to filter. For best results, columns should contain headings.
On the "tab" data"click the button" Filter".
Filtering options for tables and ranges
You can apply a general filter by selecting Filter, or a custom filter depending on the data type. For example, when filtering numbers, the item appears Numeric filters, for dates the item is displayed Filters by date, and for text - Text filters. By applying a general filter, you can select to display the desired data from a list of existing ones, as shown in the figure:
By selecting the option Numeric filters You can apply one of the following custom filters.
