When working with spreadsheets in Microsoft Excel or Microsoft Access, you can rearrange the data being moved. Microsoft Excel includes a special “Paste” dialogue that will automatically transpose data from columns to rows. You can transpose data from a spreadsheet in Access by exporting the data to Excel and then importing it back into a new table in Access.
- Skill level:
Other People Are Reading
Things you need
- Microsoft Excel
- Microsoft Access
Highlight the columns you want to transpose into rows. Press “Control + C” to copy the data.
Open a new worksheet for the transposed data. Select an empty worksheet tab from the bottom of the Excel window, or press “Shift + F11” to create a new worksheet. Rename the worksheet “Transposed Data.”
Right-click on cell “A1” to open the context menu. Select “Paste Special” from the menu to open the “Paste Special” dialogue.
Check the “Transpose” box at the bottom of the “Paste Special” dialogue to transpose the data. Click “OK.” Your data will be pasted into the spreadsheet as rows. Save your workbook.
Open the table you want to transpose. Select “Save As/Export” from the “File” menu. Select “To an External File or Database” in the "Save As" window and click “OK.”
Give your table a name and select the version of Excel installed on your computer from the dialogue box. Click “Export.”
Open your exported file in Microsoft Excel. Follow Steps 1-4 of “Transposing Data in Excel.” Close Microsoft Excel.
Import the transposed data into Microsoft Access. Select “Import” from the “File” menu. Use the “Files of Type” drop-down list and select “Microsoft Excel.”
Locate and select the file that contains the transposed data and click “Import.” Click “Show Worksheets” on the first screen of the Import Wizard and select the “Transposed Data” worksheet. Click “Next.”
Do not select the “First Row Contains Column Headings” option in the second screen of the Import Wizard; click “Next.” Check the “No Primary Key” option on the third screen of the Import Wizard and click “Finish” to import your data into a new table.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for