 | |
| Convert Columns to Rows in Excel (NO Transpose, NO Formulas - SIMPLY UNPIVOT in Power Query) |
It sounds simple to just switch columns to rows - basically transpose horizontal data to vertical. BUT you can't always use a simple transpose (neither the transpose feature nor the Transpose function) if you only need to transpose parts of your data set. Some columns, instead of all columns.
For example if you have a report in Excel with a list of months in the columns, it would be easier to analyze the data and create formulas or pivot tables on the data, If your data is in a tabular format. For each value field in the original report, we will get a new row. Since this involves unpivoting or transposing parts of the data, it can be difficult to achieve with Excel formulas.
This is where you can use Excel's "Get & Transform" feature - (also called Power Query). Get & Transform is available since Excel 2016. All you need to do is create a table or a named range from your source data and then unpivot the columns in the Power Query editor. If some of the value cells in your report happen to be empty - the entire row will be removed in the final table. To keep empty cells, you need to replace the null value in Power Query with 0.
Related Topics:
Post a Comment