Xoay cột dòng trong excel – How To Convert Matrix Style Table To Three Columns In Excel?

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?

doc convert matrix to list 1


 Convert Matrix Style Table To List With PivotTable

excel-tab-banner-2014-12-02


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:

doc convert matrix to list 2

2. Then click Next button, in the Step 2a of 3 wizard, select the I will create the page fields option, see screenshot:

doc convert matrix to list 3

3. Go on clicking Next button, in the Step 2b of 3 wizard, click doc convert matrix to list 5 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:

doc convert matrix to list 4

4. And click Next button, in Step 3 of 3 wizard, select a location for the pivot table as you want.

doc convert matrix to list 6

5. Then click Finish button, a pivot table has been created at once, see screenshot:

doc convert matrix to list 7

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:

doc convert matrix to list 8

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:

doc convert matrix to list 9

http://q2a.dothanhlong.org/?qa=111/trong-excel-convert-matrix-style-table-three-columns-excel