Unpivot Tables add-in: an easy tool to transform crosstab tables to flat lists

Crosstab tables are also called two-dimensional (2D), or pivot tables. They present data in a concise and easy to read matrix with headers for columns and rows. But this layout is not suitable for PivotTable, charting, filtering, export to external systems, etc. That is why getting an accurate flat list from a crosstab table is essential for any powerful data analysis.

Unpivot Table converts crosstab tables to flat lists precisely and without macros:

  • Flatten crosstab table to a list in seconds
  • Transform complex tables with multilevel headers
  • Correctly redesign tables with merged or blank cells
  • Preserve column headers
  • Preserve cell formatting

(Note: the video may not reflect the latest upgrades. Please use the guide below.)

 

Add "Unpivot Table" to Excel 2016, 2013, 2010, 2007

Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).

Download XLTools Add-in

to Excel 2016, 2013, 2010, 2007  Works in: Microsoft Excel 2016 – 2007, desktop Office 365 (both 32 bit and 64 bit).

 

Learn your way around:

 

How to unpivot a crosstab table in Excel to a flat list

  1. Click the ‘Unpivot table’ button on XLTools tab > A window will appear.
  2. Select a crosstab table including table headers.
    Tip: click any cell in a table, and the entire table will be automatically selected.
  3. Validate table headers:
    In a simple table: Header Rows = 1, Header Columns = 1.
  4. Choose where to place the result: to a new or an existing worksheet.
    To place the flat list in an existing worksheet, select the starting cell (top left).
  5. Click OK > Done. The add-in will autofit column width for the flat list.

Unpivot a simple crosstab table into a flat list

 

How to unpivot a complex crosstab table with multilevel headers

Some crosstab tables may have a complex structure and multilevel headers. You can flatten them as well with XLTools:

  1. Click the ‘Unpivot table’ button on XLTools tab > A window will appear.
  2. Select a crosstab table including table headers.
    Tip: click any cell in a table, and the entire table will be automatically selected.
  3. Validate table headers:
    • Header Rows: the number of rows that constitute table header on the top.
    • Header Columns: the number of columns that make up table header on the left.
  4. Choose whether to place the result in a new or an existing worksheet.
  5. Click OK > Done. The add-in will autofit column width for the flat list.

Unpivot a complex crosstab table with multilevel headers to a flat list

 

How to flatten a table with blank cells

If your crosstab table contains blank cells, then the correnspoding cells of the flat list will also be blank. However, empty values in a flat list not provide any meaningful information for analysis. So we recommend that you do the following:

  • If empty cells are located in table headers: fill out missing header labels prior to Unpivot.
  • If empty cells are located in table body: you can omit corresponding rows in the flat list:
  1. Click the ‘Unpivot table’ button on XLTools tab.
  2. Select a crosstab table including table headers.
  3. Validate table headers.
  4. Check the box "Skip rows with empty values".
  5. Choose where to place the result.
  6. Click OK > Done.

Flatten a crosstab table: skip empty cells

 

How to flatten a table with merged cells

  1. Click the ‘Unpivot table’ button on XLTools tab.
  2. Select a crosstab table including table headers.
  3. Validate table headers.
  4. Check the box "Fill merged cells with duplicates":
    • If merged cells are located in table headers: the contents of merged header cells will be automatically duplicated in each corresponding row of the flat list.
    • If merged cells are located in table body: the values from merged cells will be automatically duplicated in each corresponding cell of the flat list.
  5. Choose where to place the result.
  6. Click OK > Done.

    Flatten a crosstab table: duplicate values from merged cells

     

    How to flatten a table and preserve table headers

    1. Click the ‘Unpivot table’ button on XLTools tab.
    2. Select a crosstab table including table headers.
    3. Validate table headers.
    4. Check the box "Preserve headers":
      • Where possible, the add-in will duplicate headers from the crosstab table.
      • Table categories will be automatically assigned the label "Category".
      • Table variable values will be automatically assigned the label "Value".
    5. Choose where to place the result.
    6. Click OK > Done.

    Unpivot a crosstab table and preserve headers

     

    How to flatten a table and preserve cell formatting

    1. Click the ‘Unpivot table’ button on XLTools tab.
    2. Select a crosstab table including table headers.
    3. Validate table headers.
    4. Check the box "Preserve cells format":
      Each cell will keep its format in the resulting flat list, including fill color, borders, font color, conditional formatting colors, general/number/date/currency format, etc.
    5. Choose where to place the result.
    6. Click OK > Done.

    Note: large tables with heavy formatting take longer to process.

    Unpivot crosstab table in Excel: keep cell format

     

    Which tables does the Unpivot Table add-in process

    Unpivoting a table essentially means that data from your source crosstab table are copied – and rearranged to form a flat list. Your source table is not changed and remains intact. To prevent data corruption, instead of cell references, functions or formulas in the crosstab table, XLTools Unpivot Table inserts their values into the resulting flat list.

    The word 'table' in Excel is commonly used to name actually different things:

    • A 'true' table – a named range with table style formatting (operation 'Format as Table'). Can be converted to a simple range.
    • A range – a simple range of cells in a table-like layout, with or without formatting of background color, borders, etc. Can be converted to a 'true' table.
    • A PivotTable – a dynamic table generated via Excel PivotTable operation. Сells cannot be edited.

    XLTools Unpivot Table allows you to flatten 'true' tables and ranges. To flatten a PivotTables, first, copy the PivotTable and paste as values – this will create a simple range you can unpivot.

     

    Any questions or suggestions? Please leave your comment below.

     

    Any questions or suggestions? Please leave your comment below.

    6 Responses to Unpivot Tables add-in: an easy tool to transform crosstab tables to flat lists

    1. Jeong yong geol says:

      Very good!!

    2. Richard says:

      Great tool, thank you, but unpivot table seems to be rounding my values to the nearest integer as it unpivots them – why? Can I make it return the original (decimal) value? Thanks

      • Maria Balobanova says:

        Hello Richard! This sounds odd – Unpivot Table is supposed to keep number format of the original value. I cannot reproduce the error on our machines. I have sent you an email, so we could figure out what the problem is.

    3. Wilkenes says:

      Good day!
      I’m starting developing for office. I would like some tips. What would amelhor C # or VB.Net? What if you could’ve release the code ”
      “UNPIVOT Tables in Excel.” to study?

    Leave a Reply

    Your email address will not be published. Required fields are marked *