Unpivot Columns without Power Query

Hello Excel Gurus,

Without using Power Query, how do you unpivot columns of a particular table?

The reason why i'm asking this is because I'd like to make it live and dynamic. So if there's additions to the table (in terms of more rows or columns), I'd like the formula to pick it up right away, rather than manually refreshing the power query.

And take note too that there I only want the 'Province' to "duplicate" by the number of city names, and not by the number of city columns.

For example, Notice that Ontario only has 3 cities, thus Ontario will duplicate 3 times.

PS: I'm only looking for a formula. Not VBA or Power Query. For some reason I can't use macro because of 'sharing the workbook with tracked changes'.

Unpivot Columns Without Power Query.png

try

=LET(z,Table1,r,ROWS(z),c,COLUMNS(z)-1,s,SEQUENCE(r*c,,0),p,INDEX(z,s/c+1,(MOD(s,c)+2)^{0,1}),FILTER(p,INDEX(p,,2)<>""))