243johnm
asked on
Adding Data Columns to an Excel 2010 Pivot Table Seems Impossible!
I have an Excel sheet that contains the following columns:
Team
Proposal Number
Company
Date
Project
Project Value
I created a pivot table, grouping the Date field by months, with a second grouping by Team, with the Project Value as the summarized field.
In each group created by the Pivot Table, I also want to list the Date, Proposal Number, Company Name & Project. Please see the attached sheet for the way I want it to look. Groupings---Data-Columns-in-Exce.xlsx
This is something that is easy enough to do in Crystal Reports, but I cannot find a way to do this in Excel Pivot Tables. My client wants something they can modify themselves, which means I cannot do it in Crystal Reports.
If, as I fear, this cannot be done in a Pivot Table, is there some other way to do it in Excel?
Thanks very much,
John
Team
Proposal Number
Company
Date
Project
Project Value
I created a pivot table, grouping the Date field by months, with a second grouping by Team, with the Project Value as the summarized field.
In each group created by the Pivot Table, I also want to list the Date, Proposal Number, Company Name & Project. Please see the attached sheet for the way I want it to look. Groupings---Data-Columns-in-Exce.xlsx
This is something that is easy enough to do in Crystal Reports, but I cannot find a way to do this in Excel Pivot Tables. My client wants something they can modify themselves, which means I cannot do it in Crystal Reports.
If, as I fear, this cannot be done in a Pivot Table, is there some other way to do it in Excel?
Thanks very much,
John
vasto
This should be done using subtotal. Create a flat table in excel with all your data and then select "Data"->Subtotal. Set subtotal on team change.
ASKER
I tried that, but couldn't figure out how to total by Month or Week on the Date field!
John
I created a pivot table, grouping the Date field by months, with a second grouping by Team, with the Project Value as the summarized field.That's why I went to Pivot Tables, which allows you to summarize by Month or week (actually by number of days).
John
Check the uploaded file
Result.xlsx
Result.xlsx
ASKER
Thanks!
When I tried that, I ended up with summaries for every level:
Month
Team
Proposal Number
Company
Date
Project
How did you avoid that?
John
When I tried that, I ended up with summaries for every level:
Month
Team
Proposal Number
Company
Date
Project
How did you avoid that?
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's great vasto, thanks very much! Two quick questions:
1. Is there anyway to change the format of the Month field? It just says Jan, Feb, etc., and I want it to say 2013.01, 2013.02, etc.
2. Is there anyway to add a blank row after each summary line? I found "Insert Row After Each Item" but no "Insert Row After Each Summary"
Thanks again,
John
1. Is there anyway to change the format of the Month field? It just says Jan, Feb, etc., and I want it to say 2013.01, 2013.02, etc.
2. Is there anyway to add a blank row after each summary line? I found "Insert Row After Each Item" but no "Insert Row After Each Summary"
Thanks again,
John
Currently the Month is showing 2013.01. Do you want to show Jan, Feb ...instead ?
I don't know the answer of the 2nd question
I don't know the answer of the 2nd question
ASKER
Your sample shows 2013.01, mine (based on the Excel sheet I will be creating the pivot table from) shows Jan.
In Crystal Reports, all you have to do is right mouse on a field (or summary field) and you can change the format. (even in a cross-tab, the equivalent of a pivot table).
In a pivot table, is there an easy way to change the format of those summary fields?
Thanks,
John
In Crystal Reports, all you have to do is right mouse on a field (or summary field) and you can change the format. (even in a cross-tab, the equivalent of a pivot table).
In a pivot table, is there an easy way to change the format of those summary fields?
Thanks,
John
You can select the column - right click and set the format. If you want to see something like 2013.1 you need to use custom format yyyy.m
ASKER
Tried that, it didn't change...
Select the column in the excel worksheet not the pivot table column