EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel TRANSPOSE Formula
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (36+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Excel TRANSPOSE Formula

By Madhuri ThakurMadhuri Thakur

TRANSPOSE Formula in Excel

Excel TRANSPOSE Formula (Table of Contents)

  • TRANSPOSE Formula in Excel
  • How to Use the TRANSPOSE Formula in Excel?

TRANSPOSE Formula in Excel

Sometimes the data is not available in the manner we want. For example, it may be due to the system generating a report, or some people like to see the information differently than you prefer. One example is when the information is available horizontally, but you want to see the information vertically or vice-versa.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Microsoft Excel has an inbuilt function to tackle this issue known as “Transpose.” Transpose helps us to re-arrange the information in the manner we want. For example, it helps us to swap or switch columns to rows without re-typing the information. There are different ways to use the Transpose Formula in Excel. Either you can use this with the Copy-Paste function, or you can use the Transpose Formula.

How to Use TRANSPOSE Formula in Excel?

TRANSPOSE Formula in Excel is very simple and easy to use. Let’s understand how to use the TRANSPOSE Formula in Excel with the help of some examples.

You can download this TRANSPOSE Formula Excel Template here – TRANSPOSE Formula Excel Template

Let’s look at the below example where I want to switch the information from Horizontal to Vertical.

Example #1

In this spreadsheet, I have given the cost and installation charges of some Electric appliances. The information is available in Horizontal Manner. So I have to change the report orientation from Horizontal to vertical.

TRANSPOSE Formula in Excel output 1

Now to switch or swap the information from Horizontal to Vertical, we will need to follow the below steps.

Related Courses

Excel Training (21 Courses, 9+ Projects)

  • Select the range of information that you need to swap or switch. In this example, A1:F4.

TRANSPOSE Formula in Excel output 2

  • Copy the Table (CTRL+C) and go to another sheet (or anywhere in the excel sheet where you want the information) and Paste special values (CTRL+ALT+V).
  • After the special paste values, you will see the below options.

TRANSPOSE Formula in Excel output 3

  • As you can see, there are multiple options for Paste & Operation. Also, there is an additional option for Skip Blanks and Transpose. In the paste area, there are two important options (Values and Formulas). Select Values if you just want to copy the values of data and select Formulas if you want to copy the data with Formulas.
  • In this case, I am selecting Formulas because I need the data with formulas. So click on the Formula button.

TRANSPOSE Formula in Excel output 4

  • In addition to the formula button, click on the Transpose button as well. So that it can paste the data with formulas, it can swap the information from Horizontal to vertical and then click on OK.

TRANSPOSE Formula in Excel output 5

  • Now you will see the information pasted vertically.

TRANSPOSE Formula in Excel output 6

Introduction to Transpose Function

Now we have seen how to swap orientation with the help of the copy-paste feature, but there is a risk of creating duplicated data. So to avoid that, we can use the TRANSPOSE function in excel. You will need to be a little careful while using the function.

The Transpose function must be entered as an array formula in a range with the same number of rows and columns as in source data.

The values will also change in the Transpose Formula whenever the source information changes. This is the main difference between using the Transpose function and the Transpose with copy-paste feature. In the copy-paste feature, the values will not change if the source information changes. So Transpose function is more useful than the copy-paste feature.

The syntax for the transpose Formula is:

TRANSPOSE Formula in Excel output 7

Example #2

We will see the same example above and try to use the TRANSPOSE formula in Excel.

TRANSPOSE Formula in Excel output 8

Please follow the below steps to use this function.

  • You must first select some blank cells in the other direction as the original set of cells and select the same number of cells. In our example, we need to select 24 cells in the opposite direction. As you can see in the below screenshot, I have selected 24 blank cells in the Vertical direction.

TRANSPOSE Formula in Excel output 9

  • Now be careful with this step because you need to insert the TRANSPOSE Formula in Cell A6 while all the blank cells are still selected. Please refer to the below screenshot on how to do this.

output 10

  • The next step is to select the range of the original cells for which you need to change the orientation. In our example, the range is A1:F4. After selecting the range, don’t press ENTER because ENTER will not work with this function.

output 11

  • So the last step in this function is to press CTRL + SHIFT + ENTER. The reason for doing this is because TRANSPOSE is an array function, and this is how an array function ends and not by pressing ENTER.  An array is a function that can perform multiple calculations on one or more items in a cell.
  • After hitting the CTRL+SHIFT+ENTER, you will see the result below.

output 12

TRANSPOSE function with IF Condition

The transpose function can be used with other functions to get the desired results. So let’s try to use this with the IF Condition formula.

Example #3

Suppose you have a report card of a few students available in the Horizontal orientation. For students who score below 50 or are absent, you want to transpose the information to be marked as Fail.

output 13

So we can use transpose Formula with IF condition like below.

=TRANSPOSE(IF(A1:F3<50,”FAIL”,A1:F3))

output 14

But make sure not to press ENTER and press CTRL+SHIFT+ENTER.

So after hitting the CTRL+SHIFT+ENTER, you can see the result below.

output 15

As you can see, Raj and Vikas scored less than 50, so they are marked as FAIL.

Things to Remember

  • Make sure you select the same number of blank cells as the number of cells in source information while using the Transpose Formula.
  • Ensure you do not press ENTER after inserting the Transpose Formula because the Transpose function is an Array function. Instead, you should press CTRL+SHIFT+ENTER.
  • You can use the copy and paste feature for Transpose, which creates duplicate data. So when the values in source information change, the values are not updated.

Recommended Articles

This is a guide to the TRANSPOSE Formula in Excel. Here we have discussed how to calculate TRANSPOSE Formula in Excel, practical examples, and a  downloadable excel template. You may also look at the following articles to learn more –

  1. SUMPRODUCT Formula in Excel
  2. COUNTIF Formula in Excel
  3. Frequency Formula in Excel
  4. Excel Formula For Rank
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

Download TRANSPOSE Formula Excel Template

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download TRANSPOSE Formula Excel Template

EDUCBA

डाउनलोड TRANSPOSE Formula Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more