How to Create a Horizontal Filter in Excel

To apply a horizontal filter in Excel, you can use the FILTER function, which allows you to filter a table based on specified criteria. By specifying the table range and criteria, you can filter data horizontally.

Let’s consider an example where we have a small dataset with names and ages arranged horizontally (headings in rows). Our goal is to filter in Excel the data based on ages above 45.

Here’s how you can apply a horizontal filter in Excel:

1. Start in cell D2 and enter the FILTER function.
2. In the first argument, refer to the range B2:AO2 where the actual data to be filtered is located.
3. In the second argument, specify the range where the age values are located.
4. Add the greater than or equal to (>=) operator after the age range.
5. Enter the criteria value of 45.
6. Optionally, in the third argument, specify a value to use for cells where there is no value.
7. Close the parentheses and press Enter to obtain the filtered data.

The formula to apply the horizontal filter would be:

=FILTER(B1:AO2, B2:AO2>=45)

How Does this Formula Work?

The FILTER function is dynamic and enables working with multiple values in a single formula, returning values in multiple cells.

Let’s break down the formula into two parts:

1. The first part refers to the entire range where names and ages are located (B1:AO2). This tells Excel to consider two separate arrays since we have rows A and B.
2. The second part only refers to the age column and specifies a condition to test (>=45).

The magic happens in this second part. By specifying the condition, the function tests each value in the age column. In our example, it identifies three values that are greater than or equal to 45, indicating the need to filter those values.

As a dynamic function, selecting any of the filtered cells highlights the entire filtered data. Additionally, you can further manipulate the data by transposing it from horizontal to vertical using the TRANSPOSE function. Simply wrap the FILTER function with the TRANSPOSE function to achieve this.

I hope this helps you understand how to create a horizontal filter in Excel.