Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

Excel Pivot Table with No Summary Fields

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
  • 4
  • 3
1 Solution
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.
bbaoIT ConsultantAuthor Commented:
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.
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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

bbaoIT ConsultantAuthor Commented:
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!
bbaoIT ConsultantAuthor Commented:
brilliant answer! thanks so much!
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.

bbaoIT ConsultantAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now