+ Reply to Thread
Results 1 to 6 of 6

Pivot table display

  1. #1
    Registered User
    Join Date
    02-11-2013
    Location
    Bryan, TX
    MS-Off Ver
    Mac 2011
    Posts
    16

    Pivot table display

    I am having a whale of a time with my pivot table on the Summary sheet in the attached Transcript.xlsx file. The table should look like the Transcript 2.xlxs file. What I am after on that pivot table is that no matter how I sort or filter the information, Field, Course, and Description are on the same row. In other words, those three are a group. How do I accomplish this in a pivot table?

    Also, how do I lighten the shade of a fill color?

    TS
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Pivot table display

    If I understand correctly then choosing the Tabular Report Layout and not showing Subtotals should do what you want.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    02-11-2013
    Location
    Bryan, TX
    MS-Off Ver
    Mac 2011
    Posts
    16

    Re: Pivot table display

    Thanks, Jete! This looks much better and does what I want it to do.

    I now need two things:

    1) How do I take the "Points" calculation from the data table "Grades" and make it a calculated field in the PivotTable "Transcript"? I cannot get the formula to return anything but 0. Is there a cleaner way of writing a nested IF/OR Statement?

    2) Instead of displaying "Sum of Points," how do I get the PivotTable to display the actual value? I cannot drag "Points" into the Rows or Columns.

    3) I need to add a "GPA" calculation to the Subtotal and Grand Total rows in the PivotTable. The formula would be
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    How do I do this?

    Many thanks,
    Terrible Tim
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Pivot table display

    The reason that points was not adding to the row field is that the pivot table's selected table/range read: Grades[[Term]:[Grades]] that has now been changed to Grades[[Term]:[points]]
    The design of the pivot table has been changed to allow grand totals for columns.
    Sum of GPA has been added as a calculated field with the formula =points/hours
    Let us know if you have any questions.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-11-2013
    Location
    Bryan, TX
    MS-Off Ver
    Mac 2011
    Posts
    16

    Re: Pivot table display

    I'm sorry, but you are close, but not quite because I was not clear. I want to take the "Points" field out of the data table and make it a calculated field in the PivotTable. And, "GPA" should only be displayed in the Subtotals and Grand Totals rows. "Sum of GPA" should not be displayed at the end of every line. The value for "Points" goes at the end of every line.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Pivot table display

    I let this sit a few days to see if I or anyone else could come up with an idea.
    I don't believe that you can use a calculated field to get the points, and so I have left that in the data table.
    I also don't see a way to include GPA for only the subtotal and grand total lines in the pivot table, however they could be made to appear as if they are part of the pivot table.
    This requires two helper columns which are hidden by grouping in the attached copy of the file.
    The first helper column (I) yields the total hours using: =IF(A4="Grand Total",SUM(I$3:I3),IF(ISNUMBER(SEARCH("Total",A4)),SUM(F$4:F4)-SUM(I$3:I3),""))
    The second helper column (J) yields the total points using: =IF(A4="Grand Total",SUM(J$3:J3),IF(ISNUMBER(SEARCH("Total",A4)),SUM(H$4:H4)-SUM(J$3:J3),""))
    The GPA column is populated using: =IF(I4="","",ROUND(J4/I4,2))
    Let us know if you have any questions.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 05-16-2014, 07:34 PM
  2. Display SQL for Pivot Table
    By excelwhiz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-24-2012, 01:17 AM
  3. Excel 2008 : Pivot table display
    By boann in forum Excel General
    Replies: 1
    Last Post: 10-21-2011, 08:23 PM
  4. Pivot Table to roll up data and display in pivot chart
    By Dawson64 in forum Excel General
    Replies: 3
    Last Post: 09-26-2011, 05:06 PM
  5. Pivot table display
    By ybu1106 in forum Excel General
    Replies: 10
    Last Post: 06-21-2010, 10:42 AM
  6. Excel 2007 : Pivot Table Display
    By phillb in forum Excel General
    Replies: 9
    Last Post: 04-13-2010, 07:39 AM
  7. Display for Pivot table
    By maximpinto in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-20-2009, 05:41 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1