Yes, it is possible to filter data horizontally in Excel, but not with the normal filter option. You can use the FILTER function in Excel and link it to a cell where you enter the criteria, like the following example.
=FILTER(all_data,filter_column="value_to_filter")
It happens sometimes that you are dealing with horizontal data and need to filter it. The point is, in Excel, you cannot apply a filter on a row header; you can only apply it on a column header. This means that, by default, when you use Filter in Excel, you cannot filter horizontal data.
But there is a workaround with a new function called FILTER. This function, as its name suggests, helps you filter data from one range and return it to a new range.
What is the Filter Function?
Now, before we start using the FILTER function to filter horizontal data, let’s go over a short description of the function in case you have never used it. The first thing to know is that the FILTER function is only available in Excel 365, Excel 2024, Excel 2021, and Excel for the Web.
Its syntax is quite simple; you need to define two arguments first. The first argument is the main data, in this case, the horizontal data you want to use in the function. The second argument is the single column you want to use to filter that data.
=FILTER(array,include,if_empty)
In this syntax, the array will be your main data, and include will be the column and condition you want to use to filter that main data. After that, you have the third argument, which allows you to specify a value if no result is found in the main data based on the condition or criteria you specify.
And now, let’s take a simple example to understand how you can filter your horizontal data and return it into a new range, horizontally.
Apply a Horizontal Filter in Excel
In this data, we have two rows: one row with the names and another row with the ages. The whole idea here is to filter the data based on age. For example, if I want to filter people who are above 40 or 45, I want the filter set up so that I can enter the value in a cell, and the FILTER function will filter the data for me.
- In cell A4, start adding the filter function.
- From here, in the first argument, refer to the range B2:AO2, where we have the actual data that we need to filter.
- Now, in the second argument, refer to the range where we have the age value.
- Next, enter the greater than or equal to operator.
- After that, enter the 45 that we want to use as criteria.
- In the third argument, you can specify a value to use for the cells where there is no value.
- In the end, enter the closing parentheses and press Enter to obtain the filtered data.
As you can see, we have got filtered data from the original data that we have in the horizontal form.
=FILTER(B1:AO2,B2:AO2>=45)How Does this Formula Work?
Before we try to understand this formula, we need to know that FILTER is a DYNAMIC function that allows you to work with multiple values at the same time in a single formula and then return values in multiple cells.
Now let’s split this function into two parts to understand this:
In the first part, we have referred to the entire range where we have name and age, which is B1:AO2. When you refer to this, it tells Excel to take two different arrays as we have rows A and B.
Now, in the second part, we have referred only to the age column and along with that we have specified a condition to test which equals to and greater than 45.
Now the entire magic of this function is in this part. When specifying a condition to filter, it tests each value in the age column.
On testing, it founds only three values that are greater than or equal to 45 in the age column. And this tells the FILTER function there are three values that we need to filter.
As I said, it’s a dynamic function when selecting any of the cells the entire filtered data highlighted.
You can also go one step ahead and transpose data and filter horizontal data and transpose it into vertical data by using the transpose function.
You just need to wrap the filter function in the transpose function.