You can do these actions in a number of ways. Perhaps the easiest is to perform these steps using SQL commands, but you need to know this language and its specific functions. If you want to do these actions directly in Excel, you need to use some add-ins. The Power Pivot and Power Query add-ins are mostly used for this purpose.
This post shows the capabilities of the Reshape.XL add-in. In principle, all available solutions are comparable and based on the same principles. The difference between using the Reshape.XL add-in and other solutions is that the data pivoting and unpivoting is easier, more straightforward, faster.
For pivoting and unpivoting, we use the functions from the ribbon toolbar tab Reshape - Gather and Spread.Their functionality is shown in the following two figures. The Gather function serves to data unpivoting - selected columns from the Excel table are transformed into rows (into the Key - Value pair of variables).Values in the Key column will contain the names of the previous columns and the Value variable will contain individual cell values.
Spread function serves for the opposite purpose - it transforms the selected Key - Value pair of variables into several new columns. Values from the Key variable will create new column names, and variable Value will form individual cell values.
Despite the relative complexity of the described functions, their setup and application is very simple and quick. Thanks to the Grammar of Data implementation, it is possible to continue processing with other functions, so the result of Gather and Spread functions does not form the final, static and unchanging form. The use of these functions is presented in following short video tutorial.
As is clear from the video tutorial, using Gather and Spread functions is very simple and fast. You can learn more about these features in the documentation that defines each adjustable property and shows individual use cases.
In addition to the two described functions, you can also use additional rows-to-columns and columns-to-rows transformations within the Reshape.XL add-in - Transpose, Flip_Horizontal and Flip_Vertical. Their character is slightly different.