Note: this is an array formula that must be entered with Control + Shift + Enter across the entire range H5:I9, except in Excel 365. 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
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.
