We have 2 rows and 8 columns, therefore, we should highlight 16 cells as shown in figure 3.We also type in the quantities for each item in the rows from Cell B6 to Cell H6įigure 2 – Setting up the Data Applying the TRANSPOSE and IF function.

We will type the items in the rows from Cell B5 to Cell H5.

Our objective is to use the TRANSPOSE and IF functions to flip the table into a vertical orientation.

We will prepare a spreadsheet in a horizontal orientation. The steps below will walk through the process.įigure 1- How to convert rows to columns in a table without zeros using the TRANSPOSE and IF function General Formula This video covers the basics of Paste Special.We can use the TRANSPOSE function in combination with the IF Function to flip the orientation of a given range without having the zero snag. However if you only need a one-time conversion, you can use Paste Special with the Transpose option. The TRANSPOSE function makes sense when you need a dynamic solution that will continue to update when source data changes.

Confirm the formula as an array formula with control + shift + enter.

Enter the TRANSPOSE function, and select the source range as the array argument.

First select the target range, which should have the same number of rows as the source range has columns, and the same number of columns as the source range has rows.However, in other versions of of Excel, TRANSPOSE must be entered as a multi-cell array formula with control + shift + enter: In Excel 365, which supports dynamic array formulas, no special syntax is required, TRANSPOSE simply works and results spill into destination cells automatically. = TRANSPOSE (( XLOOKUP (value ,lookup_range ,return_range ))

Transpose button in excel does what