Exceljet

Quick, clean, and to the point

Transpose table without zeros

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

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.