How To Filter In Excel By Name

In this tutorial, you will learn how to filter data in Excel in different ways: how to create filters for character values, numbers, dates, how to use filters to search, and how to filter. by color or by the value of the selected cell. You will also learn how to clear a filter, and how to fix an Excel AutoFilter when it's inactive.

If you are dealing with large data series, then not only the data computation, but finding relevant information is also very challenging. Fortunately, Microsoft Excel has made it easier to minimize the search area with a simple but very useful filter tool. To know more about filtering in Excel, click on the links below.

What are filter in Excel?

Filters in Excel, also known as Auto Filters in Excel, are a quick way to display relevant information at a given time and delete other extraneous data. You can filter rows in an Excel worksheet by value, by format, or by criteria. After using filters, you can copy, edit, chart, or print just the displayed rows without having to reorder the entire list.

Filters with ordered in Excel

https://www.youtube.com/watch?v=yPgF_zV73CI

In addition to the myriad of filtering options, Excel AutoFilter also provides sorting options related to specific columns:

For character values: Sort A to Z, Sort Z back A, and Sort by color.

For digits: Sort small to large, Sort big to small, or Sort by color.

For dates: Sort old to new, Sort new to old, and Sort by color.

Here's the difference between sorting and filtering in Excel:

When you sort data in Excel, the entire table is rearranged, for example in alphabetical order or from minimum to maximum value. However, sorting doesn't hide all the items, it just sorts the data in a new order.

When you filter data in Excel, only the items you want to see are displayed, and other extraneous items are temporarily hidden.

How to add filter in Excel

https://www.youtube.com/watch?v=04_kOwCnyog

For the AutoFilter in Excel to work correctly, your data series should have row headers with column names as below screenshot shown:

Once the column headers are added, select any cell in the data series, and use one of the methods below to insert a filter.

3 Way to add Filter to Excel

On the Data tab, in the Sort and Filter groups, click the Filter button.
On the Home tab, in the Editing group, click Sort and Filter> Filter.

Use the shortcut in Excel Filter to turn the filter on / off: Ctrl + Shift + L
Whichever method you use, down arrows will appear in each title box:

How to use filter in Excel

https://www.youtube.com/watch?v=_OdsZR_rL1U

An A drop-down arrow in the column header means the filter has been added, but is not yet in use. When you move the mouse around the arrow, a screen tip displays (Show All).

To filter data in Excel, do the following:

Click the down arrow in the column you want to filter.

Uncheck the Select All box to deselect all data quickly.

Select the boxes near the data you want to display, then click OK.

For example, here's how we filter the data in the Region column to see the East and North only sales:

Done! The filter was used for column A, temporarily hiding any regions other than East and North.

The down arrow in the filtered column changes to the Filter button, and moving around it shows the screen tip - meaning the filter is already in use:

Filter multiple columns

https://www.youtube.com/watch?v=rgATdkyLey8

To use Excel filters for multiple columns, repeat the above steps for as many columns as you want.

For example, we can scale the result down to show Apples in the East and North. When you use multiple filters in Excel, the filter button is displayed in each filtered column:

Tips. To make the filter window in Excel wider and / or longer, move the mouse around the bottom handle, and as soon as the double-headed arrow appears, drag it down or drag it to the right.

Filter Blanks

To filter data in Excel ignoring blank or nonblank cells, do one of the following:

To filter but ignore blank cells, namely to show cells with data, click the auto filter arrow, make sure the (Show All) box is selected, and then deselect ( Blanks at the end of the list. This will display only the rows with data in the specified column.

To filter but ignore cells with data, namely show blank cells, deselect (Show All), and then select (Empty cells). This will display only those rows with blank cells in the specified column.

Note:

The selection (Empty Cells) is available only for a column containing at least one blank cell.

If you want to delete blank rows based on a certain main column, you can filter but ignore cells containing data in that column, select the filtered rows, right-click Selection, and then click Delete row. If you want to delete only completely blank rows and leave rows that contain some data or contain a few blank cells, check out this solution.

In addition to the basic filtering options discussed above, AutoFilter in Excel also provides advanced tools to help you filter specific data types like characters, numbers, and dates. exactly according to your needs.

Note:

The different types of filters in Excel are mutually exclusive. For example, you can filter a column by value or by cell color, but not both.

For accurate results, don't combine different data types in the same column because only one filter type is active for each column. If a column contains multiple types of values, filters are added according to the data type that occurs most often. For example, if you store digits in a specified column but most of the digits are formatted as characters, the character filter is visible for that column, not the alphanumeric filter.

And now, take a closer look at each option and see how you can create the best filter for your data type.

Filter data characters

If you want to filter a column that contains characters for something very specific, then you can take advantage of several advanced options provided by Character Filter in Excel such as:
Filter cells that begin or end with one or more special characters.

Filter cells that contain or do not contain a specified character or word anywhere in the string.

Filter cells that are absolute or not equivalent to one or more specific characters.

As soon as you add a filter to a column containing character values, the Character Filter will automatically appear in the AutoFilter menu:

For example, to filter but ignore rows containing Banana, do the following:

Click the down arrow in the column header and point to Character Filter.

In the drop-down menu, select the desired filter (in this example it does not contain ...)

The Custom AutoFilter dialog box will appear. In the box to the right of the filter, type the desired characters or items from the list.

Click OK.

As a result, all rows of Banana, including Green Banana and Yellow Banana, will be hidden.

Column filters have 2 conditions

To filter data in Excel with two conditions, do the following steps to syntax the first condition, and then do the following:

Select the And or Or button depending on whether both or only one of the two conditions will be fulfilled.

Select the comparison operator for the second condition, and then enter a character value in the box.

For example, here's how you filter rows that contain either Banana or Lemon:

How to filter number in Excel

Number filters in Excel allow you to process numeric data in many ways, including:

Filter numbers equal to or not by a certain number.

Filter numbers, greater than, less than, or between certain numbers.

Filter the first 10 or last 10 digits.

Filter cells that contain numbers greater than or less than the average value.

The screenshot below shows all available number filters in Excel.

For example, to create a filter that only shows orders with a total amount greater than VND 6 million but not more than VND 7 million, perform the following steps:

Click the automatic filter arrow in the column header, and then point to Number Filter.

Select a suitable comparison operator from the list, in this example Between ...

In the Custom AutoFilter dialog box, enter a smaller boundary value and a larger boundary value. By default, Excel suggests using the comparison operators "Greater than or equal to" and "Less than or equal to". You can change them to "Greater than" and "Lesser" if you add a boundary value.

Click OK.

Filter do no work in excel

If the AutoFilter in Excel doesn't run from the bottom half of the worksheet, it is mostly because the newly imported data is outside the filtered range. To solve this, use the filter again. If your filter still isn't running, delete all filters in the worksheet and use the new filter. If your data series contains blank rows, manually select all ranges with your mouse and then use automatic filters. As soon as you do this, the new data will be added to the filtered range.

Basically, this is how you add and use filters in Excel. But there's still more you can do! In the next tutorial, we will explore the capabilities of Advanced Filter and look at how to filter data with multiple conditions. Please look forward to it!

Soure: Blebees.com

source https://blebees.com/filter-in-excel-by-name/