Transpose table without zeros
To dynamically transpose a table that contains blanks, you can use an array formula based on the TRANSPOSE function and IF function. In the example shown, the multi-cell array formula in H5:I9 is:
Note: this is an array formula that must be entered with Control + Shift + Enter across the entire range H5:I9, except in Excel 365.
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,""}
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
