Paste Transpose in Excel: A Step-By-Step Guide

PDF download Download Article
PDF download Download Article

In Microsoft Excel, it can sometimes be useful to change the orientation of your data from rows to columns or vice versa. While there are a few different ways to do this, one quick and easy technique is to use paste transpose, which is a basic copy and paste operation. In this article, we’ll explain how to do it in just a few simple steps.

1

Select the data you want to transpose.

PDF download Download Article
  1. You can also select a single cell, hold down the ⇧ Shift key, and then use the arrow keys to select the range using your keyboard. [1]
    • Another option is to click one of the cells in the desired range, then hit ⌘ Command + A (on a Mac) or Ctrl + A (in Windows) to select the entire range.
    2. Advertisement
2

Click Copy on the Home tab.

PDF download Download Article
  1. Then, click the Copy button. You can also use the keyboard shortcut ⌘ Command + C on a Mac or Ctrl + C in Windows. [2]
    • Make sure you use the Copy command rather than the Cut command. Otherwise, you won’t be able to paste your data.
3

Select a cell where you’d like to paste the data.

PDF download Download Article
  1. Otherwise, the data you’re pasting will overwrite any data that’s currently occupying those cells. [3]
    • For instance, if you’re moving data from the row A:1-C:1, try clicking on E:2 to start your new column.
    2. Advertisement
4

Open the Paste menu.

PDF download Download Article
  1. Then, locate the Paste icon in the top left corner of the menu. It looks like a clipboard with a blank rectangle overlapping it. Click the down arrow next to the Paste icon to open a dropdown menu of paste options. [4]
    • Alternatively, right-click the top left portion of the cell where you want to paste your data to open a context menu. [5] Or, if you’re on a Mac, ^ Control -click on the cell instead.
5

Click Transpose .

PDF download Download Article
  1. This will paste your data to the new location you selected and automatically rotate it. That is, if your original data range was in rows, it will now appear as columns, and vice versa. [6]
    • Depending on which version of Excel you’re using, you may see the Transpose option as an icon that looks like a clipboard with 2 perpendicular blue and white rectangles connected by a curved arrow. [7]
    2. Advertisement
6

Delete the data in the original range if you like (optional).

PDF download Download Article
  1. If you don’t need the data in both places, select the original range and delete it. [8]
7

Make sure any formulas you transpose use absolute references.

PDF download Download Article
  1. If you have any formulas in your range that are linked to data located in other cells (relative references), the results may be skewed when you transpose the data. This is because the reference cell will shift when you move the data, so the formula will be drawing its data from a different location in the spreadsheet. [9] To prevent this from happening: [10]
    • Add a dollar sign before the column and row references in each formula. For instance, if a cell contains the formula =A2+B3 , change it to =$A$2+$B$3 to lock the reference and change the formula from relative to absolute.
    • Add a dollar sign to just the row or just the column reference to create a “mixed” reference that is partially relative and partially absolute. For instance, =$A3 will lock the reference to column A, but not to row 3.
    2. Advertisement
8

Convert tables to ranges before using the paste transpose feature.

PDF download Download Article
  1. Tables won’t rotate properly using paste transpose. If you want to keep the data in your tables, you’ll have to convert them into ranges first. Select the table, then navigate to the Table tab in the ribbon menu. Click Convert to Range , then click Yes in the pop-up to confirm. [11]
9

Use the TRANSPOSE function as an alternative to paste transpose.

PDF download Download Article
  1. To use the TRANSPOSE function: [12]
    • Select a range of blank cells where you’d like your data to go, making sure it’s the same number of cells as the original range.
    • In the first cell of your new range, type =TRANSPOSE().
    • Insert the original data range (e.g., A1:B6) in the parentheses.
    • Hit Ctrl + ⇧ Shift + ↵ Enter . Your data, including any tables, will appear in the new range you selected.
    2. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      You Might Also Like

      How to
      Add the Google Play Store to an Amazon Fire Tablet
      How to
      Play Minesweeper
      How to
      Open EPUB Files
      Using Rufus to Create Bootable USB Drives: A Step-By-Step Guide
      A Step-by-Step Guide to Compare Two Excel Files
      How to
      Save an Animation in Blender
      How to
      Make an Exe File
      How to
      Add a Password to a RAR File
      2 Simple Ways to Create an RSS Feed
      How to
      Turn on Microsoft Defender in Windows 10 and 11
      How to
      Change the Hostname on Linux Without Rebooting
      How to
      Edit DLL Files
      How to
      Crack Software by Modifying DLL Files
      How to
      Run a .Jar Java File
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/select-cell-contents-in-excel-23f64223-2b6b-453a-8688-248355f10fa9
      2. ↑ https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
      3. ↑ https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
      4. ↑ https://support.microsoft.com/en-us/office/transpose-data-from-rows-to-columns-or-vice-versa-in-excel-for-mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede
      5. ↑ https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
      6. ↑ https://support.microsoft.com/en-us/office/transpose-data-from-rows-to-columns-or-vice-versa-in-excel-for-mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede
      7. ↑ https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
      8. ↑ https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744
      9. ↑ https://support.microsoft.com/en-us/office/transpose-data-from-rows-to-columns-or-vice-versa-in-excel-for-mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede
      1. ↑ https://support.microsoft.com/en-us/office/switch-between-relative-and-absolute-references-981f5871-7864-42cc-b3f0-41ffa10cc6fc
      2. ↑ https://support.microsoft.com/en-us/office/transpose-data-from-rows-to-columns-or-vice-versa-in-excel-for-mac-9c16dd55-ed1a-4aa2-8b74-b1b9211e2ede
      3. ↑ https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027
      4. ↑ https://support.microsoft.com/en-us/office/transpose-rotate-data-from-rows-to-columns-or-vice-versa-3419f2e3-beab-4318-aae5-d0f862209744

      About This Article

      Thanks to all authors for creating a page that has been read 11,698 times.

      Is this article up to date?

      Advertisement