I thought this would be easy but I'm really struggling.

I have items across different columns and I need a count of all the items across each column.


enter image description here

Desired Pivot Table:

enter image description here

What I'm trying is this, but it doesn't show the values from the other columns and the counts for each column aren't right.

enter image description here

2 Answers 2


Since you're not considering Test number (1,2,3,4,5) on your final output, just use test and color. I copied each column and pasted to build the cells below. The table names I just copied one cell, then pasted while selecting multiple cells. It does alter your original data, but it is sometimes faster to just re-arrange your data.

enter image description here

enter image description here

enter image description here


Same approach as 'Sun', however you keep the test data as they are and unpivot them with the free Microsoft Excel add-In Power Query (from Excel 2010). In this way you can keep your data source as it is and transform it quickly.

Having the data in a tabular form is a good practice for building flexible pivot table. Having the following table structure, allow you to make the pivot you want.

Test no.    Test Attribute    Value

To make the transformation (actually you unpivot your data) follow the description on the MS Website.

  1. Import the data in Power Query (Ribbon Power Query -> from Table, cursor must be somewhere in your data)
  2. Select the first column (test), on the Ribbon Transform, click on Unpivot Columns > Unpivot Other Columns

    enter image description here

  3. Close and load to Excel (Ribbon Home)

You will get this list as data source for the pivot table:

enter image description here

Here the script generated from the Power Query UI.

    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Test", Int64.Type}, {"Test A", type text}, {"Test B", type text}, {"Test C", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Test"}, "Attribute", "Value")
    #"Unpivoted Other Columns"

The pivot table is then based on the new list:

  • ROWS = Value
  • COLUMN = Attribute
  • VALUES = Attribute (count)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.