Displaying data in Excel Pivot Tables

Question

I am working on a project and being able to use a pivot table would make life much easier. I need essentially for one column to become the column labels which is fine, however I would like to display values (in my case counts) going vertically down the page, rather than being inserted again along the horizontal?

Cheers for any help

Answer 1

i think it might help to post the sample data in your post rather than a comment, it's a bit tricky to delimit from there. but i think this might be what you're asking.

if 1) you want a count of each column name as a row level pivot, you'll get only one valid cell for each row/column in the resulting pivot. the row context for the column value and the count ( distinct ) of the row will always match 1:1: example below:

Data (col2 is the formula =COUNTIFS($A$2:$A$7,A2) and shows the number of times a, b, or c appears in column A) :

+------+------+--------+------+
| col1 | col2 |  col3  | col4 |
+------+------+--------+------+
| a    |    3 | data   |    1 |
| b    |    2 | text   |    1 |
| c    |    1 | number |    1 |
| a    |    3 | text   |    1 |
| a    |    3 | text   |    1 |
| b    |    2 | number |    1 |
+------+------+--------+------+

Pivot (notice how a only shows data for 3, b for 2, and c for 1) :

+-------------+---------------+---+---+-------------+
| Sum of col4 | Column Labels |   |   |             |
+-------------+---------------+---+---+-------------+
| Row Labels  | a             | b | c | Grand Total |
| 1           |               |   | 1 | 1           |
| 2           |               | 2 |   | 2           |
| 3           | 3             |   |   | 3           |
| Grand Total | 3             | 2 | 1 | 6           |
+-------------+---------------+---+---+-------------+

if however, 2) you are asking for each column with a corresponding "count of" label appended to it's name for the pivot table, Try adding a helper column in the source table =a2&" - "&countifs(A:A,A2) (for our example in column E / col5 AND assuming the column A is desired pivot header). The new col5 will show values of a - 3, b - 2, and c - 3 (for our example). Make col5 the pivoted column or row and do the rest of your pivoting with the added label of how many rows you are using.

result of revision below: Data :

+------+------+--------+------+-------+
| col1 | col2 |  col3  | col4 | col5  |
+------+------+--------+------+-------+
| a    |    3 | data   |    1 | a - 3 |
| b    |    2 | text   |    1 | b - 2 |
| c    |    1 | number |    1 | c - 1 |
| a    |    3 | text   |    1 | a - 3 |
| a    |    3 | text   |    1 | a - 3 |
| b    |    2 | number |    1 | b - 2 |
+------+------+--------+------+-------+

Pivot :

+-------------+---------------+--------+------+-------------+
| Sum of col4 | Column Labels |        |      |             |
+-------------+---------------+--------+------+-------------+
| Row Labels  | data          | number | text | Grand Total |
| a - 3       | 1             |        | 2    | 3           |
| b - 2       |               | 1      | 1    | 2           |
| c - 1       |               | 1      |      | 1           |
| Grand Total | 1             | 2      | 3    | 6           |
+-------------+---------------+--------+------+-------------+

hope it helps.