none
Excel Pivot Table - Format Numbers in Rows

    Question

  • How are numbers in rows of an Excel Pivot Table formatted?
     The table with the source data has a column of times. When this is added as a row in the Pivot Table, it displays as HH:MM:SS AM/PM. I want to use the custom format HH:MM AM/PM. It seems the Number format button in Field Settings is only available for Values.
    If I format the entire column on the pivot table sheet, the format reverts back whenever I click refresh.  I have "Preserve cell formatting on update" checked.

    Part two of the problem is on the pivot chart, column A is dates, column B is time. If I check "Display labels from the next field in the same column" in the Date field settings, it changes the times from column B into dates in the year 1900.

    It looks like this:
    Wednesday, December 07, 2011
      Sunday, January 08, 1900

    I want:
    Wednesday, December 07, 2011
      8:30 AM

    There must be a way to do this.
    Wednesday, November 09, 2011 3:12 PM

Answers

  • Excel 2010 PivotTable
    Use Outline or Tabular Form
    not Compact Form.
    http://c3017412.r12.cf0.rackcdn.com/11_10_11c.xlsx

    If you get *.zip, don't unzip, just rename *.xlsx

     

    Thursday, November 10, 2011 7:34 PM
  • After much head scratching - I found the very simple solution to this question. To format rows or columns in a PT, hover the mouse at the top of the column or beginning of the row until a black arrow appears, click to highlight the row/column and format as usual.

    For Display labels from next field in same column, uncheck this, follow above procedure, then recheck.


    Paula Scharf

    • Marked as answer by Dorothy4116 Saturday, May 19, 2012 11:11 AM
    Saturday, May 19, 2012 11:10 AM

All replies

  • Excel 2010 PivotTable
    Use Outline or Tabular Form
    not Compact Form.
    http://c3017412.r12.cf0.rackcdn.com/11_10_11c.xlsx

    If you get *.zip, don't unzip, just rename *.xlsx

     

    Thursday, November 10, 2011 7:34 PM
  • After much head scratching - I found the very simple solution to this question. To format rows or columns in a PT, hover the mouse at the top of the column or beginning of the row until a black arrow appears, click to highlight the row/column and format as usual.

    For Display labels from next field in same column, uncheck this, follow above procedure, then recheck.


    Paula Scharf

    • Marked as answer by Dorothy4116 Saturday, May 19, 2012 11:11 AM
    Saturday, May 19, 2012 11:10 AM
  • In pivot table options, make sure "Preserve cell formatting on update" at the bottom of the first tab is checked.  Make format changes to numbers.  Go back to pivot table options, uncheck "Preserve cell formatting on update" and then recheck it.  This should work.
    Tuesday, November 05, 2013 6:31 PM