Excel formula: Transpose table without zeros

Generic formula

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

Explanation

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 H5: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","apples","pears","limes",0;"Qty",14,10,4,0}

After IF, it looks like this:

{"Item","apples","pears","limes","";"Qty",14,10,4,""}

Author

Dave Bruns

Related functions

Excel TRANSPOSE Function

The Excel TRANSPOSE function "flips" the orientation of a given range or array. TRANSPOSE converts a vertical range to a horizontal range, or a horizontal range to a vertical range. You must enter the TRANSPOSE function as an array formula.

Excel IF Function

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions....

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.