How Do I Change Data in Vertical Columns to Horizontal Columns in Excel or Access?

Written by william nagel
  • Share
  • Tweet
  • Share
  • Email

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:
Moderate

Other People Are Reading

Things you need

  • Microsoft Excel
  • Microsoft Access

Show MoreHide

Instructions

  1. 1

    Highlight the columns you want to transpose into rows. Press “Control + C” to copy the data.

  2. 2

    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.”

  3. 3

    Right-click on cell “A1” to open the context menu. Select “Paste Special” from the menu to open the “Paste Special” dialogue.

  4. 4

    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.

  1. 1

    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.”

  2. 2

    Give your table a name and select the version of Excel installed on your computer from the dialogue box. Click “Export.”

  3. 3

    Open your exported file in Microsoft Excel. Follow Steps 1-4 of “Transposing Data in Excel.” Close Microsoft Excel.

  4. 4

    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.”

  5. 5

    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.”

  6. 6

    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.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.