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
LVL 1
243johnmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vastoCommented:
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.
0
243johnmAuthor Commented:
I tried that, but couldn't figure out how to total by Month or Week on the Date field!
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
0
vastoCommented:
Check the uploaded file
Result.xlsx
0
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

243johnmAuthor Commented:
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
0
vastoCommented:
Select the pivot table, the Pivot table Fields List will be shown. Click Date field in Row Labels panel  and a pop up menu will be shown . Click "Field Settings" and  in the dialog box select "None" for subtotals ( default is automatic). Do this for each of the field except Team
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
243johnmAuthor Commented:
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
0
vastoCommented:
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
0
243johnmAuthor Commented:
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
0
vastoCommented:
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
0
243johnmAuthor Commented:
Tried that, it didn't change...
0
vastoCommented:
Select the column in the excel worksheet  not the pivot table column
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.