If your data set has blank cells and you use the transpose function to convert columns to rows, in the resulting data set you would get zeros instead of the blank cells. To remove the SPILL error and get the result, just delete the filled cell that’s in the way of the result of the Transpose function Blank Cells in the Original Data Are Converted to 0

In case any cell that is supposed to get the data from the transpose function is already filled, you will see the SPILL error as the result of the formula. When you use the TRANSPOSE function to convert columns to rows, you need to make sure that it has enough empty cells so that the resulting data can be accommodated. You can also convert the columns into rows using Excel’s built-in TRANSPOSE function.īelow I have a dataset that I want to transpose.Ī couple of important things to know when using the TRANSPOSE function to convert columns to rows in Excel: Transpose Function Can Give SPILL Error Also read: Transpose in Excel (Shortcuts) Method 4: Convert Columns into Rows using the TRANSPOSE Function

The above steps would instantly paste the data while also transposing it.ĪLT + E + S is the keyboard shortcut to open the Paste Special dialog box, and when you also hit the E key after ALT + E + S, it checks the Transpose option in the dialog box.

Use the above keyboard shortcut ALT + E + S + E + Enter.

Copy the data by right-clicking and then clicking on Copy or by using the keyboard shortcut Control + C (or Command + C if using a Mac OS).

Select the data that you want to transpose.

Here is how to use this keyboard shortcut: Here is the keyboard shortcut: ALT + E + S + E + Enter You can also use a simple keyboard shortcut to quickly convert columns into rows using Paste Special dialog box. Also read: How to Swap Columns in Excel? Method 3: Convert Columns to Rows using Paste Special Similarly, you can also use this method to convert rows into columns as well. For instance, if you applied the blue background color to all the regions, the same formatting will be applied to the converted data. Your formatting will remain the same when using this method.

Transform columns to rows in excel