Filter And Transpose Data From Horizontal To Vertical

This post will show you how to use Filter function and in combination with Transpose function to filter data from horizontal and transpose data as vertical in Microsoft Excel.

You can refer to the below general formula based on TRANSPOSE and FILTER function:

=TRANSPOSE(FILTER(range,logic))

You can use the FILTER with TRANSPOSE function to filter data horizontally and show the result in a vertical format. In the below example, the formula in A8 is used:

=TRANSPOSE(FILTER($B$1:$J$3,$B$1:$J$1=2))

Clarification


This task will filter the horizontal data in range $B$1:$J$3, and display results transposed to a vertical format. We will work with both the data range ($B$1:$J$3) and Class ID range ($B$1:$J$1) in this example.

Filters are one of the most useful functions for organizing your data. You can use them to extract specific pieces or ranges, and they’ll return only what you want! For example, the formula in A8 is:

=TRANSPOSE(FILTER($B$1:$J$3,$B$1:$J$1=2))

The argument includes filter can be used to check for the existence of a file or files. This means it will only return true when the input string matches one or more pattern(s). The syntax looks like this:

$B$1:$J$1=2

The output is an array of 9 TRUE and FALSE values.

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

filter and transpose horizontal to vertical1

The input array contains one value per record in the data, and each TRUE corresponds to a “2” Class ID column. This returning value is then used by FILTER as an argument for its filtering:

FILTER($B$1:$L$3,{FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE, FALSE })

filter and transpose horizontal to vertical1

By only passing through columns that correspond with TRUE, the result is a set of data for those three people in  Class 2. FILTER gives us back this original format but displays it vertically instead! The TRANSPOSE function was included so we can see they’re displayed properly.

=TRANSPOSE(FILTER($B$1:$J$3,$B$1:$J$1=2))

filter and transpose horizontal to vertical1

By using the TRANSPOSE function, we can transform our data into a vertical array. This means that when there is a variation in any of cells B1 through J3 (or their values), all filter results will be updated automatically!

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
Repeating Character n Times in Excel1

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 ...

Calculate Cumulative Loan Interest in Excel
calculate cumulative loan interest excel1

What is Cumulative Loan Interest? When comparing different types of loans, many people want to know what is Cumulative Loan Interest. The sum of all interest payments you've made on a loan is referred to as cumulative interest. Different lenders ...

Calculate Cumulative Loan Principal Payments in Excel
Calculate Compound loan principal payments excel1

How do you Calculate Cumulative Loan Principal Payments in Excel? There are a few different ways to calculate this information, but one way has been proven to be accurate more often than others. One option is to use the CUMPRINC ...

Trap Error or Replace Error by Specific Value with IFERROR function
Trap Error or Replace Error by Specific Value with IFERROR function1

We often use Excel formulas in our work life, and we may encounter this situation that the formula throws an error and finally an error like #DIV/0! Is displayed in the cell. In today’s tutorial, we will introduce you the ...

Calculate Compound Interest in Excel

This article will show you how to calculate compound interest in Excel. It will help you calculate the interest you will accrue on a given amount of money. There are several ways to calculate compound interest. One of the simplest ...

Count Attendance and Absence with COUNTIF function
Count Attendance and Absence1

In our campus life and work life, we usually record everyone's attendance. Today we will introduce you the application of Excel COUNTIF function to count the attendance. Using a week as an example, we use the following example to show ...

Sidebar