How to Convert Table to List in Excel (3 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

While we are working in excel with a data table, we may need to turn an Excel table (with row and column headings) into a list for some reason. The following methods can give you a proper guideline to convert a cross table to a list easily and quickly in Excel. Here is a gif for a better understanding of how we have done this.

Overview Image


Convert Table to List in Excel: 3 Quick Ways

This article will demonstrate how to convert a table to a list in Excel by using PivotTable and PivotChart Wizard, using Power Query, and applying VBA code.


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

Let’s get introduced to our dataset first. I have placed some salespersons’ sales according to three weeks. You will notice that there are different headings towards row 2 and column B. We want to make a list that will have all headings of weeks along a column and the sales amounts along another column. So now we’ll convert this data table to a list by using PivotTable and PivotChart Wizard. It’s a pretty lengthy procedure but easy.

Sample Data Set for Using PivotTable and PivotChart Wizard to Convert Table to List in Excel

Steps:

  • To begin with, click on File and you’ll see different options.

Choosing File Tab

  • Secondly, press the Options.
  • Then, the Excel Options window will open up.

Selecting Options

  • After that, click as follows: Customize Ribbon > Commands Not in the Ribbon > PivotTable and PivotChart Wizard.

Opening Excel Options Window

  • Now, press successively: Data > New Group > New Group (Custom) >  Add.

Adding PivotTable and PivotChart Wizard in Excel

  • Lastly, you will see an option for PivotTable and PivotChart Wizard like the image below.
  • Now just press OK and you will get the PivotTable and PivotChart Wizard options in the top menu bar.

Showing PivotTable and PivotChart Wizard

  • Besides, click serially: Data > PivotTable and PivotChart Wizard.
  • As a result, a dialog box will appear.

Using PivotTable and PivotChart Wizard to Convert Table to List in Excel

  • So, put marks on Multiple consolidation ranges and PivotTable.
  • Then, press Next.

Opening PivotTable and PivotChart Wizard Window in Excel

  •  Later, click on the radio button named Create a single page field for me and press Next.

Opening PivotTable and PivotChart Wizard Window in Excel

  • Now select the data range and press Next.

Opening PivotTable and PivotChart Wizard Window in Excel

  • Then, choose your desired worksheet. I have chosen the New worksheet.
  • Besides, click Finish.
  • After that, a PivotTable Fields will appear on the right side of your Excel window.

Opening PivotTable and PivotChart Wizard Window in Excel

  • Deselect the Row and Column option.
  • Finally, just double-click on the Sum of Value.

Showing PivotTable Fields in Excel

  • As a result, you notice that we have found our desired list in a new sheet.

Showing Result for Using PivotTable and PivotChart Wizard to Convert Table to List in Excel

Read More: Types of Tables in Excel: A Complete Overview


Method 2: Using Power Query to Switch Table to List in Excel

Power Query is a tool that is used to simplify the process of collecting data from different sources and can be sorted into an Excel sheet in a convenient and usable format. Here, we’ll use it to convert an Excel table to a list.

Steps:

  • Firstly, select the data range.
  • Then, click as follows: Data > From Table/Range.
  • After that, a dialog box named Create Table will open up.

Using Power Query to Switch Table to List in Excel

  • So, just press OK.
  • And you will get a new window named Power Query Editor.

Creating Table in Excel

  • Then, press Ctrl and hold it; then using the left-click of your mouse select the three columns of the week from this new window.
  • Afterward, click: Transform > Unpivot Columns.

Opening Power Query Editor

  • Consequently, you will find a list like the image below.
  • Then, press Close & Load.

Opening Power Query Editor in Excel

  • Finally, you will observe that we have got our expected list in a new worksheet.

Showing Results by Using Power Query to Switch Table to List in Excel

Read More: How to Convert Range to Table in Excel


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

If you like to code, then it is possible to do the task using Excel VBA. Let’s see how to do it.

Steps:

  • Firstly,  right-click your mouse on the sheet title.
  • Secondly,  select View Code from the context menu.
  • Then, a VBA window will open up.

Opening VBA Code Editor in Excel

  • After that, write the following codes given below-
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
  • Then, press the Play icon to run the codes.

Applying VBA Code to Transform Table into List in Excel

VBA BREAKDOWN

  • Set input and output range
Set TheRange = Sheets("VBA").Range("B5:B11")
Set TheRange_output = Sheets("sheet4").Range("B2")
  • Apply For loop to assign values through the last cell in a range
For i = 1 To TheRange.Cells.Count
Set range_values = Range(TheRange.Cells(i).Offset(0, 1), TheRange.Cells(i).End(xlToRight))
  • Apply the If condition with For loop to get the output range
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)
  • Finally, you will get your desired list in a new sheet.

Showing Results by Applying VBA Code to Transform Table into List in Excel


Download Practice Book

You can download the free Excel template from here and practice on your own.


Conclusion

I hope all of the methods described above will be well enough to convert a table to a list. Feel free to ask any questions in the comment section and please give me feedback.


Further Readings


<< Go Back to Excel Table | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. Hi, tried the first 2 methods, really useful thank you. Would it be possible to include an additional category on the left, eg the region of the salesperson?

  2. Hello Jane, thanks for your feedback.
    Yes, it’s possible, just add the column on the left and apply the commands as I applied.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo