Convert Excel Tables To Lists

note: please see the update on the bottom of this article for an even quicker way to convert a table

In a dark past I was an Excel instructor (among other things). I have trained countless people in the art of Excel number wizardry. I have then become a certified Excel VBA specialist, and I must say, in my years being a professional programmer, this is the skill that has set me apart from all other programmers around me. Sure, people can do Regular Expressions… So can I. Sure, people can do Object Orientation. So can I. But what programmer fancies dumb jumbling of data, and programming a language that has the word ‘basic’ in it? Right. Most programmers I knew were Linux shell companies (pun intended) that had no idea something good was hidden up the sleeve of Microsoft. But ok, sometimes I was able to show them some awesome things, and they would instantly recognize that their world view (Excell is for end-users) was a grave mistake.

What fun it was to teach people Excel, and more so, VBA! To me, it’s the tool of all tools, and it can greatly help anyone who ever works with data (ehm, anyone). So in this first part of a long, long series (I hope) I will show you, the humble ignorant user how to convert an Excel table to a list.

Why? Many times I have gone to companies and helped them with some particular problem. Usually it started out with an analyst/marketer/ceo showing me a bunch of data. The data was always presented as a table, with column headings and row headings, with the data in the middle. That seems like a nice way to present data, yes, it is in fact. The first thing I would do is then convert this table to an ugly list.

So why would you convert it to a list?

Because Excel is in love with lists. Excel craves lists, it’s like Access’ little brother, but it can speak five languages and juggle 4 balls. It’s no database tool (maximum of 65535 rows, 1M in Excel 2007)… but it can transform any list into a deep, deep analysis.

The way this analysis is done later on is with Pivot Tables. I wrote those capitals on purpose. Pivot Tables are so powerful that you can basically give it any data list and it can tell you what’s missing, what’s wrong, what’s unique, what’s the total, what’s the average, you name it. But more on that later on.

Let’s convert!

Warning: code ahead…

A table consists of three parts:

  1. The row headings (left)
  2. The column headings (top)
  3. The data (center)

We will loop through the data cell by cell, and create a row in a new list for each. That’s the basic idea (pun intended).

Before we start, we check some preconditions. We have to make sure that we are inside a set of data, formed into a table. All we do is just check if we have at least two rows and two columns (not the ultimate, but it works).

Sub TableToList()
If ActiveCell.CurrentRegion.Rows.Count < 2 Then
Exit Sub
End If
If ActiveCell.CurrentRegion.Columns.Count < 2 Then
Exit Sub
End If

Then we will need some variables to refer to the various sections of the table

Dim table As Range
Dim rngColHead As Range
Dim rngRowHead As Range
Dim rngData As Range
Dim rngCurrCell As Range

Next. we will need some variables for the data itself

Dim rowVal As Variant
Dim colVal As Variant
Dim val As Variant

Now, we will start pointing our variables to the data, row headings and column headings, like so

Set table = ActiveCell.CurrentRegion
Set rngColHead = table.Rows(1)
Set rngRowHead = table.Columns(1)
Set rngData = table.Offset(1, 1)
Set rngData = rngData.Resize(rngData.Rows.Count - 1, rngData.Columns.Count - 1)

Note that “currentregion” is a handy tool that expands any cell into a surrounding of non-empty cells. So this way your selected cell could be anywhere inside the table when you run the macro. The data part is a bit harder, line 4 and 5 together shift and resize the original table to form the right bottom part, where all the data resides.

ActiveWorkbook.WorkSheets.Add

Next, we create a new sheet in the workbook, to hold the list.

ActiveCell.Value = "Row#"
ActiveCell.Offset(0, 1).Value = "RowValue"
ActiveCell.Offset(0, 2).Value = "ColValue"
ActiveCell.Offset(0, 3).Value = "Data"
ActiveCell.Offset(1, 0).Select

In this sheet, we create a first row, “manually”, where we name the column headings for our list. These column headings are very important for sorting, analysis, pivot tables, export and such. The last statement instantly moves the current cell selection one row down. Notice we’re inserting a special column for Row Number. This is not always necessary, but it doesn’t hurt, and it helps you to always be able to restore the original order of the list.

Now it’s time for the actual grunt work, looping through the table

Dim n As Long
For Each rngCurrCell In rngData
colVal = rngColHead.Cells(rngCurrCell.Column - table.Column + 1)
rowVal = rngRowHead.Cells(rngCurrCell.Row - table.Row + 1)

The “for each rngCurrCell in” is a real beauty in VBA. It just runs through any selection, without worries of overflows, row and column numbers, or calculations. In the loop, we set the value of the current column and row. Note that the rngCurrCell.column and rngCurrCell.row are not relative, it’s the actual number of the column/row. So if the tables starts at C3, the first cel is having column=3 and row=3.

n = n + 1
ActiveCell.Value = n

Here, we upped counter ‘n’ and put it in the list.

ActiveCell.Offset(0, 1).Value = rowVal
ActiveCell.Offset(0, 2).Value = colVal
ActiveCell.Offset(0, 3).Value = rngCurrCell.Value
ActiveCell.Offset(1, 0).Select

We do the same trick again to put a new row in the data list on our new sheet. As you can see this part of code is repeated from the part where we created the header. A small improvement would be to create a function named ‘newRow(n, rv, cv, dv)’ to insert a new row with these values.

If, instead of actual values, you prefer to link to the original cell, you can use

ActiveCell.Offset(0, 3).Value = "=" & rngCurrCell.Worksheet.Name & "!" & rngCurrCell.Address

Finish the loop with:

Next
End Sub

Well, that’s it!

Running your code

Make sure to have a table setup in Excel, and click inside the table, it will be automatically selected.

  • Press ALT+F8
  • Select TableToList
  • Click Run

In Office 2003 you can add a shape to your worksheet, right click, and choose assign macro.

  1. Choose Tools > Customize
  2. Choose Macros > Custom menu item -> drag to toolbar
  3. Right click item
  4. Choose Assign macro…
  5. Choose TableToList

Since Office 2007 this option is not available anymore, but you can still right click the ribbon and choose ‘customize quick access toolbar’. From there you can pick the Macro’s category and add the macro.

A new sheet will be created. Take a look at the list. You can try sorting, filtering, analyzing, totalling, and… pivot tables. A pivot table is a dynamically updating table which automatically totals values from a list, and presents them in… a table. Here’s how to re-create the original table from the list:”

  1. Choose Data > Pivot Table
  2. Choose Finish
  3. Drag ColumnValues to the ‘column fields’
  4. Drag RowValues to the ‘row fields’
  5. Drag Data into ‘Drop data items here’

Voila, the list is back. That is, if it was a list of numbers. Pivot Tables are for numeric operations, if you had text in there, it won’t show anything (anything good).

Download

Download the file here:

Table2List.xla

How to install:

  1. Office 2003: to install an XLA you need to go to Tools > AddIns and select the file with the browse button. Make sure the checkbox is enabled.
  2. Office 2007: Click on the Office Button in the left top, then Excel Options, then Add-Ins. Now select Manage… Excel Addins and click Go. Again browse and enable the addin.

You may wish to copy the file to the suggested Add-Ins folder. If you are on a network and wish to share the add-in with others, make sure to keep it on a network drive.

Once the Add-in is enabled you will see a new button that runs the macro. In Office 2007 the button is under the Add-Ins ribbon tab. You can also press ALT+F8, then type ‘Table2List.xla!TableToList’. The macro will be hidden in the XLA file, so you cannot select it.

An even quicker solution

Abu Yahya (see comments) gave me an even quicker solution. All kudos go to him for this.

First, start the pivot table wizard. Now in Excel 2007 and up you may have a hard time finding it! So, right click the quick access toolbar (it’s the bar with tiny icons on the top). Then choose Customize… and select Choose commands from: Commands not in the ribbon. Now find “pivottable and pivotchart wizard” in the list, and add it to the list on the right. You will see a tiny pivottable icon in the toolbar now.

Go on and click that icon, and then:

Step 1. Choose multiple consolidation ranges
Step 2. Choose I will create the page fields
Step 2b. Select Range of the table then Add to
Step 3. Choose New Worksheet
Step 4. Click Finish
Step 5. on the new sheet – Pivot table field list –> uncheck [ ] Row and [ ] Column
Step 6. There will be one value exactly in your pivottable. Double click it.

You will now see a new sheet with a list built up of the columns Row, Column and Value.

Step 7 (optional). Create a pivottable from this list to analyze your data.

One important note: you can have exactly 1 field that will show up in your data next to your row and column fields. That field has to be in the far left column of the data you select before consolidating. If you need more data in your final analysis you can combine fields with the “&” operator, using a formula like e.g.in cell C2:  =A2 & B2.

About these ads

53 comments so far

  1. Philip Truax on

    The file you linked to is no longer available. Thank you for the interesting article though.

    • michiel on

      Thanks for the tip. I fixed the link, it should work again.

  2. George on

    I downloaded and installed the add-in but not sure how its supposed to work–didn’t see where it added itself to any menu options or a toolbar. I know I must be missing the obvious…Help? Or do I just need to the code?

    Great tip!

  3. Jan on

    Many thanks for the tip. The table to list converter was exactly what I was looking for. But like George, I didn’t manage to get it to work. Can you provide more info on how to start addin? Thank you!

    • Michiel on

      You can activate it like this:
      - Tools > Customize
      - Macros > Custom menu item -> drag to toolbar
      - right click item
      - assign macro
      - type Table2List.xla!TableToList

      Now put your active cell on the table, and click the button!

  4. Laurent on

    Hi Michiel! This bit of code is great and does exactly what I was looking for… almost! How could it be adapted to take into account the fact that some of the source data is already in list format ?

    For example let’s say there are 3 columns of row headings (Region, Country, Customer) and then 4 columns of sales figures (Q1, Q2, Q3, Q4). And I want to turn this into a list with 4 columns of row headings (Region, Country, Customer, Quarter) and 1 column of values ?

    Hope you can help me, I’ve been stuck all morning trying to figure out a way to do this with VBA! Cheers

    • michiel on

      Laurent, this is solvable. Just make a column in your data that combines the data of the three Region, Country and Customer. Like this: =A2&”-”&B2&”-”&C2. So you will have columns ‘combined’, q1,q2,q3,q4. Then run the macro.

  5. Archana on

    Invoice Statement for FEB-09 ( Hyderabad – BANA )
    Dollars in Whole Amounts

    SERVICES RENDERED 120%
    Sl No. Inv. No. Inv. Date Process DB ID Class Code LOB Co. No. CC GL Acct. 2 Dot 3 Dot 4 Dot Desc Head Count FTE Month Start Month Close Net Rate / FTE Gross Rate / FTE FEB 09 Inv. Amt. (USD) FEB 09 LOB Rebate FEB 2009 Net Invoice Value (USD) Desc Remarks
    – – 0 0 0 0 Cards – Ops Rita Nag Chowdhury
    Total 0 0 0 0 0

    Re-imbursables

    Sl No. Inv. No. Inv. Date Process DB ID Class Code LOB Co. No. CC GL Acct. 2 Dot 3 Dot 4 Dot Desc Head Count FTE Month Start Month Close Net Rate / FTE Gross Rate / FTE FEB 09 Inv. Amt. (USD) FEB 09 LOB Rebate FEB 2009 Net Invoice Value (USD) Desc Remarks

    Total – – –

    Seat-Utilization

    Sl No. Inv. No. Inv. Date Process DB ID Class Code LOB Co. No. CC GL Acct. 2 Dot 3 Dot 4 Dot Desc Head Count FTE Month Start Month Close Net Rate / FTE Gross Rate / FTE FEB 09 Inv. Amt. (USD) FEB 09 LOB Rebate FEB 2009 Net Invoice Value (USD) Desc Remarks

    Total 0 0 0

  6. Manindra on

    Hi,
    Thanks for this. It really helped.

    Can I adapt this code to work with Pivot tables that has multiple row or columns headings? At present it seems to work only when there is one row & one column heading

    Please email me about this

    Manindra

    • michiel on

      I guess it’s possible, but difficult. How is the script to know which are headings and which are not? You could ask, but it defeats the purpose of a 1-click macro, which I really like. I’d say just copy the part you need to make a list of into a separate sheet and then run the program. If you need to, you can combine multiple values using “&”. So you create a copy of the table, and then add a column/row where you make a heading of the two values combined. So e.g. FirstName (A2), LastName (A3) becomes FullName (=A2 & ” ” & A3). This will then be your new heading.

  7. john on

    I was really wondering to know “how to convert excel tables to data lists”. This tip really helped me. Many thanks Michel. Really Useful.

  8. Hafiz on

    How to eliminate those data with zero value or blank?

    • Hafiz on

      I mean I have huge data (more than 80,000) but half of it is ‘blank’ or 0 value

      • michiel on

        In Office 2007 use Data > Filter, then click on the drop down for value and clear the check box at ‘(Blanks)’. Google Excel filters if you’re not sure how it works.

  9. [...] row) are nice for a visual report, but the data cannot be used in any other way. If you’ve read a recent article about the TableToListConverter, you know why. Excel is an excellent analyzer of data, as long as this data is organized in rows [...]

  10. [...] Posted by Thomas Patrick on June 6, 2010 Convert Excel Tables To Lists « The Universe Divided. [...]

  11. Terry on

    This is fantastic…I use pivots often, and the data that comes from our system is often in table form and is a nuisance to use. Thanks.

    Is there a way to get the rowvalues to be carried over as a text field? We use numberical item #s that are stored as text, but when that info is brought over, it is in value format.

    I could add to the macro a text to columns convert, but leading zeros get lost and it causes problems on a large batch of items.

    • michiel on

      To fix the leading zero problem, you might want to use

      ActiveCell.Offset(0, 3).formulaR1C1 = “=” & chr(34) & rngCurrCell.Value & chr(34)

      Insert this instead in the line were the value is set. I haven’t tried it, so see if it works.

      • Terry on

        Thanks, Michiel…I tried that and it errors out on the rngCurrCell part of that statement saying that the variable is undefined. I tried a couple of edits to that statement, as well as defining it but couldn’t get it work.

        Thoughts?

        • Paul on

          I had one small problem with the code. It would not create the new sheet. The code below fixes the problem. You can see what is commented out and the next line is the fix.

          Dim shList As Worksheet
          ‘Set shList = ThisWorkbook.Worksheets.Add
          Sheets.Add.Name = “shList”

          Other than that this works GREAT Thnaks!!!

        • michiel on

          Sorry for that mistake. Use this instead:
          ActiveCell.Offset(0, 3).Value = “=” & Chr(34) & cel.Value & Chr(34)

          Don’t copy/paste it, because wordpress converts the quotes to something that doesn’t work in VBA. Use regular double quotes.
          Apparently the XLA file uses the name ‘cel’ whereas in my article I am using the name ‘rngCurrCell’

  12. Chris on

    Thanks, its a great script.!
    I just have a question if I would like to set a link to the other table instead of copying the value how do I need to adapt the formula?

  13. Arlene on

    Hey Michiel – perfect – this is so cool. Thank you

  14. Danièle on

    Thanks! Thanks for the add-in but also for the step by step explanation of the logical steps of the code!
    Great!

  15. Danièle on

    I have a problem that no one seems to have encountered!
    I do not get a new sheet, and the list that is created from the position of the active cell so that as it enters data, it erases the data that was to the right and bottom of that cell.
    Reading the code, it does ask to create a new sheet ( which incidentally is not happening at all), but I do not seem to find a line saying that worksheet needs is then activated.
    When the active cell is the last cell of the table, the list that is generated is “near” perfect, except for the last line. It is exactly as it was hoped for…. except where the list is generated!
    Thanks!

    • michiel on

      See Paul’s tip of July 29, 2010 above. That might solve your problem too. I am not sure why sometimes it works and sometimes it doesn’t, but it has to do with the ThisWorkbook variable.

  16. greg on

    great article but i cannot find the tool bar in excel 2007 once enabled???

    • michiel on

      @greg, the code doesn’t offer a toolbar. However, I updated the XLA file to automatically create a toolbar. In Office 2007 the menu item will be shown under Add-Ins.

  17. Ling on

    Thank you for the tip! I copied your VB line by line and it worked perfect for me.

  18. Deb on

    Hi Michel,

    I take my hat off to your humanitarian efforts in assisting us mere button clickers.

    I ran your sexy macro on my table:

    Species Count
    SpeciesA 3
    SpeciesB 4
    SpeciesC 2

    and it gave me…

    Row# RowValue ColValue Data
    1 SpeciesA Count 3
    2 SpeciesB Count 4
    3 SpeciesC Count 2

    I was expecting it to list Species A three times, Species B four times and Species C twice, but as you can see it has just repeated my table.

    Could you please tell me what I’ve done wrong?

    Thanks!
    Deb.

    • Michiel van der Blonk on

      Well, it’s working fine, it’s doing exactly what it should. It is converting a table to a list. The program has no idea what the word Count means. The table is not repeated, it is written in a different ‘format’. For what you have in mind you need an entirely different macro that repeats rows a number of times.

  19. greg on

    i have it enabled but when I use it it says the file could not be found???

  20. Becky on

    This macro is fantastic. So helpful to me, thanks.

    Now, am trying to tweak it to be able to start with a table that has TWO titles (instead of one) which will create ColValue1 AND ColValue2 to the left of the data column … any pointers from anyone?

    thanks again!

  21. Becky on

    i got it (replace these portions of the code) ….

    Dim rowVal As Variant
    Dim colVal As Variant
    Dim colVal2 As Variant
    Dim val As Variant

    Set table = ActiveCell.CurrentRegion
    Set rngColHead = table.Rows(1)
    Set rngColHead2 = table.Rows(2)
    Set rngRowHead = table.Columns(1)
    Set rngData = table.Offset(2, 1)
    Set rngData = rngData.Resize(rngData.Rows.Count – 1, rngData.Columns.Count – 1)

    ActiveWorkbook.Worksheets.Add

    ActiveCell.Value = “Line#”
    ActiveCell.Offset(0, 1).Value = “Project”
    ActiveCell.Offset(0, 2).Value = “Budget Year”
    ActiveCell.Offset(0, 3).Value = “Journal Date”
    ActiveCell.Offset(0, 4).Value = “Amount”
    ActiveCell.Offset(1, 0).Select

    Dim n As Long
    For Each cel In rngData
    If cel.Value 0 Then
    colVal = rngColHead.Cells(cel.Column – table.Column + 1)
    colVal2 = rngColHead2.Cells(cel.Column – table.Column + 1)
    rowVal = rngRowHead.Cells(cel.Row – table.Row + 1)
    n = n + 1
    ActiveCell.Value = n
    ActiveCell.Offset(0, 1).Value = rowVal
    ActiveCell.Offset(0, 2).Value = colVal
    ActiveCell.Offset(0, 3).Value = colVal2
    ActiveCell.Offset(0, 4).Value = cel.Value
    ActiveCell.Offset(1, 0).Select
    End If
    Next

  22. becky on

    Can’t figure this one out … can anybody help?

    Would like to have the macro read the data vertically instead of horizontally (all the way down the first column, then all the way down the second etc.)

    thanks

  23. Dave Piasecki on

    Michael,
    Thanks very much for this. This is soooo much easier than the mostly manual methods I’ve done in the past. It’s surprising Microsoft still doesn’t have this functionality incorporated into Excel (they’ve had pivot tables for 15 years).

  24. Michiel van der Blonk on

    Yeah, exactly! Microsoft fails to realize people work in their own way, not the Microsoft way. Only when it’s too late do people realize that their own way was flawed. And then they need tools!

  25. Pat on

    Never left comment, but your vba is life saver. You are the best of the best.
    I’m working on data import from two quit large tables to SQL.
    – cannot find word to thank you enough –

  26. Peter on

    Great piece of code and very helpful – where has this add-in been all my life! Works fine in Office 2010 once I worked out where the activation button sat in the Ribbon and how to deal with tables with multiple row label columns and zero values in the table itself. A little bit of data filtering and then pasting into a new sheet.

    You are right why on earth hasn’t this functionality been put into Excel already…

    Cheers!

  27. Abu Yahya on

    Another way to do that is using Pivot Table Wizard
    Step 1. Choose multiple consolidation ranges
    Step 2. Choose I will create the page fields
    Step 2b. Select Range of the table then Add to
    Step 3. Choose New Worksheet
    Click Finish
    on the new sheet – Pivot table field list –> uncheck [ ] Row and [ ] Column
    it will one value shows then double click the cell (usually A4)
    then ‘TADA!!!”

    • Michiel van der Blonk on

      Wow! I did not know this, and I guess most people didn’t know this. I am glad you told me, now I know about a built in option for my macro!

      It does include ‘empty cells’ but that is easily filtered out.

      I still wish to write the ‘reverse’ of it, which creates a pseudo pivot table. The pivot table namely only works with numeric data. Unless you have a solution for that as well.

    • AndyR on

      TADA is right! Awesome trick, thanks! The whole time I’m going through that thinking “this can’t be right”, but that last double-click is worth the trouble :).

  28. george on

    i love you, you saved me from creating index files.

  29. Vipul on

    This is what I was looking for…..
    Thank you Michiel

  30. tp on

    You almost brought a tear to my eye. Love it!

  31. ryang on

    BAAM! thanks Michiel..Exactly what I needed in no time at all, and imported right into access too

  32. Michelle Vestey on

    I am trying to run this on a spreadsheet that is 2,083 rows (incl header row) by 60 columns in Excel 2010 but it hangs up after producing around 2,000 rows of output.

    Any suggestions?

    • michiel on

      Michelle, sorry for the late reply, I don’t regularly check the messages on this blog. Anyway, there is a great comment by Abu Yahya above. Check it out, I will put that in my article as well, since it is most of the times a quick and easy solution!

  33. Giuseppe on

    It saved my day (weeks actually :=)).
    I really appreciate, thanks for sharing.

  34. kentuckyfreethinker on

    “An even quicker solution” is brilliant! Saved me hours of work. Thanks for sharing.

  35. https://Chinabiomet.com on

    would you mind letting me know whch webhostt you’re utilizing?
    I’ve loaded your blog in 3 completely different internet browsers and I must say this blog loads a lot
    quickdr then most. Can you recommend a good weeb hosting provider at a fair price?
    Thank you, I appreciate it! Thanks. By the way, it would be a lot more helpful if
    yoou can add mmy twitter back :)

  36. MT on

    the quicker solution #7 really worked for me! Thanks for the great detailed instruction, including how to find the pivot table wizard. It really worked! When I had just the one number and clicked to reveal all the results was the highlight of my week, ha! :) Thanks again! Saved me tons of time and staved off carpal tunnel – this was 100 columns of data, whew!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: