Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Excel Pivot Table with No Summary Fields

Posted on 2016-11-18
Medium Priority
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 2000 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.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
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.

579 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