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.

Generic formula



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:


After IF, it looks like this:

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.