Excel Pro Trick: Unpivot data in Excel using Formulas with TOCOL Function - No Power Query Method

Learn how to unpivot data in Excel using formula without the need of power query in seconds. Using Excel array functions, we can easily unpivot data dynamically.

In this tutorial we learn how to use TOCOL function and its power to our advantage to unpivot columns in Excel with the help of TEXTSPLIT and TEXTJOIN function to rearrange columns in Excel.

Until now, Power Query was the only tool available to us to unpivot columns in Excel data. However, with advanced array functions now available, unpivoting Excel data is a breeze!

Lets Excel!

Excel Workbook to Download or Practice online for this tutorial:

For more Excel tips, tricks and tutorials don't forget to subscribe to @LearnExceltoexcel channel.

Learn #Excel your way:
===================
--------------------------------------

Pick your favourite social page:

#exceltutorial #exceltips #excel #excelformula #excelformulasandfunctions

[crc]

This is a real time saver for manipulating already "set up" data. Is there any additional formula though that keep the unpivoted columns that are numeric, remain as numeric? or is a conversion column needed after?

[franks]

Why not build your pivot table in the below view to start with and then copy/paste special values?

[chetanagarwal]

Hi,

This is not working for large datasets.
10 Columns and 50 rows data.

It is throwing a #Calc Error

[johanzain]

Pfftt, just use power query! No complicated formula syntax needed!