Home » Posts

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: {=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, except in Excel 365.

April 14, 2025 · 1 min · 61 words · Christopher Mcgee

Table of Contents

Explanation

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.

When a cell is blank, the IF function supplied anempty string("") to transpose.

Excel TRANSPOSE function

If not, IF supplies the value normally.

More than one condition can be tested by nesting IF functions.

Excel IF function

Explanation#

Explanation