none
dispaying data in a pivot table in rows horizontally

    Question

  • i have 2 columns A = places B= dates of visit. I wish to eliminate the repeated places and sort the dates hotizontally.

    I have been able to set up a pivot table where all the names are reduced to one but the date values are displayed in a column. I wish the dates to be displayed on the same row as the place ie london   25/1/2012    12/02/2012    18/2/2012

                                                                 paris      28/1/2012    15/02/2012     24/02/2012

    Where I am at the moment is   London   

                                                              25/01/2012

                                                              12/02/2012

                                                              18/02/2012

    Wednesday, March 28, 2012 5:31 PM

Answers

  • Hi Stever,

    Let’s change the format of your table.

    1. Add a column into the table. Named as “date_type”.
    2. In the “date_type” column, type in “date1”, “date2”,”date3”… for each place.

      3. Insert the Pivot Table. Set the “Field List” as the picture.

      4. For the “Values” part, left click the button “Sum of da…”, choose “Value Field Settings”. Choose the “Show Value As” tab, then choose “No Calculation”. Click the button “Number Format”, choose “Date”.

      5. Select the whole Pivot Table, right click and choose “PivotTable Options”, choose “Total&Filters”,uncheck the “Show grand totals for rows” and “Show grand totals for columns”.Click OK.


    Jaynet Zhang

    TechNet Community Support

    Friday, March 30, 2012 7:18 AM