Alternatively you could record/write some VBA code to do this, but VBA is not my kind of thing. You can also use the shortcut key Ctrl+C (Command+C on the Mac). Choose a new location in the worksheet where you want to paste the transposed table, ensuring that there is plenty of room to paste your data. Note: Ensure that you copy the data to do this, since using the Cut command or Ctrl+X won’t work. Step 2: On the Home tab, in the clipboard group, click Copy. Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C. Step 1: Select the data set you want to transpose. Once you have upgraded to a newer version and if this is going to be recurring task you may want to look into Power Query to automate this kind of transformation. Take a look at the images below and follow the steps below to convert the data in columns to row data and vice versa. Thus, everything shifts one column to the right and an empty column A appears.ģ) In A4 enter the formula =B3 and copy it all the way down.Ĥ) Select entire column A, Copy and paste values onto itselfĪt this stage you should have something like this:Ħ) Press the filter button in B2 and select only the dates.ħ) Select all visible rows with dates and delete the rows a date then one row, then a date and again one row etc.ġ) Insert a column to the left of the data. I've assumed that the data is organised for all 500 row as your picture show. This is a on-time task, I would do it manually.

Change from column to row in excel