How to Convert a Table to a List in Excel (3 Quick Methods)

Method 1: Using PivotTable and PivotChart Wizard to Convert a Table to a List in Excel

Introduction to the Dataset:
- You have a dataset containing sales data for different salespersons over three weeks.

Objective:
- Create a list with the headings of weeks in one column and the sales amounts in another column.

Steps:
- Click on the File tab to access different options.

- Next, click on Options.

- In the Excel Options window, navigate to Customize Ribbon” > Commands Not in the Ribbon > PivotTable and PivotChart Wizard.

- Click Add to include this option in the top menu bar.

- Now, go to Data > PivotTable and PivotChart Wizard.

- A dialog box will appear.

- Select both Multiple consolidation ranges and PivotTable.
- Click Next.

- Choose the radio button labeled Create a single page field for me and click Next.

- Select the data range and click Next.

- Choose your desired worksheet (e.g., New worksheet) and click Finish.
- The PivotTable Fields will appear on the right side of your Excel window.

- Deselect the Row and Column options.
- Finally, double-click on Sum of Value.

Result:
- You’ll find your desired list in a new sheet.

Method 2: Using Power Query to Convert an Excel Table to a List

Power Query is a powerful tool that simplifies the process of collecting data from various sources and organizing it into a usable format within an Excel sheet. In this method, we’ll use Power Query to convert an Excel table into a list.

Here are the steps:

Select the Data Range:
- Begin by selecting the data range (the table) that you want to convert to a list.

Access Power Query:
- Click on the Data tab in Excel.
- Choose From Table/Range from the options.

Create Table Dialog Box:
- A dialog box named Create Table will appear.

- Simply press OK to proceed.

Power Query Editor:
- You’ll now see the Power Query Editor window.

- Hold down the Ctrl key and use your mouse to select the three columns representing the weeks from this window.

Unpivot Columns:
- Click on Transform in the Power Query Editor.
- Select Unpivot Columns.

- This action will transform the columns into rows, creating a list-like structure.

Close & Load:
- After making the necessary transformations, click Close & Load.

The result will be a new worksheet containing the expected list.

Method 3: Applying VBA Code to Transform a Table into a List in Excel

Open the VBA Editor:
- Right-click on the sheet title where your data is located.
- Select View Code from the context menu.
- This will open the VBA window.

Write the VBA Code:
- Create a new subroutine called TransposeThis() (you can choose any name).
- Define the input and output ranges:
- TheRange : Set it to the data range you want to convert (e.g., Sheets(“VBA”) .Range(B5:B11)).
- TheRange_output : Set it to the location where you want the list output (e.g., Sheets(“sheet4”) .Range(“B2”)).

Loop Through Rows:
- Use a For loop to iterate through each row in TheRange.
- For each row, determine the range of values to be transposed:
  - range_values : Set it to the cells from the current row (excluding the first cell) up to the last non-empty cell in that row.

Check Condition:
- If the number of cells in range_values less than 15,000 (you can adjust this threshold), proceed with the transposition.

Assign Values:
- Inside the loop, assign the value from TheRange to TheRange_output.
- Offset TheRange-output by one column and assign the corresponding value from range_values.

Move to Next Row:
- Offset TheRange_output by one row to prepare for the next row’s output.

Sub TransposeThis()
Set TheRange = Sheets("VBA").Range("B5:B11")
Set TheRange_output = Sheets("sheet4").Range("B2")
For i = 1 To TheRange.Cells.Count
Set range_values = Range(TheRange.Cells(i).Offset(0, 1), TheRange.Cells(i).End(xlToRight))
If range_values.Cells.Count < 15000 Then
For j = 1 To range_values.Cells.Count
TheRange_output.Value = TheRange.Cells(i).Value
TheRange_output.Offset(0, 1).Value = range_values.Cells(j).Value
Set TheRange_output = TheRange_output.Offset(1, 0)
Next j
End If
Next i
End Sub