Transpose table without zeros

Transpose table without zeros
Example

Related Functions

=TRANSPOSE(IF(rng="","",rng))

To dynamically transpose a table that contains blanks, you can use an array formula that uses the TRANSPOSE and IF functions.

In the example shown, the multi-cell array formula in I9 is:

=TRANSPOSE(IF(B5:F6=””,””,B5:F6))

Note: this is an array formula that must be entered with Control + Shift + Enter across the entire range H5:I9.

How this formula works

The TRANSPOSE function automatically transposes values in a horizontal orientation to vertical orientation and vice versa.

However, if a source cell is blank (empty) TRANSPOSE will output a zero. To fix that problem, this formula contains an IF function that checks first to see if a cell is blank or not. When a cell is blank, the IF function supplied an empty string (“”) to transpose. If not, IF supplies the value normally.

Without IF, the array going into TRANSPOSE looks like this:

{“Item”,”grapes”,”lemons”,”cherries”,0;”Qty”,24,30,14,0}

After IF, it looks like this:

{“Item”,”grapes“,”lemons“,”cherries“,””;”Qty”,24,30,14,””}

0 votes. 0 / 5

Excel - Excel Functions - Excel Formulas
Logo