As you already know, Excel has many filters to perform multiple functions easily. In this post, we are going to discuss horizontal filters used frequently in a worksheet. Let’s dive in to learn about horizontal filters Excel.
What are Horizontal Filters?
Using filters lets you enjoy the freedom of displaying or hiding multiple entries in a dataset. Talking about a horizontal filter, lets you experience the filtered columns of your worksheet. Remember that it is not a built-in feature that’s why you have to practice it. Unlike vertical filter in Excel, this filter rather than rows filters your columns to look at the dataset, as you need.
You can make a horizontal filter in different ways. Using a filter horizontally in Excel needs you to make use of the FILTER function. Filter function lets you filter a table based on criteria that you identify in the function. Once the table is identified, you can then use the name of the row, or even you can specify the criteria to filter.
Method 1:
Use the FILTER Function to Filter Horizontal Data in Excel
On the bases of pre-defined criteria, the Filter feature lets you filter data horizontally in Excel. Using this feature, you can filter the data in both vertical and horizontal formats.
Let’s have a brief overview of the FILTER Function:
FILTER Function
Syntax: =FILTER(array, include, [if_empty])
Argument Details
The array is a required argument that demands the range of data to be filered.
Include is also a required argument and a Boolean array has an identical height or width to the array.
If_empty is an optional argument that explains if the criteria don’t match, you will get a predefined string.
Here in this example, you will see the dataset is filtered on the bases of three different categories, such as Fruit, Vegetable, and Fish.
Now, let’s go further in some detailed steps:
In cell C10, you can see the vegetable category that will be used as the criteria to filter the dataset. You will also have to make an output table that lets you accommodate the filtered data.
Put the following formula in cell C12.
=FILTER(C4:J8, C5:J5=C10, “Not Found”)
Formula Breakdown
In the FILTER function, you can see two arguments are working – data and logic.
The Blue colored box in cells C5:J5 shows filtered data. In row C, you can also see cells C5:J5 with categories given in the red color boxes. You can set the criteria for these boxes.
The C5:J5=C10 lets you check the C10 value against every cell value of C5:J5.
This formula also provides a dynamic solution that simply shows when the data of a cell is changed, the output will adjust the value.
In the outcome, you will see only columns along with the Vegetable category.
Now, you will have to change the value of cell C10 to Fruit and your data will be filtered horizontally for the specific category.
Method 2:
Use of Transpose Option to Filter Data Horizontally in Excel
We have another outstanding method that lets you understand how to filter horizontally in Excel. Follow the steps given below to utilize this approach:
Using any of the options from the context menu or the Home tab, you have to open the Paste Special window. Choose the Transpose checkbox from the Operation options and press OK.
Now, choose the transposed dataset and click on the Filter option from the Data Tab.
Doing this will activate the filtering options on every column. Click on the Category Filter and then check the Vegetable category.
Below you can see the output.
This is how to filter horizontally in Excel by using the above-mentioned steps. Now, whenever somebody asks you can you filter horizontally in excel, you can answer them easily with examples.
To Sum Up
Now, you are aware of how to filter rows in Excel and how you can perform it. Most likely you will now be able to perform as well as help others in learning this simple but effective function. Keep sharing useful information and continue your practice.
