How to quickly convert an array table to a list table in Excel?
Supposing you have an array table/cross table, and to convert this array table to a list table may be not a difficult job for you and you can convert it by entering the data one by one. However, if there are multiple array tables needed to convert to list tables, the manual method must not be a good solution. Now, this tutorial introduces the tricks which can quickly convert array table to list table in Excel.
You can apply PivotTable to convert array table to list and then copy it as range.
1. Press Alt + D simultaneously, and then press P key to enable the PivotTable and PivotChart Wizard dialog, and check Multiple consolidation ranges and PivotTable options. See screenshot:
2. Click Next to go to the Step 2a of the wizard, and check I will create the page fields option. See screenshot:
3. Click Next to go to Step 2b of the wizard, and select the array range and add it to the All ranges list. See screenshot:
4. Click Next to go to the last step, check one option as you need. See screenshot:
5. Click Finish and then a PivotTable has been created, then go to the crossing cell of Grand Total, double click it, and you can see a list PivotTable has been created in a new worksheet. See screenshots:
6. Then select the list PivotTable, and right click to select Table > Convert to Range from the context menu. See screenshot:
Now the array table has been converted to list table.
Here is a VBA can help you convert array table to list table, please do as below steps:
1. Press Alt + F11 to enable the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below VBA code to the new Module window.
VBA: Convert array table to list
Sub ConvertTableToList() 'UpdatebyEntendOffice20160429 Dim I As Long Dim xCls As Long Dim xRg As Range Dim xSaveToRg As Range Dim xTxt As String On Error Resume Next xTxt = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Select Array Table:", "Kutools for Excel", xTxt, , , , , 8) If xRg Is Nothing Then Exit Sub Set xSaveToRg = Application.InputBox("Select a range(cell) to put the list table", "Kutools for Excel", , , , , , 8) If xSaveToRg Is Nothing Then Exit Sub Set xSaveToRg = xSaveToRg.Cells(1) xCls = xRg.Columns.Count - 1 Application.ScreenUpdating = False For I = 1 To xRg.Rows.Count xSaveToRg.Offset((I - 1) * xCls).Value = xRg.Cells(I, 1).Value xSaveToRg.Offset((I - 1) * xCls, 1).Resize(xCls).Value = _ Application.WorksheetFunction.Transpose(xRg.Cells(I, 2).Resize(1, xCls)) Next Application.ScreenUpdating = True End Sub
3. And press F5 key to run the VBA, and a dialog pops out for you to select the array table excluding headers. See screenshot:
4. Click OK to go to next dialog to select a cell to output the list table. See screenshot:
5. Click OK, and the array table has been converted to list.
If you want to fill the blank cells based on above cell, you can do as below:
1. Install Kutools for Excel -- a handy tool and then click Kutools > Insert > Fill Blank Cells. See screenshot:
2. In the Fill Blank Cells dialog, check Based on values and Down options. See screenshot:
3. Click Ok or Apply, and now the blank cells are filled based on above values.
If above methods are not easy enough for you, here I can introduce a handy tool – Kutools for Excel, its Transpose Table Dimensions utility can quickly and easily convert between array table and list table.
|Kutools for Excel, with more than 300 handy functions, makes your jobs more easier.|
After free installing Kutools for Excel, please do as below:
1. Select the array table you want to convert to list, and click Kutools > Range > Transpose Table Dimensions. See screenshot:
2. Then in the Transpose Table Dimensions dialog, check Cross table to list option, and then select the range that output the list table. See screenshot:
3. Click Ok, now the array table has been converted to a list table.