Unpivot Table Add-in for Excel in the Office Store

Convert a crosstab table to a flat list

Crosstab tables, or two-dimensional pivot tables are an excellent tool to present data. However, they are not suitable for data analysis, PivotTable generation, data export to external systems, filtering, etc.

The Unpivot Table app will redesign tables quickly and accurately:

  • Transform crosstab tables to a flat list
  • Redesign tables even with multilevel headers

XLTools.net Unpivot Table is built on Microsoft platform. MS Office Add-ins (formerly known as Office Apps) work across Excel Online, Excel 2016 and Excel 2013 desktop, Excel for iPad, Excel for Mac.

XLTools Unpivot Table App overview


Learn your way around:


How to add the Unpivot Table Add-in for Excel from the Office Store

Download the add-in from the Office Store website, or directly from Excel interface:

  1. Open Excel > Insert tab > In the Add-ins group click ‘Store’ or ‘Office Add-ins’.
  2. Search for XLTools Unpivot Table in the Store > It is free, so just click ‘Add’.
  3. The add-in’s task pane will appear on the sheet. Drag and place it as you wish.

Note: you should be singed in to your Microsoft account to manage products and subscriptions:
File > Account > User Information > Sign in.

Add Unpivot Table Add-in from the Office Store to Excel 2016 Excel 2013

Add XLTools.net Unpivot Table Add-in from the Office Store to Excel Online


How to transform crosstab 2D table in Excel to a flat list

Follow the steps on the add-in’s pane:

  • Step 1. Click ‘Select’ > Select the range of the crosstab table you want to unpivot.
  • Step 2. Select any empty cell as the starting cell to place the flat list.
  • Step 3. If your crosstab table has empty cells, you may choose to ‘Skip empty values’ in the flat list.

Please note: any formulas will be overwritten with values.

XLTools.net Unpivot Table Add-in output: flat list

Done! The flat list is ready for further analysis.


Does the Unpivot Table Office Add-in work offline

Office add-ins require Internet connection.

But check out our desktop Unpivot Table (requires installation). You can run all the same operations, and it also works offline.

Read more on the difference between Office Add-ins and desktop version.

 

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

 

7 Responses to Unpivot Table Add-in for Excel in the Office Store

  1. Radek says:

    Hi Peter,

    It would have been very useful not only for for me to unpivot cells just as they are with formats and colors. 😉

    • Peter Liapin says:

      Hi Radek,

      I see what you mean, but it is not yet possible to add such an option with the current version of JavaScript API for Office (Excel Apps API) which we are using to build this kind of apps.

      On the other hand we have COM Add-In which is build using much more API and we can think about the way how to implement it there. You can read more about current version of this tool in COM based Add-In.

  2. Ryan says:

    Is there a function to use this with multiple columns in the row area?

    • Peter Liapin says:

      Hi Ryan,

      Your question is very similar to question above from Sam and as I replied to Sam we will add this option in the near feature. I will let you know when it is available.

  3. sam says:

    How do you handle multiple columns in the Row area

    • Peter Liapin says:

      Hi Sam,

      Thank you for this question! We plan to add this ability in the near feature. I will let you know when it is available.

      • Rodrigo Polacco says:

        Hello,

        thanks for the app. I had the same question and I’m waiting out the new version that deals with multiple headers.

        As I had to deal with the problem developed a routine in VBA to use as palliative while awaiting the new version. I put the code below, it can help others with the same need.

        Cheers,
        Rodrigo

        Sub CriaTabelaDynamica()

        ‘Declara as variáveis
        Dim PastaInput As String
        Dim PastaOutPut As String
        Dim CabecalhoColunaAtual As String
        Dim CabecalhoLinhaAtual As String

        Dim CabecalhoInicioLinha As Integer
        Dim CabecalhoFinalLinha As Integer
        Dim CabecalhoInicioColuna As Integer
        Dim CabecalhoFinalColuna As Integer

        Dim ImportRange As Variant
        Dim ExportSheet As Object

        Dim h As Integer
        Dim i As Integer
        Dim j As Integer
        Dim k As Integer
        Dim l As Double

        ‘Puxa os parâmetros
        PastaInput = ActiveWorkbook.Sheets(“Parametros”).Range(“B1”).Value
        PastaOutPut = ActiveWorkbook.Sheets(“Parametros”).Range(“B2”).Value

        CabecalhoInicioLinha = ActiveWorkbook.Sheets(“Parametros”).Range(“B5”).Value
        CabecalhoFinalLinha = ActiveWorkbook.Sheets(“Parametros”).Range(“C5”).Value
        CabecalhoInicioColuna = ActiveWorkbook.Sheets(“Parametros”).Range(“B6”).Value
        CabecalhoFinalColuna = ActiveWorkbook.Sheets(“Parametros”).Range(“C6”).Value

        Set ImportRange = ActiveWorkbook.Sheets(PastaInput).Cells(CabecalhoFinalLinha, CabecalhoFinalColuna).CurrentRegion

        ‘ Adiciona Planilha de Exportação
        Sheets.Add After:=Sheets(Sheets.Count)
        ActiveSheet.Name = PastaOutPut & Sheets.Count
        Set ExportSheet = ActiveSheet

        l = 1

        ‘Roda 1x para cada coluna de dados
        For j = CabecalhoFinalColuna + 1 To ImportRange.Columns.Count

        ‘Roda 1x para cada linha de dado
        For k = CabecalhoFinalLinha + 1 To ImportRange.Rows.Count

        For h = CabecalhoInicioColuna To CabecalhoFinalColuna

        For i = CabecalhoInicioLinha To CabecalhoFinalLinha
        ‘Normaliza Label Coluna
        If ImportRange.Cells(i, j).Value “” Then
        ExportSheet.Cells(l, i) = ImportRange.Cells(i, j).Value
        Else
        ExportSheet.Cells(l, i) = ExportSheet.Cells(l – 1, i)
        End If

        Next i

        ‘Normaliza Label linha
        If ImportRange.Cells(k, h).Value “” Then
        ExportSheet.Cells(l, i + h – 1) = ImportRange.Cells(k, h).Value
        Else
        ExportSheet.Cells(l, i + h – 1) = ExportSheet.Cells(l – 1, i + h – 1)
        End If

        Next h

        ‘Dado
        ExportSheet.Cells(l, i + h – 1) = ImportRange.Cells(k, j).Value

        l = l + 1

        Next k

        Next j

        End Sub

Leave a Reply

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