How to convert a N columns table to two column table in excel

Question

How to convert a table of multiple columns to a two columns table in excel. As shown in the image the two-column table should be based on the first column of the multiple column table

Answer 1

use these steps

• select the data,
• get & transform tab on data menu
• from table
• data will be opened in power query window
• select columns 2 to column 5, at once
• right click, use unpivot
• save and load

see the following screenGIFs

Edit in view of comments below

To add null values to output you can do following workaround

• replace null values from any non-existent value say -
• then unpivot
• see the following GIF