|

How to Switch Rows and Columns in Excel (the Easy Way)

If you want to rearrange your data from rows to columns or vice versa (rotate or transpose it), it is very easy to do so in Excel. Just use the TRANSPOSE feature, let me show you how to convert columns to rows in excel.

how to convert columns to rows in excel

There are 2 methods you can use to switch between rows and columns. If you want to do it just once, you can use the static method #1.

If you want a dynamic switching between them, use the TRANSPOSE function – method #2. What the function does is basically it links the new transposed table to the original table. Whenever you change the data in the original table, the change will be reflected in the new table as well.

Method 1: Transpose (rotate) data from rows to columns or vice versa using paste special

Step 1: Copy data

First, select the data you want to switch from, including all headers or labels and press Ctrl + C

selecting the data to switch between rows and columns

Pro tip: Make sure you use the COPY command, as using CUT won’t work

Step 2: Paste to a new location

Choose where you want your new data pasted. Make sure you have enough place to fit the whole table as any old data will be overridden.

You can now either right click on the top left cell and click “transpose” either here

transpose excel columns to rows

or like so

using paste special to switch excel columns to rows

OR

Right click and press ALT + E+ S. This will call the Paste special dialog box.

paste using the transpose option to excel convert columns to rows

Mark transpose on the bottom right corner and click OK

The table is now transposed and the columns are switched with rows. If you want to, you can now delete the original table and the data in the new table will remain intact.

TIPS:

  • If you are using any formulas, make sure you are using absolute references because Excel will automatically update them to match the new placement.
  • If you want to change your data by dragging fields from rows to columns or the other way around frequently, use a pivot table
  • Use the copy/paste (static method) if you want to switch between rows and columns just once. Use the dynamic method if you need both views and if you want the new table to reflect any changes you make to the original table

Method 2: Switching rows to columns using the TRANSPOSE function

Transpose function quickly converts rows to columns and vice versa. The syntax of the transpose function is:

transpose function syntax - an easy way to switch columns to rows and vice versa

Where array is a range of cells or a range of values that you want to transpose (change the orientation)

To use the transpose function, count the number of rows and the number of columns in your original table. There are 2 ways you can do this – either conting them one by one or by selecting the whole table you want to transpose and noting the values in the upper left corner (highlighted in yellow below). In our case we have 6 rows and 5 columns.

counting the number of rows and columns so you can easily convert from columns to rows or vice versa

Now you need to select the same number of blank cells as the original set of cells but in the other directions. In our case we need to select 5 rows and 6 columns. This place is where the new set of transposed cells will end up.

While the cells are still selected type =TRANSPOSE(

DO NOT PRESS ENTER YET. Just continue with the next step

switching rows to columns - the transpose function in action

Now select the original set of cells like so. As you see the new place is still selected.

Now, press CTRL+SHIFT+ENTER.

Why you may ask! This is because TRANSPOSE function uses an array formula that is a special formula that gets applied to more than one cell at a time and this is the way you enter array formulas – not just pressing enter, but pressing Ctrl+shift+enter simultaneously.

NOTE: If you just press enter you will get a #VALUE error and you will need to start all over again. Do not do that! You have been warned!

So, here’s the final result:

quickly convert columns to rows using the transpose function - showing the final result

TIPS how to convert columns to rows in Excel:

  • Using the TRANSPOSE FUNCTION will ensure the new table updates every time you update your original set of cells.
  • Using the TRANSPOSE function will only convert rows to columns or vice versa, but will not keep the original text or cells formatting. If you really need to keep the formatting you can try copy+paste and using the transpose option – it will rotate the data but you will lose the ability to update the new table every time your original one changes.
  • Getting #VALUE error can be due to either 1) not selecting the right number of cells or columns that you need for the new table (your new table rows have to be equal to the old table columns, and the new table columns have to be equal to the old table rows) or 2) you have not entered the array formula using pressing CTRL+SHIFT+ENTER

Well, that’s all folks! I hope you learned how to switch rows and columns in Excel quickly. If you know an easier method to convert columns to rows or vice versa, drop in the comments down below and share your method with us!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.