Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
- Power Bi (35+)
Auto filter in excel is used to filter out different types of desired data in a data range or column, this is an inbuilt button which is available in the Home tab in the editing section or we can also use the keyboard shortcut CTRL + SHIT + L, using this feature makes the work easier for any user as one can filter out the required data only.
Excel AutoFilter (Table of Contents)
AutoFilter In Excel
Excel Autofilter is an easy way to turn the values in excel columns into specific filters based on the cell content. The auto filter in excel enables us to filter our data as we desire in one or two or more columns at once.
Excel AutoFilter allows us to slice and dice our data as per our own requirements. We can filter based on our choices from a list or search that specific data we want to find. The rows which do not meet the criteria of the filters will be hidden.
In simple words AutoFilter in Excel allows us to view specific rows in excel while hiding the other rows. When excel AutoFilter is added to the header of the row it gives us a drop-down menu in the header row. It provides us with a number of filter options which we will discuss in this topic.
Filter Options in AutoFilter in Excel
AutoFilter in excel is allowed us to view specific rows in excel while hiding the other rows. When excel AutoFilter is added to the header of the row it gives us a drop-down menu in the header row.
AutoFilter in excel gives us multiple filter options such as :
- Equals to
- Greater than
- Less Than
- Greater than or equals to
- Begin with
- Ends with
- Does Not Contains
The first four are the number of filters while the rest is the text filters.
2 Ways to Enable AutoFilter in Excel
There are two ways to use the auto filter in excel:
1. In the data, tab click on Filter under the Sort & Filter Section
2. Excel shortcut – Press Ctrl + Shift + L.
The options are used to specify which rows of the excel workbook are to be displayed.
How to Use of Excel Auto Filters?
Let us learn the use of Excel Auto Filters by few examples:
AutoFilter in Excel – Example #1
For a real estate website, they have data for different property types “residential, commercial” also they have specific broker and photographer who clicked the images for those real estate properties. The company also maintains the picture count in the properties taken by the photographer.
Now the task is to find which city has a picture count of 33 and broker Prateek has that property.
- Click on any row header that is on row 1.
- Now in Data Tab click on “Filters” under the sort and filter section.
- In the row header, the filter is applied and it gives us a drop-down menu.
- Unselect all the count by unchecking select all and select 33 for picture count.
- Now in Broker column select broker as Prateek by unchecking select all and selecting Prateek.
- Now we have our cities which have 33 pictures and broker Prateek has those properties.
By excel autofilter, we have chipped our data, and the data which did not meet our criteria i.e. picture count other than 33 or broker other than Prateek is hidden.
We have our desired data as we wanted it to be.
AutoFilter in Excel – Example #2
In a teacher’s class, there are students who have scored marks in their annual exams. The teacher wants to know which student has scored marks above than 50 in maths and their total is above than 300.
- We will use a different approach to use filters this time.
- In the row header press “Ctrl” + “Shift” + “L”.
- After the filter is applied now in the maths column uncheck select all and select values above 50
- Now select values in total above 200 by unchecking select all and selecting values above 300.
- Now we have those students who have scored above 50 in mathematics and total is above 300.
AutoFilter in Excel – Example #3
In continuation with example 2, we can also use a custom filter.
But first what is a custom filter?
A custom filter is an option in excel filters which allows us to put certain conditions to get our values for example in example 2 the condition marked above 50 in mathematics or total above 300 in the total marks.
Let us learn custom filter by example 2.
- Select any cell in the row header.
- Press Ctrl + Shift + L.
- In the maths, filter click on number filters as the data is in numbers a dialog box pops up.
- Our condition values above than 50 so select greater than and another dialog box pops up.
Write 50 in the box where there is the text “is greater than”.
- Excel automatically filters the data in which the marks in maths is above 50.
- Now in the total column use the same approach and write total greater than 300.
Now we have our result where the marks in mathematics are above 50 and the total is above 300.
Things to Remember about AutoFilter in Excel
- Click on Row header to insert the filter.
- Filter hides the data which does not meet the criteria.
- We can remove the filter by any of the two options to use the filter and get the data back in original format.
This has been a guide to Auto Filter in Excel. Here we discuss how to use AutoFilter in Excel along with excel example and downloadable excel templates. You may also look at these useful excel tools –
- VBA AutoFilter
- How to Lock Row Header in Excel?
- Excel Add Filter
- Excel Filter Shortcut
- How to Apply Conditional Formatting in Pivot Table?
- Excel Conditional Formatting Based on another cell value
- Excel Numbering
- Hide Formula in Excel
- Auto Format in Excel Tips
- Formatting in Excel Tips
- Tips to use Conditional Formatting in Excel
- Tips to Sort Data in Excel