Excel Pivot Table with No Summary Fields

Posted on 2016-11-18
Last Modified: 2016-11-22
hi Excel Folks

please see attached the example spreadsheet for two  basic project management sheets with raw data in Sheet 1 and Pivot Table in Sheet 2.

I would like to see projects detail per each PM, so I have used a Pivot Table to generate that view as well as summarised budget and cost at left, but then I want to see the other project details at right. but for all fields in the Pivot Table, Excel must choose a way to summarise them even for fields not applicable in logic, such as description, start date, close date etc.

currently for illustration purpose, I manually put the column names at right and they are not part of the Pivot Table.

how can I generate a single table containing all the info (views) i need?

thanks heaps
Question by:bbao
  • 4
  • 3
LVL 81

Expert Comment

ID: 41893947
The Classic PivotTable Layout may be much more to your liking. You enable it on the Pivot Table Options...Display dialog by checking the box for Classic PivotTable layout (enables dragging of fields in the grid).

Once you have done that, drag Description, Start Date and Close Date into the Row items box in the PivotTable Field chooser.

Next you will want to rightclick Start Date and Close Date on the PivotTable, and choose to Ungroup...

Finally, you will want to rightclick Project Name and uncheck the Subtotal item in the resulting dialog. Repeat with Description, Start Date and Close Date.

See attached workbook to see what it looks like.
LVL 37

Author Comment

ID: 41894429
thanks heaps for your reply byundt

it looks like something i am after but i just can't produce the same result using my Excel 2013.

what's your version? is it a version specific solution?

thanks again.
LVL 81

Accepted Solution

byundt earned 500 total points
ID: 41894535
Although I have multiple versions of Excel available to me, I happened to have been using Excel 2013 when I worked on your problem.

To investigate your difficulty reproducing my results, I pasted my previous Comment in a textbox that I inserted on worksheet Sheet2. I then rightclicked the sheet tab and chose to make a copy of that worksheet. I then followed the instructions from my previous Comment step by step. I got exactly the same results as I had previously posted in Sheet2 (2) of the workbook I attached to my previous post.

I don't know if you can view it, but here is a 4 minute video clip of me going through the suggested steps. No audio other than mouse clicks.

Could you please post a workbook showing your failed attempt to follow my instructions? Perhaps I can figure out from that which step(s) you missed.
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

LVL 37

Author Comment

ID: 41894588
thanks so much for your brilliant demonstration Brad! I really appreciate your time and effort.

i didn't realise the "Classic PivotTable Layout" referred in your first comment is actually an option name and it is hiding under Pivot Table Options > Display. that's the main reason i got confused. also, unchecking Subtotal for non-calculation fields is the key to make the layout readable. your demonstration video well explained everything i need.

i have revised the Pivot Table and its layout and it now looks like what i need (project details in one line as they are in raw data), but it seems no way for me to mix the text fields (Owner, Client and Description) and summarised fields (Budget, Cost and Completed). i have to leave all summarise fields always at right. Please see attached the version 2 for your reference.

moreover, i notice the data shown in the Pivot Table in Sheet 2 are totally different from the raw data in Sheet 1, not matched at all. Is that caused by wrong settings in Pivot Table? Or is it caused by the raw data that are generated in random numbers?

BTW, i followed your instruction to check Ungroup for each field but it seemed no any change noticeable. is it a necessary step for every field?

thanks again!
LVL 37

Author Closing Comment

ID: 41894589
brilliant answer! thanks so much!
LVL 81

Expert Comment

ID: 41894960
Your raw data and the PivotTable were disassociated because your raw data was randomly generated--and those pesky RANDBETWEEN functions kept updating as the PivotTable was being built. I tried refreshing the PivotTable, and it updated--but so did the raw data, once again leading to disassociation. But if you do a Copy and Paste Special...Values on the raw data, you can then refresh the PivotTable and expect fidelity.

Apparently I also used Excel 2016 in trying to solve your problem. That version tried to be helpful and automatically grouped the dates by Quarter and Year and generated extra columns for them in the PivotTable report. By going through the Ungroup steps shown in the video, I eliminated those columns. And the Ungroup steps didn't hurt in Excel 2013, so I kept on doing it when I got back to the problem in Excel 2013. As you noticed, however, Excel 2013 doesn't require the Ungroup steps on the two date fields.

LVL 37

Author Comment

ID: 41897122
yes, a new pivot table correctly showed the related data and calculation from a copy of raw data disassociated from the source full of random values.

FYI - just for curiosity, i opened the v2 sheet using my Excel 2008 running on my vintage iMac, the pivot table was read-only and the same steps couldn't produce the same layout once a new pivot table created.

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question