WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Excel TRANSPOSE Function

Excel TRANSPOSE Function

What Does Transpose Function Do in Excel?

Transpose function in excel is used to rotate the values or switch the values from rows to columns and columns to rows, as this function takes an array of cells as an argument so to execute it we need to press CSE or Control + Shift + Enter, first select the exact size of range which needs to transposed and then use the transpose formula.

Syntax

Below is the TRANSPOSE Formula in excel.

Transpose Formula in Excel

Arguments

array: Required. A range of cells you want to transpose.

It is an array function.

How to use the TRANSPOSE Function in Excel? (with Examples)

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

Example #1

The original data you want to transpose is in A3: B8.

transpose example 1

  • Step 1: Select the range where you want your transposed value, here, D6: I7.

transpose example 1.1

  • Step 2: Now, type the TRANSPOSE Formula in the selected region and press CTRL+SHIFT+ENTER (or COMMAND+SHIFT+ENTER in Mac).

=TRANSPOSE (A3: B7)

transpose example 1-2

You will get the transposed output in D6: I7, as shown below.

Excel transpose function example 1-3

In this, D3: I4 is the copy-paste transposed output, and D6: I7 is by transpose output.

Notice that the Excel TRANSPOSE Function is

{=TRANSPOSE (A3: B7)}

The curly braces { } shown above indicate that the function has been input as an Array Formula.

If you have to change any value in the original data, say B8 = SUM(B3: B7), the transposed output, where you have used the TRANSPOSE, will be automatically updated, but not in the case where you have used the copy-paste option.

excel transpose function example 1-6

It can also be used along with other functions to get the desired output. Let us see few examples of this.

Example #2

Suppose you have a list of students and their marks, as shown below. Some of the students did not give exams. Hence, the marks column is left blank for those students.

Excel TRANSPOSE Function Example 2

You want to transpose this data, and when the cell is empty, you want to put “Absent” in that cell.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

You can do so by using Transpose along with the IF function in excel. You may use the following TRANSPOSE Formula in excel for doing so:

=TRANSPOSE(IF(B3:D10=””,”ABSENT”,B3:D10))

‘IF(B3: D10=” “, “ABSENT”’ means that if any cell in the range B3: D10 is empty, it will place “Absent” instead. It will transpose the given range.

TRANSPOSE Example 2-1

Suppose in this list, if any student has scored less than 70 marks or has not appeared in the exam is considered FAIL, you may change the TRANSPOSE Formula as:

=TRANSPOSE(IF(B3:D10<70,”FAIL”,B3:D10)

and you will get the final output, as shown below.

Excel TRANSPOSE Function Example 2-2

Example #3

Sometimes there is a need to add some characters to the existing ones along with transpose. Suppose you have a list of IDs in B4: B7, as shown below.

TRANSPOSE Example 3

You want to transpose the ids and add the prefix “ID” to it. You can do so using the Formula in excel:

=TRANSPOSE(“ID”&B4:B7)

TRANSPOSE Function Example 3-2

This will transpose the data and add the prefix to each of the cell content.

TRANSPOSE Example 3-3

You may also add any value as a suffix using the above TRANSPOSE Formula. This may be helpful when using the Transpose function to concatenate the words given in different rows into a single cell. To do so, you may follow the following steps:

  • Type the syntax: CONCATENATE(TRANSPOSE(B4:B7&”,”). Do not press Enter.
  • Select the excel transpose function, as shown below.

TRANSPOSE Example 3-4

  • Press the F9 key.

Example 3-5

  • Remove {} from the formula.

Example 3-6

  • Press Enter.

Example 3-7

It will concatenate the cells in a single cell.

Example #4

Suppose you have a library data wherein the several subjects are arranged on a shelf, and there are different shelves as shown below.

Example 4

You may want to retrieve the location of any of the subjects (given in H3:H11) using this data (B4:E6).

Example 4-1

You can do so using the syntax

=INDEX($B$4:$B$6,MATCH(1,MMULT(–($C$4:$E$6=<Subject>),TRANSPOSE(COLUMN($C$4:$E$6)^0)),0))

Let us see how this TRANSPOSE Formula in excel works.

  • –($C$4:$E$6=<Subject>)

When <Subject> is given in H3, it will be –($C$4:$E$6=H3).

This forms an array of 1 and 0 to indicate the presence or absence of the value. For Biology in H3, it will form the array as: {1,0,0;0,0,0;0,0,0}

  • TRANSPOSE(COLUMN($C$4:$E$6)^0))

It creates an array of 3 rows in a column, and a power of 0 makes sure that the numbers are converted to 1. The output of this function is {1,1,1}

  • MMULT(–($C$4:$E$6=<Subject>),TRANSPOSE(COLUMN($C$4:$E$6)^0))

The MMULT in excel multiplies the output of A and B.

MMULT({1,0,0;0,0,0;0,0,0}, {1,1,1}) gives the output {1;0;0}

  • MATCH(1,MMULT(<..>),0)

It will match the output of C with 1. MATCH(1, {1;0;0},0) returns the position 1.

  • INDEX($B$4:$B$6,MATCH(<…>,0))

It will identify the value of the cell for which the match function specified the position. INDEX($B$4:$B$6, 1) will return A1.

Example 4-2

Similarly for “Geography” as subject, it will be:

  1. –($C$4:$E$6 = D6) returns {0,0,0;0,0,0;0,1,0}
  2. TRANSPOSE(COLUMN($C$4:$E$6)^0)) retuns {1,1,1}
  3. MMULT({0,0,0;0,0,0;0,1,0}, {1,1,1}) returns {0;0;1}
  4. MATCH(1, {0;0;1}, 0) returns 3
  5. INDEX($B$4:$B$6, 3) returns A3.

Things to Remember

  1. The copy-paste option creates duplicates.
  2. This Function links the data to the source.
  3. If the number of rows and columns selected are not equal to the columns and rows of the source data, it will give a #VALUE error.
  4. Once it has been entered, any individual cell, which is a part of this function, cannot be changed.
  5. Press CTRL+SHIFT+ENTER to enter the Formula in excel.

Recommended Articles

This has been a guide to the TRANSPOSE Function in Excel. Here we discuss how to TRANSPOSE in excel using 1) Paste Special and 2) TRANSPOSE Formula along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Copy Paste in Excel VBA
  • VBA IsEmpty
  • Paste Special in VBA
  • SWITCH Function in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

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

Login

Forgot Password?

WallStreetMojo

Download TRANSPOSE Function Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More