+ Reply to Thread
Results 1 to 16 of 16

Pivot Table Group Help

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Pivot Table Group Help

    Hello,

    I'm writing in order to have some suggestions about the Pivot Table linked in the picture.
    I'd like to:
    • make the "Data" (means Date) values displayed as a simple string and not a collapsible group (a kind of simple VLOOKUP).
    • display Subtotals only for the group named "Categoria" avoiding that the subtotal is created also for Date rows.

    Thanks in advance for you help
    Regards
    Attached Images Attached Images

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Group Help

    try:
    - Design - Layout - show in Tabular Form
    - Analyze - Show - Buttons - untick +/-

    (next time attach excel file not a picture)

    If that takes care of your original question, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you) then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED. Thanks.
    Last edited by sandy666; 08-24-2017 at 03:55 AM.

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Pivot Table Group Help

    Thanks, but:
    - the best design for my need is the Outline form, in order to have a "simple table" shown.
    - I already tried the buttons but they actually display/hide the simple ribbon. The "Data" field is still a group that can be open or collapsed.

    I need the "Data" field to be a static column and not a Group. Imagine, for example, to add a vlookup when the Pivot table is already inserted. It has to appear like the "Nome Breve Attivitą" column. (Maybe the only way is a concatenation between Data and Nome Breve Attivitą in order to have a single level and not multiple sub-levels).
    This would allow me to have a Pivot table that is collapsible only for "Categoria" and have subtotals only for Categoria.

    Thanks again

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Group Help

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Pivot Table Group Help

    Hello,
    please find file in attachment.
    Thanks again
    Attached Files Attached Files

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Group Help

    your source dates has mixed format
    you need to decide: all dates are as excel dates or all dates are as text

    is that what you want (on "to be" tab)?

    If that takes care of your original question, to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    Thank you.
    Last edited by sandy666; 08-24-2017 at 10:56 AM.

  7. #7
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Pivot Table Group Help

    Thanks for your help.
    I fixed the dates format (there is a part of the vb code fort this - I just handled it by mistake making some attempts).
    Dealing with subtotals, it is a really good result, I already implemented it in the original file.
    The only inconvenient is that "Data" values are still groups (no ribbon is fine but it can be collapsed) and it is still bold. Is there a chance to make this field appear and be "static" like "Nome Breve Attivitą"?

  8. #8
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Group Help

    I don't think so. You can move "Data" below "Nome Breve Attivitą" but in this case Pluto & Pippo will be bolded. Something for something

    If that takes care of your original question, to say Thanks, please click on Add Reputtion (bottom left corner of the post of the person(s) who helped you)
    then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.
    Thank you.

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Pivot Table Group Help

    If you want "Data" without bold, you can add (helper) column in source with values 1,1,1,1....
    and in PT move it before "Data" but...
    But 1 will be bolded and you can collapse/expand just this column
    Last edited by sandy666; 08-24-2017 at 12:42 PM. Reason: file updated

  10. #10
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Pivot Table Group Help

    or like in this version "no see" name 'helper' and values: 1

  11. #11
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Pivot Table Group Help

    Actually the last one is exactly the same "solution" (or workaround) that came to my mind!
    PS: just a last question, how do you manage to set PT data origin from Query - Table1 and choose only some fieds to be "displayed"?
    Immagine_1.PNG
    Immagine_2.PNG
    Immagine_3.PNG (my file)
    Something like this? https://www.myexcelonline.com/blog/l...s-power-pivot/
    This is just for my curiosity, in this case I need the data origin to be variable (I used VB code for this).

    Thanks

  12. #12
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Group Help

    sorry but Italian is not my perfect language

    what about fields in PT?

    Use PQ, then right click on unnecessary column...

    removecolumnfromquerytable.jpg

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by sandy666; 08-25-2017 at 03:42 AM.

  13. #13
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Pivot Table Group Help

    Sorry for Italian...
    I see that you set the data in tab "Totale_Transazioni" as a "proper" table but I honestly don't know how you managed to create a query and linking it to the Pivot Table (Connection Name: (Query - Table1) in my Image_1 which refers to Change data source in PT options).
    Let's say I've never used queries in Excel..

  14. #14
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Group Help

    download and watch from link: PQPT (this is example only)
    btw. it will be accessible for few hours only
    to be honest PQ is not required to do what you want

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Last edited by sandy666; 08-25-2017 at 05:27 AM.

  15. #15
    Registered User
    Join Date
    06-19-2013
    Location
    Sheffield
    MS-Off Ver
    Excel 2013
    Posts
    86

    Re: Pivot Table Group Help

    Many Thanks

  16. #16
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Group Help

    You are welcome

+ 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. [SOLVED] Need to group 1 pivot table among 30
    By FernTurpin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-02-2015, 11:37 AM
  2. [SOLVED] Need to group 1 pivot table among 30
    By FernTurpin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-02-2015, 11:36 AM
  3. [SOLVED] Need to group 1 pivot table among 30
    By FernTurpin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-02-2015, 11:36 AM
  4. [SOLVED] Need to group 1 pivot table among 30
    By FernTurpin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-02-2015, 11:36 AM
  5. Replies: 5
    Last Post: 12-18-2012, 11:37 AM
  6. Cannot Group in Pivot Table
    By shosan in forum Excel General
    Replies: 2
    Last Post: 08-24-2011, 05:06 PM
  7. Pivot Table, can't group
    By Pascale in forum Excel General
    Replies: 2
    Last Post: 08-09-2005, 09:07 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