Filter Text Number Date and Filter by Color in Microsoft Excel AutoFilter Shortcuts Step by Step

Data collection, data organization and data analysis, the main purpose of these steps is to find the relevant information for an organizations. Sometimes we only need to view the data for specific items. For example suppose your business territory is divided into four regions, North, South, East and West. Now let suppose you need to view the performance of North region and the South and so on. If you are working with large data then you might find that North's few rows are in 3rd and 4th rows and then East has began and then again North's data. In this situation FILTERs and SORT helps us greatly. Filter & Sort are very useful to analyze data, therefore, learn how to use. Filters can be used to find or display data according to cell or font color.

Lets' find out how to work with this essential tool in Microsoft Excel. We shall discuss about FILTER in this article today.

What is filter in Excel?

Microsoft Excel Filter or AutoFilter, a quick way to display only the information relevant at a given time and hides all other data from view. You can filter rows in Excel worksheets by value, by format and by criteria. After applying a filter, you can copy, edit, chart or print only visible rows without rearranging the entire list.

Filter in Excel

Once the column headings are in pace, select any cell within your data, and use one of the following methods to insert filter.
How to add filter in Excel

1.   On the Data tab, in the Sort & Filter group, click the Filter button.

How to Filter

2.   On the Home tab, in the Editing group, click Sort & Filter > Filter.

How to Filter

3.   Or you can use the Excel shortcut to turn the filters on or off,  Ctrl+Shift+L (or you can use Alt+D+F+F)

4.   Whatever method you use, the filter drop-down arrows will appear in each of the header cells and you can now filter by selecting items.

How to apply filter in Excel

A drop-down arrow in the column heading appearing means that filter is added, but not yet used. When you stays on this down arrow icon or hover over the arrow, a screen tip displays "Column Header: (Showing All)".

To filter data in Excel, you need to do the following:

1.   Click the drop-down arrow for the column you want to filter.

2.   Uncheck the Select All box to quickly deselect all data.

3.   Check the boxes next to the data you want to display, and click OK.

Use of Filter in Microsoft Excel

And you are done! The filter is applied to column B, temporarily hiding any regions other than East.

The drop-down arrow in the filtered column changes to the Filter button, (a small down arrow displayed on the left of this button) and hovering over that button displays a screen tip indicating which filters are applied:

How to know Filter has been applied

Filtering multiple columns

You can repeat the above steps for as many columns as you want and you are done for multiple selection.

For example, after selecting East region from the region Column header, you may further be interested to filter Territory Code T-14 or  something else. Just repeat the same steps.

How to use filter in Excel - AutoFilter

Apart from basic filtering options discussed above, AutoFilter in Excel provides a number of advanced tools that can help you filter specific data types such as text, numbers and dates exactly the way you want.

And now, let's have a closer look at each option and see how you can create a filter most suited for your data type.

In the filter drop down you will find one menu Text Filters and within this menu you will find some sub-menus also like Equals, Does not Equals, Contains, Does not Contains etc., as shown in the below image. These filters are advanced level filtering method and if you need specific filtered data, you can use these filter options. I shall discuss most of it.

Excel Text Filter

Filter Text Data

•   Filter cells that are exactly equal or does not equal to a specified data, as per your requirement and logic.

•   Filter cells that begin with or end with a specific character(s). This is very important feature in filter.

•  Filter cells that contain or do not contain a given character or word anywhere in the text.

Example :
If you click on Filter button and then Text Filters and then Equals Submenu, a small window opens (as shown in the below picture) and again click on the Drop Down Button where Equal word is displayed (as shown in the below image) you will find few options in that drop down list which is self explanatory.

Customize AutoFilter

Customize AutoFilter

Filter column with two criteria

To filter data in Excel with two text criteria, you need to do the following:

Apply Filter in the Columns, Click on Text Filters, Click Equals and Check And or Or radio button depending on whether both or either criterion should be true. In my case I've selected OR to see the data of East and North together as shown in the below image.

Multiple Filter in Excel

•   Select the comparison operator for the second criterion, and enter a text value in the box right to it.

How to create filter in Excel with wildcard characters

If you don't remember exact search or want to filter rows with similar information, you can create a filter with one the following wildcard characters:

Wildcard in Excel Filter

How to filter numbers in Excel

Excel's Number Filters allow you to filter out numeric data in a variety of ways, for example,

•         Filter top 10 or bottom 10 numbers.

•         Filter numbers equal or not equal to a certain number.

•         Filter numbers, greater than, less than or between the specified numbers.

•         Filter cells with numbers that are above average or below average.

Use of Filter to Number in Excel

How to filter dates in Excel

Excel Date Filters provide lots of varieties of choices that let you filter records for a certain time period quickly and easily.

By default, Excel AutoFilter groups all dates in a given column by a hierarchy of years, months, and days. You can expand or collapse different levels by clicking the plus or minus signs next to a given group. Selecting or clearing a higher level group selects or clears data in all nested levels.

In addition, Date Filters allow you to display or hide data for a particular day, week, month, quarter, year, before or after a specified date, or between two dates. The screenshot below demonstrates all available date filters:

Apply Filter to Date in Excel

You can customize filter for date.

Customize Date Filter

How to filter by color in Excel

If the data in your worksheet is formatted manually or through conditional formatting, you can also filter that data by color.

Clicking the autofilter drop-down arrow will display Filter by Color with one or more options, depending on which formatting is applied to a column:
      Filter by cell color
      Filter by font color
      Filter by cell icon

How to copy filtered data in Excel

The fastest way to copy a filtered data range to another worksheet or workbook is by using the following 3 shortcuts.

1.   Select any filtered cell, and then press Ctrl + A to select all filtered data including column headers.
To select filtered data excluding column headers, select the first (upper-left) cell with data, and press Ctrl + Shift + End to extend the selection to the last cell.

2.   Press Ctrl + C to copy the selected data.

3.   Switch to another sheet/workbook, select the upper-left cell of the destination range, and press Ctrl+V to paste the filtered data.

Usually, when you copy the filtered data elsewhere, filtered-out rows are omitted. In some rare cases, mostly on very large workbooks, Excel may copy hidden rows in addition to visible rows. To prevent this from happening, select a range of filtered cells, and press Alt + ; to select only visible cells ignoring hidden rows. If you're not accustomed to using keyboard shortcuts, you can utilize the Go To Special feature instead (Home tab > Editing group > Find & Select > Go to Special... > Visible Cells only).

How to remove filter in Excel

After applying a filter to a certain column, you may want to clear it in order to filter the data in another way.

To clear filter in Excel, do any of the following.

To remove a filter from a column, click the filter button in the column's header, and then click Clear Filter from <Column name>:

Customize Date Filter

To remove all filters in a worksheet, either:

•         Go to the Data tab > Sort & Filter group, and click Clear.

•         Go to the Home tab > Editing group, and click Sort & Filter > Clear

•         Or You can remove all the filters by pressing Ctrl+Shift+L or Alt+D+F+F

Filter not working in Excel

If Excel's AutoFilter stopped working partway down a worksheet, most likely it's because some new data has been entered outside the range of filtered cells. To fix this, simply re-apply filter. If that does not help and your Excel filters are still not working, clear all filters in a spreadsheet, and then apply them anew. If your dataset contains any blank rows, manually select the entire range using the mouse, and then apply autofilter. As soon as you do this, the new data will be added to the range of filtered cells.

Basically, this is how you add, apply and use filter in Excel. But there is much more to it! In the next tutorial, we will explore and capabilities of Advanced Filter and see how to filter data with multiple sets of criteria. Please stay tuned!

Things to Remember

a)   Column headers should not be blank in between Columns or Filter will be applied only to the continuous field and rest will be ignored.

b)   Different Excel filter types are mutually exclusive. For example, you can filter a given column by value or by cell color, but not by both at a time.

c)   For correct results, do not mix different value types in a single column because only one filter type is available for each column. If a column contains several types of values, the filter will be added for the data that occurs the most. 

For example, if you store numbers in a certain column but most of the numbers are formatted as text, Text Filters will appear for that column but not Number Filters.

Thank you for reading ...
You can also watch the below YouTube video for further explanations.