3 Simple Ways to TRANSPOSE Data in Excel [with & without Formula]

In this video, I will show you a couple of ways to transpose data in Excel.

The following methods of transposing data are covered in this video:
-- Transpose using Paste Special
-- Using the TRANSPOSE function
-- Transpose using Find and Replace and Paste Special

When you use Paste Special to transpose the data, it will give you the static result. This means that when you change the original dataset, it will not update the resulting transposed data.

If you want to have the transposed data linked, you need to use the Transpose function or the Find and Replace technique I cover in the video.

One issue that I don't like about the TRANSPOSE function is that you need to select the range which is of the same size as the expected result.

This video is a part of my 'Excel is two-minute' series, where I share simple and quick tips to help you speed up your work and be more efficient

This YouTube channel is managed by Sumit Bansal (who also runs the TrumpExcel website). This channel is meant for people who want to learn Excel.

It covers a lot of Excel basics and advanced Excel topics such as Excel Formulas, Functions, Pivot Table, Shortcuts, Excel VBA, Macros, Excel Dashboards, Excel Charts, Conditional Formatting, Power Query, etc. Please subscribe to this channel to be the first to know when new Excel tutorials come out.

⚙️ Gear I Recommend:

Note: Some of these links here are affiliate links!

#Excel #ExcelTips #ExcelTutorial

[NehaSharma-lwsn]

Great!! thanks for the simple solution

[jzhgmvn]

Thanks for this transpose method using Replace and # = . You saved me hours!

[kalivaradhanmurugesan]

Dear Sumit, All your videos on Excel are awesome and i should appreciate your way of teaching. Keep rocking.

[mahamohan]

Something different i had learnt today. Many thanks

[phcderol]

Great suggetion to make more userful this formula, i like thanks bro

[jasontan]

Creative problem solving 👍👍 My approch would be convert it to Excel Table (Ctrl-T), then import data into Power Query and Transpose there instead. Once done, output the result in Table form. Doing so, can add/remove row & column that fully reflect once Data Refresh..

[paruchurischannel]

Hi Sumit !
Thanks for sharing 3 methods for Transposing Data.The third one is really amazing.Thanks again for the excellent videos that you are sharing

[suryasurendran]

Excellent, Sumit!! I do need to transpose large arrays at times. Get by with the 2nd Method usually, but as the sitting becomes longer, tend to make errors in selecting the number of rows and columns!! The 3rd Method you have described will come in handy on such occasions, though it will take longer than the second!! Thanks!!

[devendrabain]

Aaj to kamal ho gaya.
Main jab bhi thoda bahut excel seekh ke khush feel karta hoon. Turant sumit sir ka video aa jaata hai..
Isko karne ke do tareeke aur hai
😂😂😂😂😂

[PSMVlogs]

tq for this useful one, i need another shortcut in this method for example in one cell having many of number of names i want to convert into each separte cell so how could we do that in such a case, i hope u understand my question

[wayneedmondson]

Hi Sumit.. thanks for sharing the 3 methods.. all great ways to solve the problem. Another method that I like for linking the transposed table to the source table is by using the INDEX function as in: Enter the following in cell G1: =INDEX($A$1:$E$5, COLUMNS($A1:A1), ROWS(A$1:A1)). Copy the formula down to row 5 and right to column K; use the Format Painter to copy the formatting from the source table to the transposed table. What I like about the formula method is that it is easier and faster to set up (once you get the hang of it) and fewer steps (arguably). It is also flexible in that if your source table grows, you can expand your transposed table along with it by continuing to copy the formulas down and right.. and once the formatting is set, the formatting follows the copying. So, thought I would share this method #4. Likely, you already know it.. but maybe it will be new for some readers of the comments. Thanks for sharing your vast and excellent knowledge in your videos, your courses and on your web site.. much valued and appreciated. Thumbs up!

[thomasng]

Can you also transpose the conditional formatting ? I have Conditional formating which use formular. Anyone has any ideas ?

[vijaykishan]

Hi Sumit, Can you please show through index method.