How To Convert Matrix Style Table To Three Columns In Excel?
Supposing you have a matrix-style table which contains column headings and row headings, and now you would like to convert this style table to three columns table, it also called list table as following screenshot shown, do you have any good ways to solve this problem in Excel?
Convert Matrix Style Table To List With PivotTable
In Excel, there isn’t a direct feature for us to convert the matrix style table to three columns table, but, if you are familiar with PivotTable, it may do you a favor. Please do with the following steps:
1. Activate your worksheet which you want to use, then holding Alt + D, and then press P in the keyboard, in the popped out PivotTable and PivotChart Wizarddialog, select Multiple consolidation ranges under the Where is the data that you want to analyze section, and then choose PivotTable under the What kind of report do you want to create section, see screenshot:
2. Then click Next button, in the Step 2a of 3 wizard, select the I will create the page fields option, see screenshot:
3. Go on clicking Next button, in the Step 2b of 3 wizard, click button to select the data range that you want to convert, and then click Add button to add the data range to the All ranges list box, see screenshot:
4. And click Next button, in Step 3 of 3 wizard, select a location for the pivot table as you want.
5. Then click Finish button, a pivot table has been created at once, see screenshot:
6. In the pivot table, double click intersecting cell of the Grand Total, in this case, I will double click the cell F22, and it will generate a three columns table as following screenshot shown:
7. And finally, you can convert the table format to the normal range by selecting the table and then choose Table > Convert to Range from the context menu, see screenshot: