How to un-pivot data in a table on Excel for Mac

Question

I've read the suggested results pertaining to un-pivoting a table using the method from a Jon Walkenbach article, but I'm on a Mac, and can't get the desired result.

My scenario is exactly the same as found in the article: Transform horizontal table layout to vertical table so I'll just refer to those same field names and procedure steps here.

Since I don't have access to an "old pivot table menu" in macOS (as far as I know), I don't have the same options listed there. When I try to use the tools at my disposal, I get Field Names corresponding to ID, Name1, Name2, Name3, with a checkbox for each, rather than the desired Row, Column, Value.

If I uncheck everything but ID, I do get a single-cell pivot table, but double-clicking on that cell produces the exact same table that I started out with.

I was able to obtain the desired results using Excel on Windows 10 to test it and make sure I was understanding it correctly. But trying the same steps on the Mac, I can't get it to work. One answer might be "Use a PC." But if there's another answer, I'd love it. Thanks.

Answer 1

Assuming you have 365, you can use a formula (and, according to the documentation, the functions I am using are available in 365 MAC)

I used a table named Table5 and copied it from the example you linked to, but you could use regular addressing if you had to.

Also, as in that example, I assumed you wanted a two column result, with the first column being repeated:

=LET(
    cols, COLUMNS(Table5) - 1,
    rows, ROWS(Table5),
    v, TOCOL(DROP(Table5, , 1)),
    ID, INDEX(
        Table5[ID],
        INT((SEQUENCE(rows * cols) - 1) / cols + 1)
    ),
    stack, VSTACK({"ID", "Name"}, HSTACK(ID, v)),
    FILTER(stack, CHOOSECOLS(stack, 2) <> "")
)

In the above:

• cols: number of columns to unpivot (total columns in table -1)
• rows: number of data rows
• v: a vertical array of the unpivoted columns
• ID: a vertical array of the ID column (column1) with each entry repeated by the number of rows we are unpivoting
• stack: the column headers and the two vertical columns are stacked into a result table
• That result is then filtered to remove the blank entries

Data

Unpivoted

If you do not have those functions, you can use Power Query and you can access the unpivot function from the GUI.