Using filters in Excel can narrow down the data in the worksheet and you can hide some parts of it. You may get confused in filtering and grouping cells because they sound similar. However, filtering is different because it lets you see data that is needed.
Only specific data from the sheet will be displayed when you use the filter option in Excel. In a large dataset, when you need to be focused on just a part of the data, filtering will help you. Here you will see how Excel filter works and how you can use them effectively.
Filtering Data
You can use filters in multiple ways because this feature helps you in worksheet management. Not only text, but you can even filter dates and numbers as well. furthermore, you can even use more than one filter to arrange data according to your choice.
FILTER Function Syntax
=FILTER(array, include, [if_empty])
array is the range consisting of the values that needed to be filtered.
include is considered as the logical test that returns a Boolean array such as TRUE/FALSE
if_empty appears as an optional value to return if the included array is empty i.e. if the filter results in no records.
How Excel Filter Works?
Using filters is truly convenient because it helps in managing data just the way we want to. To filter data, select the values that needed to be visible and unselect the others.
Here we have three methods to filter data in Excel:
- Filter Data with the Home Tab
- Filter Data with the Data Tab
- Filter Data with the Shortcut Key
Below we have a dataset to be used in these methods, let’s have a look:
In the following table, you can see the invoices issued to the buyers and we need to filter the data.
Filter Data with the Home Tab
Under the sort and filter option, you will find the editing section given in the Home tab.
- Choose the data and click the “filter” option placed under the “Sort and Filter” drop-down menu.
- To the selected data range, you will see the filters are applied. In the red boxes, you will see the drop-down arrows, which are filters.
- Click on the drop-down arrow of the column “city” to see the different city names.
- If you need to see only “Delhi” invoice values, choose “Delhi” and unselect all the other boxes.
- Below you can see the data for the city of Delhi only.
Filter Data with Data Tab
Find the filter option given in the Data tab and proceed further.
Under the “Sort and filter” section, there is a filter option given in the Data tab.
Filter with the Shortcut Key
Using keyboard shortcuts helps you in speeding up your productivity. Choose the data on which you want to apply filters and then use the shortcuts given below.
Press the Shift + Ctrl + L keys at the same time.
SHIFT+CTRL+L
Press Alt + D + F + F keys at the same time.
ALT+D+F+F
Keep in mind that the preceding shortcuts used for adding filters are toggle keys. Pressing these keys repeatedly will turn them on and off the filters.
Tricks to Filter in Excel
Below we have some useful tricks to be followed while filtering data:
- You must type the value when your dataset is bigger enough. It will help in filtering each potential match.
- When you need to filter the dates or numbers, you should use the “number filters” option.
- If your data needs to be filtered by the color of specific rows, you should use the “filter by color” option.
Wrap Up
That’s it. This is how Excel filter works effectively by using the above-mentioned methods. You should try at least each method once. Keep sharing your knowledge and continue exploring Excel.
