+ Reply to Thread
Results 1 to 13 of 13

How Can I Group The Items On The Pivot Table

  1. #1
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    Office 365
    Posts
    922

    How Can I Group The Items On The Pivot Table

    I want It looks like the one on the Sheet 4. My table is on the Sheet 1.

    How can I do it?
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How Can I Group The Items On The Pivot Table

    See attached.

    You needed to Group the PT Date by Month.

    Since the underlying data doesn't contain some months you won't see them shown in the PT. You would need to add dummy rows to the data
    Attached Files Attached Files
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    Office 365
    Posts
    922

    Re: How Can I Group The Items On The Pivot Table

    1) How can group PT Table by months?

    2) How can I add dummy rows to the data?

    3) Your table have missing monhts like April and September. When I check Show Items With No Data It shows unnecessary items like "<22/01/20"?
    How can I fix this?

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How Can I Group The Items On The Pivot Table

    1. The Workbook I sent is grouped by months. Put the Date field only in the Rows area of the PT Field List pane and put the Month field in the Values Section and set the 'Value' to COUNT

    2. Add a Date only in column A of the data and change the PT Source Data Range

    3. With the PT Menu (or right click in the Date field of the PT), choose GROUP and then pick Months.

  5. #5
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    Office 365
    Posts
    922

    Re: How Can I Group The Items On The Pivot Table

    1) Put The Date Date field into the Rows section. Put Months Into The Values Section. Set COUNT. But It doesn't look right.


    2) I inserted a new row on the Date column.


    3) I grouped the rows.


    What do I do wrong?
    Attached Files Attached Files

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How Can I Group The Items On The Pivot Table

    You added a whole row to the data. I said to just insert a date in column A

    The file you attached was linked to an external database so obviously I couldn't access that. The PT on the attached is linked to the data in this file.

    I also noticed a value called 'formula' in the PT. I've no idea what that was meant to ne so have removed it.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    Office 365
    Posts
    922

    Re: How Can I Group The Items On The Pivot Table

    I could do this mostly.

    1) Although I unchecked "blank" at Pivot Table They still appear both Pivot Table and Pivot Chart.
    Pivot Table (The Month April, W1, W2, W3, W4, blank)
    There is no week named blank. ?

    2)Pivot Chart X Axis looks like stuffed. Characters overlap each other. How can I spread them?
    Attached Files Attached Files

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How Can I Group The Items On The Pivot Table

    You have specified the whole of columns A:E as the PT Source data, hence you are getting blanks. Limit the source range to the table.

    Widen the chart, or change the font size of the horizontal axis or better still move the embedded chart to its own Chart Sheet
    Last edited by Richard Buttrey; 09-30-2020 at 06:10 PM.

  9. #9
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    Office 365
    Posts
    922

    Re: How Can I Group The Items On The Pivot Table

    Yes, I did that on purpose.

    I'll continuously enter new values on that table. And I want every new data will show up in the pivot chart.

    How can I do that?

  10. #10
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How Can I Group The Items On The Pivot Table

    If you continue to use A:E as the PT source then you will get blanks in the PT.

    Are you now saying you will be limiting the source range?

  11. #11
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    Office 365
    Posts
    922

    Re: How Can I Group The Items On The Pivot Table

    Then I assume there is not another way for this. I'll give up showing weeks in this case.

    I choose only the month May in Timeline. However now It show nothing on the pivot chart.
    Although May has 5 entries.

    ???
    Attached Files Attached Files

  12. #12
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How Can I Group The Items On The Pivot Table

    To which Pivot chart are you referring? The one on Sheet1 or Sheet4.

    The May Count is on the Sheet1 chart, the only reason you can't see the Total Series is because the chart is a line chart. Change it to a column chart.

  13. #13
    Forum Contributor
    Join Date
    01-16-2015
    Location
    Istanbul
    MS-Off Ver
    Office 365
    Posts
    922

    Re: How Can I Group The Items On The Pivot Table

    Thank you very much for the answers Richard Buttrey.

+ 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. Group 3500 items based on another table?
    By Smoka in forum Excel General
    Replies: 1
    Last Post: 08-13-2019, 09:47 AM
  2. Create a table/pivot table using different items items from a column
    By stevetton in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-28-2016, 10:30 PM
  3. Do not group similar items together - PIVOT TABLE
    By sourabhg98 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-10-2016, 10:55 AM
  4. Filter pivot items using pivot items from another table
    By DKolev in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2014, 08:49 PM
  5. Replies: 1
    Last Post: 09-03-2012, 10:03 PM
  6. group items in pivot table
    By minkus in forum Excel General
    Replies: 1
    Last Post: 09-30-2007, 08:35 PM
  7. Can't group pivot table items by month in Excel
    By scott_ensley in forum Excel General
    Replies: 1
    Last Post: 02-01-2005, 05:06 PM

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