Unstack Data using Excel Formula (No Power Query)

Описание: Final Formula for Name Manager:
=LAMBDA(r,HSTACK(VSTACK("Row Name",TOCOL(IF(r=r,OFFSET(r,,-1,,1)))),VSTACK("Col Name",TOCOL(IF(r=r,OFFSET(r,-1,,1)))),VSTACK("Data",TOCOL(r))))

Power query is a great tool for unstacking data, but sometimes a formula approach is preferred for reasons such as:
Preference to work without using tables or Power Query
Real time updates/no need to refresh query to see changes
Compatibility with Excel Online
Make shared workbook more user-friendly
Easy to update formulas

Steps:
1. 00:00 Input data with Column and Row Names
2. 01:00 Create Named Range (e.g. “rng”)
3. 01:30 Use TOCOL( ) Function to unstack data
4. 02:10 Use IF(rng=rng) Formula to Spill Header names
5. 02:53 Wrap Header names in TOCOL( ) Function to unstack names
6. 03:27 Repeat Previous 2 Steps for Row Names
7. 04:00 Replace Header and Row name ranges using OFFSET(rng) Function
8. 05:19 Use VSTACK( ) Function to add headers
9. 06:14 Create placeholders inside LAMBDA( ) and HSTACK( ) Functions
10. 07:10 Replace placeholders with formulas
11. 08:08 Modify Formula to be Name Manager compatible
12. 09:05 Create custom Unstack( ) Function using Name Manager