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:Bing CISM / CISSP
  • 4
  • 3
LVL 80

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

by:Bing CISM / CISSP
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 80

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.
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

LVL 37

Author Comment

by:Bing CISM / CISSP
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

by:Bing CISM / CISSP
ID: 41894589
brilliant answer! thanks so much!
LVL 80

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

by:Bing CISM / CISSP
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now