Save and close the workbook, and then quit Microsoft Excel. If you want, click Save As on the File menu to export the data directly to text from Microsoft Excel.Ĩ. In Excel 365, we can solve those issues using the FILTER function within the TRANSPOSE. Type a name for the sheet that contains the transposed data. There are other methods like Copy->Paste Special->Transpose. So, if you don’t need the original set of data then just delete it. Transpose Data doesn’t have any link with the original data set. On the Format menu, point to Sheet, and then click Rename. Method 1: Transpose using Copy and Paste If the Excel transpose function fails to work then another easier way to transpose data is by using the feature of copy and paste. In the Paste Special dialog box, click to select the Transpose check box, and then click OK.ħ. Press CTRL+SHIFT+END to select all of the data.ĥ. Select the cells you want to transpose, and press Ctrl + H to display Find and Replace dialog. Start Microsoft Excel and open the spreadsheet that you created in step 5 of the "Export the Data to Microsoft Excel" section.Ģ. To transpose and keep reference in Excel, you can apply the Find and Replace function. Does this function only work if the number or rows and number of columns are the same number? Does anyone have any suggestions for me? I greatly appreaciate any help you can provide.ġ. Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot: 3. I have been searching threads for a while and found the instructions listed below many times, however when I get to step 6 and select to save, I always receive the following error, "The information cannot be pasted because the copy area and the paste area are not the same size and shape". Select the data column that you want to combine into one cell.