I would like to enter data (numbers) into a table with rows by date and columns as categories of activities. And then in a pivot table reverse the rows & columns so that dates are columns and activities are rows.



I wish to have both Row and Column totals in the pivot table but so far it displays row totals but not column totals.

I would also like to filter the dates and again am not sure how to do that in this pivot table.

The attached file has the a tab with the data table and a separate tab with the pivot table.

Attached Files

Pivot Test.xlsx (20.9 KB, 1 views)

This is because you're pivoting data which is essentially already pivoted!
In the attached is:
1. An ugly workaround added to your existing which is a calculated field. Don't use it, it's very inflexible (and it doesn't change if you add/remove things to the values area).

2. A second pivot at cell O3 which works as I think you want.
It's your data, unpivoted by Power Query in the background and a new pivot table created from the result.
In the background your data is transformed to look like this:
2023-02-28_222447.png

Attached Files

msofficeforums50506Pivot Test.xlsx (32.8 KB, 1 views)

I apologize for not seeing your reply sooner.

I had suspected there was something about the way the Data Table was structured that was the problem.

I'm not familiar with Power Query but was able to flail around and open it so will see if I can learn how to use it. Is it in any way similar to Power BI which I have used in the past? Any suggestions for learning would be appreciated.

Thanks very much.

Power Query is also part of Power BI.


Take a look at Articles - Excelguru
Ken Puls is well versed in Power Query etc.