Transform a crosstab table in Excel to a flat list quickly and accurately

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.)

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

  1. Click theUnpivot tablebutton 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 theUnpivot tablebutton 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 theUnpivot tablebutton on XLTools tab.
    2. Select a crosstab table including table headers.
    3. Validate table headers.
    4. Check the boxSkip 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 theUnpivot tablebutton on XLTools tab.
  2. Select a crosstab table including table headers.
  3. Validate table headers.
  4. Check the boxFill 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 theUnpivot tablebutton on XLTools tab.
  2. Select a crosstab table including table headers.
  3. Validate table headers.
  4. Check the boxPreserve 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 theUnpivot tablebutton on XLTools tab.
  2. Select a crosstab table including table headers.
  3. Validate table headers.
  4. Check the boxPreserve 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 PivotTable, first, copy the PivotTable and paste as values – this will create a simple range you can unpivot.

Any questions or suggestions?
close window
How can we help?