Excel is a spreadsheet programme that lets you organise information in columns and rows and add mathematical formulas to it. It has a lot of different functions. Changing a column to a row can help professionals organise large amounts of data or change how a graph is set up. If you know how this works, you will be able to use the software better. This article shows you how to convert rows to columns in Excel.
How to convert rows to columns in Excel
Convert rows to columns in Excel using Paste Special
Let us say you have a set of data that looks like the top part of the image below. The names of the countries are in columns, but the list of countries is too long, so we should turn the columns into rows so that the table can fit on the screen. Follow these steps to switch rows to columns:
- Choose the first data. Press Ctrl + Home and then Ctrl + Shift + End to quickly select the whole table or all the data-filled cells in a spreadsheet.
- You can copy the selected cells by right-clicking the selection and choosing Copy from the context menu, or by pressing Ctrl + C.
- Choose the first cell in the range you want to go to.
- Make sure to choose a cell that is outside of the range where your original data is, so that the areas where you copy and where you paste do not overlap. For instance, if you have 10 rows and 4 columns now, the new table will have 10 rows and 4 columns.
- Right-click on the cell you want to move to, choose “Paste Special” from the menu that appears, and then choose “Transpose.”
As you just saw, the Paste Special feature lets you change rows to columns or columns to rows in a matter of seconds. This method also copies the way the original data was formatted, which is another point in its favour.
How to use macro to convert row to column
After adding the macro to your workbook, follow the steps below to rotate your table:
- Open the target worksheet, press Alt + F8, choose the TransposeColumnsRows macro, and click Run.
- Click OK after you have chosen the range where you want to switch rows and columns.
- Choose the upper left cell of the range you want to send the data to and click OK.
Why convert Excel columns to rows?
Depending on the size of the data set you are working with, you may need a certain type of table or to organise the information horizontally in rows to save space. When using rows to organise, users can add as many columns as they need, and they can add more horizontal rows to split the columns into sections.
You could use math formulas on a set of data, make a new table or a copy of an existing one, or format the data for a different device. For example, a computer monitor usually displays information horizontally, but a phone or tablet might display the same information vertically.
FAQ
How do I move alternate rows to columns in Excel?
Choose a cell next to your data, type =IF(ISEVEN(ROW(B2)),B2,”,” where B2 is the data you want, press the Enter key, and then drag the auto fill to fill the range you want to use this formula for. Now, data from every other row has been put in this column.
How do I split multiple rows into multiple columns?
Click in a cell or choose more than one cell to split. On the Layout tab, in the Merge group, click Split Cells under Table Tools. Enter the number of columns or rows you want the selected cells to be split into.
How to make columns in Excel?
To add a single column, right-click the whole column to the right of where you want to add the new column and choose Insert Columns. To add multiple columns, select the same number of columns to the right of where you want to add new ones. Right-click the selection, then choose Insert Columns from the menu.
What is the best practice to switch the columns and rows in a range?
One way is to click and drag from the first cell in your data range’s column to the last cell in the row farthest away. To highlight everything in between, you can also click on the first data cell and then shift-click on the last cell.
