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.
Convert array table to list table with PivotTable
Convert array table to list table with VBA
Convert array table to list table with Kutools for Excel 
Convert array table to list table with PivotTable
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.
Convert array table to list table with VBA
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 Sub3. 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.
Tip:
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.
Click here to know more about Fill Blank Cells.
Convert array table to list table with Kutools for Excel
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.
Click here to know more about Transpose Table Dimensions.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
