To use a filter horizontally in Excel, you need to use the FILTER function that allows you to filter a table based on criteria that you specify in the function. You can specify a table and then using the name of the row, specify criteria to filter.
In the following example, you have small data set where you have name and age in the horizontal form (headings are in rows). Now we need to filter data based on the age where the age is above 45.
Here we have a total name and age of 40 people, which means the range we have with name and age is A1:AO2. In which cells A1 and B1 are the headings.
Steps to Apply Horizontal Filter in Excel
- In cell D2, 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 age value.
- Next, enter greater than and equal to the operator.
- After that, enter the 45 that we want to use as criteria.
- In the third argument, you can use specify a value to use for the cells where there is no value.
- In the end, enter closing parentheses and hit enter to get 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.