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+)
Filters in excel are also known as auto filters, the filters are used to show some specific values desired by user in rows and columns, filters can be used from the sort and filter option from the editing section in the Home tab or we can use the keyboard shortcut using CTRL + SHIFT + L, to use filters we need to select the header of the table and use any one of the options above.
Data Filters in Excel (Table of Contents)
What are the Filters in Excel?
Filters can be used to filter down the data in your worksheet and hide the rest of the data from a view. This also works as the grouping of data. Data filtering allows you to qualify and display only the data on the basis of certain criteria.
It reduces the view of Excel data that meet certain criteria and show records. Filtering means refining the given data sets into simply what a user (or set of users) needs.
How to use Data Filters in Excel?
Let’s understand the working of Data Filters with the below examples. Data filters are very simple and easy to use. It can be used in excel worksheet and as in VBA to automate the filtering process.
Let’s consider a data set of sales from different countries.
Select any cell from the dataset and click on the Data tab, in the Sort & Filter group, click Filter to apply the filter in the given data set. Click the arrow next to Country and filter the given data set by country India.
And the output is as follows:
In the second example, we apply the filter to the quarter column in the above ‘sales’ data set.
After applying the filter of the quarter we get the below data.
In the third example, we apply the filter of numbers data sets.
Let’s consider the below-given data set and apply the filters on the basis of requirements.
There are following Number filters conditions available in the data filters as shown in the below tables.
Conditions can be:-
<> Not equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
Now apply the custom auto filter on the condition is greater than 150.
After applying the sorting on Oct month is greater than 150 the output will be as follows:
Let’s apply the filter on data set of Names that have ‘a’ in the name.
Now apply the custom auto filter on the condition it contains ‘a’.
The output will be:-
Data Filters can be used as a VBA Function.
Suppose we have the data sets located in the excel sheet range from A3 to D19 (sales data).
Then run the below macro to filter the data from quarter Q2.
Range(“A3”).AutoFilter Field:=4, Criteria1:=”Q2”
Things to Remember about the Data Filters in Excel
- You cannot apply the filter on two datasets in the single excel sheet.
- You cannot perform any calculations or data manipulation in the filter out data.
This has been a guide to Filters in excel. Here we discuss how to use Data Filters in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Excel VBA AutoFilter
- How to Use Excel Greater Than or Equal to?
- Filter in the Excel Pivot Table
- Adding Excel Column Filter
- Not Equal to in Excel
- Shortcut Keyboard Filter in Excel
- Conditional Format in Pivot Table – How to Apply?
- Hiding Formula
- TRANSPOSE Function
- MODE in Excel
- How to Format in Excel?
- What is Conditional Formatting in Excel?