Extract or Filter Horizontal Data

You might have been through this kind of situation where you need to filter out the horizontal data from the list having few columns. I am also pretty sure about it that you might have chosen to do it manually, which is also a great choice when you have only a few values in a list, and you want to filter out the top n values.

However, if you are dealing with numerous columns in the list and want to filter out the horizontal data, then executing these jobs manually would be a stupid move since there is a 90% probability that you will become weary of it and will be unable to accomplish your assignment on time.

But don’t worry about it, since after attentively reading this post, filtering out horizontal data from a list with many values will be a piece of cake for you.

So let’s go into the article and solve this problem.

filter horizontal data1

General Formula


The Following formula would help you Filter the horizontal data in MS Excel:

=FILTER(data_range,criteria)

 Explanation of Syntax


Before we go into the formula for getting the job done quickly, we need to understand each syntax to comprehend how each syntax helps filter out the horizontal data in MS Excel.

  • Filter: This function contributes to narrowing down or filtering out a range of data on the user-defined criteria.
  • data_range: In your worksheet, it represents the input ranges.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • criteria: the criteria on which you want to collect or filter the data.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and separate them from the rest of the elements.

Let’s See How This Formula Works


For understanding easily that how this formula works and how to use this formula, Suppose that you got a task in which three horizontal rows have multiple columns, each horizontal row have different types of data; now you want to filter out data from these horizontal rows on a certain logic or criteria.

As to filter the horizontal data on a certain logic, we would write the formula according to the given list like:

 =FILTER(data_range,region="West")

 

filter horizontal data1

Here in this formula, the named ranges are data_range (B1:J3) and region (B2:J2)

Please remember that FILTER is a new dynamic array function in Excel 365. There are equivalents in other versions of Excel, but they are more Sophisticated.

The primary goal is to filter this horizontal data to extract only columns (records) with the region “West“.

The FILTER function can extract data that is arranged vertically (in rows) or horizontally (in columns). The matching data will be returned in the same orientation by the FILTER function.

There is no special setup required. The formula in A8 in the example shown is:

=FILTER(data_range,region="West")

The included argument for FILTER is a logical expression that works from the inside out:

=region="West"/ look for the word "West "

When the logical expression is evaluated, it yields an array of ten TRUE and FALSE values:

 {FALSE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE}

filter horizontal data1

It’s worth noting that the commas (,) in this array indicate columns. Rows would be indicated by semicolons (;).

The array has one value for each column of data, and each TRUE corresponds to a column with the region “ West” .

This array is passed straight to FILTER as the included parameter, and it conducts the filtering of the data:

=FILTER(data_range,{ FALSE,TRUE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE })

filter horizontal data1

Because the filter only accepts data corresponding to TRUE values, FILTER delivers the 5 columns where the region is “ West“.

This data is returned by FILTER in its original horizontal layout.

Transpose Filter Results


If you want to transpose the filter results into a vertical  format, you can use the TRANSPOSE function around the FILTER function like follows:

=TRANSPOSE(FILTER(data_range,region="West"))

The result is as follows:

filter horizontal data1

Filter Data by Another Column


The same fundamental method may be used to filter data in a variety of ways. For instance, if you want to filter out data to show only columns with sales greater than 600, just use the following formula based on the FILTER function:

=FILTER(data_range,B3:J3<600)

 filter horizontal data1

Related Functions


  • Excel Filter function
    The Excel  FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
  • Excel TRANSPOSE function
    Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel.The Excel TRANSPOSE Function syntax:=TRANSLATE (range) …

 

Related Posts

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the ...

Sort/Rank Numeric Values with Duplicate Values Exist

Excel built-in RANK function can sort a set of values. If there are duplicate numbers, then the rank number is also duplicated. See the following example: There are two numbers “100” in range A2:A9, they are both the third largest ...

Calculate Days Open in Excel

If you want to know how to Calculate days in Excel, there are some formulas that you can use to do so. For example, you can use the DAYS function in Excel to find the number of days between two ...

Calculate Grades With VLookup in Excel

Why Should You Calculate Grades With VLookup Excel? If you're looking for a simple way to find out a student's grades, VLOOKUP Excel can do the trick. This function uses a lookup table to find the values and sort them ...

Repeating Character n Times in Excel

In daily life, we can use repeated characters to indicate the magnitude or priority of something. For example, a single “!” indicates a minor case, double “!!” indicates a medium case, and triple “!!!” indicates a high case, and so ...

Sidebar