excelpivotpivot-tableexcel-2016

Excel Pivot Table - Turn two columns with same values into a matrix pivot table with column names as rows and value count in columns

I'm trying to make a pivot table that would count values that are in both columns and present them together with column titles as rows.

Sample dataset:

id test1 test2
123 a b
456 a a
12 b c
34 b d
56 c a
78 d b
90 a a
11 b n
2 n b
3 d c
4 a a
5 c c
6 b b
7 a c

Pivot counts the letters. I need to present it like

                        
        a   b   c   d   n   Total
test1   5   4   2   2   1   14
test2   4   4   4   1   1   14

I can only do separate pivots for each other but not together like that. Pivot nests the values even though they are same. Is there a way to do it?

Solution

  • To achieve this you need to 'unpivot' your test1 and test2 columns and get new dimensions (say, 'attributes' and 'values'), and then configure a pivot table where dimension 'attributes' will be on Rows, 'values' will be on Columns and Value will be "Count".

    If your CSV can change in time (and columns to unpivot also may vary), it makes sense to use an alternative BI tool SeekTable where CSV unpivot can be performed on-the-fly, and to refresh your reports it is enough to upload a new CSV data.

    Here is a report configured by your sample data: unpivot example

    A link to the published report: https://www.seektable.com/public/report/632755ae7ee34c3b96271c2a39b8ca93