How to Transpose Data in Excel?

A comprehensive guide to Excel data transposition using three different methods: Paste Special, TRANSPOSE function, and blank cell preservation.

2 min readExcel Guide
Contents
  1. Method 1: Paste Special Transpose
  2. Method 2: TRANSPOSE Function
  3. Transpose Data Without Zeros

Transposing data in Excel means switching rows to columns or vice versa, which can be useful when rearranging data for analysis or presentation. Here are three quick methods for transposing data in Excel, from using paste options to formulas and advanced tools.

Method 1: Paste Special Transpose

This is the quickest way to transpose data in Excel. Here’s how to do it:

  • Select the Data Range: Highlight the cells you want to transpose.
Excel Region, Month and Sales table in A1:C7 selected and copied, marked by a dashed border, ready to transpose.
  • Copy the Data: Go to Home > Copy or right-click and select Copy.
  • Select a New Location: Click on the cell where you want the transposed data to begin.
  • Open Paste Special: Right-click, select Paste Special, or go to Home > Clipboard > Paste > Paste Special.
Excel Home tab Paste menu expanded to show Paste Special, with the copied data outlined and a destination cell selected.
  • Choose Transpose: In the Paste Special dialog box, check the Transpose box and click OK.
Excel Paste Special dialog box with the Transpose checkbox ticked, Values selected under Paste, and OK ready to click.

The selected data will now be pasted in transposed form at your chosen location, with rows converted to columns and vice versa.

Excel Region, Month and Sales columns from A1:C7 pasted as rows in F5:L7 after a Paste Special transpose.

Method 2: TRANSPOSE Function

Excel’s TRANSPOSE function dynamically transposes data and keeps it updated if the source data changes.

Syntax: =TRANSPOSE(array)

Steps:

  • Select Output Range: Choose the range where you want the transposed data to appear. Ensure it has the same dimensions as the original data, but flipped (e.g., if your data is 5x6, select a 6x5 range).
  • Enter the TRANSPOSE Formula: Type =TRANSPOSE(A1:F5) (adjust the cell range to match your data).
Excel formula bar showing =TRANSPOSE(A1:C7), with the Region, Month and Sales table flipped into rows at J6:P8.
  • Execute as an Array Formula: Instead of pressing Enter, press Ctrl+Shift+Enter to complete the array formula. Excel will display the formula with curly braces { }, indicating it’s an array formula.

The data will automatically transpose. Note that using this function replaces blank cells with zeros. For blank cells, see the next section.

Transpose Data Without Zeros

If you need to keep blank cells blank when transposing, use the IF function with TRANSPOSE.

  • Enter the Formula: Use =TRANSPOSE(IF(A1:F5="", "", A1:F5)) to maintain blank cells in the transposed data.
Excel TRANSPOSE and IF array formula in the formula bar, keeping the blank Month cell for East empty in the transposed rows.
  • Execute as an Array Formula: Press Ctrl+Shift+Enter to run the array formula, and Excel will transpose the data while preserving blank cells.

Understand data transposition to enhance your Excel workflow efficiency.

Let’s make AP
work for you.