+ Reply to Thread
Results 1 to 5 of 5

Pivot table display

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

    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 2010/2019
    Posts
    7,564

    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
    14

    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 2010/2019
    Posts
    7,564

    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
    14

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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