Excel - How To Switch Row And Column In Excel (Transpose with a Formula) - Episode 1623

Описание: Microsoft Excel Tutorial: Transpose data with a Formula

Welcome to another episode of the MrExcel podcast. In this episode, we will be discussing how to transpose data using a formula. This question comes from SJ, who has data that needs to be transposed with months going down instead of across. SJ's manager wants the final report to have the data in this format, but the problem is that the data is constantly changing and cannot be manually transposed.

At first, I suggested using the "Paste Special Transpose" feature, but SJ informed me that the data contains formulas that are constantly changing. In this case, we need to use a more advanced method. We will use the TRANSPOSE function, which is an array formula. To do this, we select the entire range where we want the transposed data to go, and then type in the formula "=TRANSPOSE" followed by the range of data we want to transpose. Remember to press Ctrl+Shift+Enter to create the array formula.

The result is a single formula that returns all of the values and transposes them sideways. This is a live formula, meaning that if the original data changes, the transposed data will also update accordingly. This is a great solution for situations where the data is constantly changing and needs to be transposed. Thank you for watching this episode of the MrExcel podcast. Don't forget to subscribe to our channel for more helpful tips and tricks. See you next time!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

Table of Contents
(00:00) Transpose Excel Data with a Formula
(00:26) Live formulas need to be transposed
(00:36) Paste Special Transpose breakes the formulas
(00:47) Using the TRANSPOSE function as an array formula
(01:02) Use Ctrl+Shift+Enter
(01:15) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
how to transpose formulas columns to rows in excel
how to use transpose formula in excel
how to reverse rows into columns in excel
how to switch row and column in excel
how to transpose in excel with formulas
how to reverse column to row in excel
how to transpose data in excel using formula
how to use transpose function in excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...




Visit us: MrExcel.com for all of your Microsoft Excel Needs!