Productivity Portfolio

Valuing your time & technology

  • Home
  • Tutorials
    • Excel
    • Google
    • Word
    • Terms
  • Quick Tips
    • Cell
    • Email
    • Internet of Things
    • Security
    • Web
    • Windows
  • Reviews
    • Health
    • Hardware
    • Services
    • Software Reviews
  • About
    • About Us
    • Contact Page
    • Cookie Policy
    • Privacy Policy
  • Newsletter
  • Resources
    • Books
    • Favorite Podcasts
    • Gadgets
    • Online Courses
    • Online Services
You are here: Home / Tutorials / Excel / How to Switch Excel Columns and Rows

How to Switch Excel Columns and Rows

Last Updated on 26-Mar-2018 by Anne Hennegar

Everyone looks at information in a slightly different way. Some people create Excel spreadsheets where the data is useful, but it doesn’t work with the way you view or interact with the data. One common example is where you want to swap or switch Excel row or column data so Row data becomes Column data and so on. There are several ways to transpose the information, but there is one simple method that doesn’t require any Excel functions. Includes sample spreadsheet.

I recently was given a large Microsoft Excel spreadsheet that contained vendor evaluation information. The information was useful, but because of the way the data was organized, I couldn’t use tools like Auto Filter. I would also have issues if I needed to import the information into a database. A simple example of such a spreadsheet is shown below.

initial Excel sheet before column swap

Excel Spreadsheet Needing to Swap Columns & Rows

Instead, I want to have the Company names display vertically in Column A and the Data Attributes display horizontally in Row 1. This would make it easier for me to do comparisons and analysis. For example, I can’t easily filter the spreadsheet for vendors in CA. I also didn’t want to re-enter the data. The fix is rather easy using Excel’s transpose feature.

To Swap Excel columns to Rows,

  1. Open the spreadsheet you need to change. You may also download the example sheet at the end of this tutorial.
  2. Insert a blank worksheet.
  3. Click the first cell of your data range such as A1.
  4. Shift-click the last cell of the range. Your selection should highlight.
  5. From the Home tab, select Copy.
  6. copy existing Excel data

    Copying the current Excel data

  7. At the bottom of the page, click the tab for the blank worksheet such as Sheet2.
  8. In the blank worksheet, click cell A1.
  9. From the Home tab, select Paste. The Paste dialog should appear.
  10. Excel Transpose command

    Selecting Transpose icon to Swap Column

    As you hover over the paste options, you can see that effect display.

  11. Click the box for Transpose.

Once you transpose the data, your company names will show in Column A. I can now sort or filter the data in many ways.

Excel file with swapped columns and rows

End Result After Swapping Excel Columns

Note: For older versions of Excel, the Copy & Paste commands are on the Edit menu.

Why Did I Use Another Worksheet?

Some of you may be wondering why I opted to create a new spreadsheet. The reason is if I tried to paste my swapped columns and rows into cell A1, Excel would give me an error.

Excel paste error

Excel Error When I Try to Paste Data

I’m using a different size because my columns and rows are swapped. By using a new worksheet, I avoided this error. Alternatively, I could’ve selected a blank cell down on the same sheet.

Additional Resources

Example Practice Sheet to Swap Columns & Rows

Want More Tips & Tools?

Several times a month, we also publish a free newsletter with relevant stories, tips and special offers.

Thank you. You should receive an email shortly from [email protected] asking you to confirm your subscription. If you're a Gmail user, the email may show under your Promotions or Updates tab. We apologize for the extra step, but we do it for security reasons.

There was an error submitting your subscription. Please try again.

Powered by ConvertKit

Filed Under: Excel, Tutorials




Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages
Filter by Categories
About
Cell
Email
Excel
Featured
Google
Hardware
Health
Internet of Things
Product & Service Reviews
Quick Tips
Screencasts
Security
Services
Software Reviews
Terms
Tutorials
Web
Windows
Word

Click for More results...

SPONSORED ADS

Free Excel Dashboard Webinar

300x250







© 2018 · Productivity Portfolio | PO BOX 117361 Burlingame, CA 94011