Unpivot Tables in Excel

A 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. But this layout is not suitable for PivotTable, charting, export, etc. That is why getting an accurate flat list from a crosstab table is essential for any powerful data analysis.

Unpivot Table converts tables to flat lists precisely:

  • Convert a crosstab table to a list in seconds
  • Transform complex tables with multilevel headers
  • Correctly redesign tables with merged or blank cells

XLTools Unpivot Table Feature Overview


Add XLTools Unpivot Table to Excel 2016-2007

Works with: Microsoft Excel 2016, 2013, 2010, 2007, and desktop version of Office 365.

Download XLTools Add-in


Learn your way around:


How does Unpivot Table add-in work

The word ‘table’ in Excel is commonly used to name actually two different things:

  • A range with style formatting (‘Format as Table’) – a ‘true’ table as Excel interprets it.
  • A data range with a table-like layout, with or without manual formatting of background and borders.

XLTools allows you to unpivot any crosstab data in Excel.

Note, that the resulting flat list will contain formatted values, e.g. 10%, 12-MAR-2015, $40. As well as computed results instead of formulas.


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

  1. Select the table including headers.
  2. Click the ‘Unpivot table’ button on the XLTools tab > A window will appear.
  3. Validate the number of header rows and columns:
    In a simple table: Header Rows = 1, Header Columns = 1.
  4. Choose where to place the result:
    To place the flat list in an existing worksheet, select the starting cell (top left).
  5. Click OK > Done.

How to unpivot a table in Excel


How to unpivot a complex table with multilevel headers

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

  1. Select the table including all headers.
  2. Click the ‘Unpivot table’ button on the XLTools tab > A window will appear.
  3. Validate the number of header rows and columns:
    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.

How to unpivot a complex table in Excel


How to flatten a table with merged cells

If table headers contain merged cells, XLTools will process them automatically.

The contents of merged header cells will be used for each corresponding row in the flat list. No action required, unpivot as usual.

Unpivot Table How to manage merged cells in headers


How to flatten a table with blank cells

  • Blanks cells in table headers:
    Fill down missing labels in headers, otherwise the resulting flat list will also contain blanks. Or, merge header cells where relevant and the add-in will process those.
  • Blank cells in table body:
    Empty cells (cells with ‘null’ value) in table body will result in corresponding blank values in the flat list. Such rows do not provide any meaningful information for analysis. So you may choose to ‘Skip rows with empty values’ altogether.

Unpivot Table How to manage blank cells

 

Any questions or suggestions? Contact us or leave your comment below.

 

6 Responses to Unpivot Tables in Excel

  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 *