In today’s lesson, we would touch on all you need to know to be able to add a filter to a dataset in excel. Before we begin, let’s look at why this is a very useful knowledge to have when working on your analysis or model.
Why do we Filter
We filter datasets in excel for the following reasons:
- Filtering a data set helps you better analyze your data. You may want to limit your analysis or view to one or more criteria while excluding irrelevant information, and adding a filter helps you do exactly that.
- Having the knowledge of how to filter in excel helps you manage your presentation to team members or senior colleagues when explaining your analysis.
- With the use of the Subtotal function, you can easily set other functions such as average, sum, max, min, count limited to what has been filtered only.
- The filter button gives you the ability to sort your dataset easily without having to go through the ribbon.
Steps to add a filter to a DataSet
Adding a filter to a data set is extremely easy, we would show you how to do add a filter by going through the ribbon and how to add a filter by using keyboard shortcuts.
Add a Filter through the Excel Ribbon
To add a filter through the excel ribbon, just follow these steps:
Step 1: Click on any cell within the data set
Step 2: Go to the home ribbon, look for the Editing group and click on the arrow below the sort and filtering icon, then choose filter.
Note: You can also get to filter by going through the data ribbon and clicking on the Filter Icon under Sort & Filter group
Result: You would notice that the column headings now have an arrow next to the heading name
Now the question you must be asking yourself is “how to filter data in excel”? Easy, just follw toe step below and you are good to go:
Step 4: Click on the arrows in the column you want to set as criteria and then pick the criteria. For example, let’s filter by Trade (Column C) and select trade in Europe only. To do this, first click on Select all to clear all the check boxes, then click the check box next to Europe.
Result: Excel displays only results in Europe Trade
You can add more than one criteria when you filter. For example, let’s say we want to view results for Tango only under the Europe trade, all we need to do is filter for Tango in column D like we did for Europe trade.
Result: As you can see from the image below, excel displays only Europe trade in Tango cruise line because of our filters
Filter by Selection Selected Cell Value
This is one of the fastest ways to add a filter to your data set. It avails us the opportunity of setting a filter based on the cell we are currently on. For example, let’s say we are on a cell with it’s trade region in Alaska, we can add a filter by following these steps:
Step 1: Right click on a cell containing Alaska as its trade
Step 2: Navigate to filter and then filter by selected cell’s value and click on it
Result: Excel filters for trade in Alaska only
Add a filter using Keyboard Shortcuts
To add a filter to your data set through keyboard shortcuts, just follow the combination below:
To remove the filter, use the combination again and your data set should now be without the arrows. While the filter arrows are there, you can bring up the filter selection box by using the keyboard shortcut below:
When the filter selection comes up, you can use the down arrow key to navigate to the search box and search for the criteria. Press enter when you are done.
