Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel Pivot Table with No Summary Fields

Posted on 2016-11-18
7
Medium Priority
?
174 Views
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
bbao
my_projects.xlsx
0
Comment
Question by:bbao
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 81

Expert Comment

by:byundt
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.
-my_projectsQ28984157.xlsx
1
 
LVL 37

Author Comment

by:bbao
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.
0
 
LVL 81

Accepted Solution

by:
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.
1
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 37

Author Comment

by:bbao
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!
my_projects_v2.xlsx
0
 
LVL 37

Author Closing Comment

by:bbao
ID: 41894589
brilliant answer! thanks so much!
0
 
LVL 81

Expert Comment

by:byundt
ID: 41894960
Bing,
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.

Brad
1
 
LVL 37

Author Comment

by:bbao
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

604 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