+ Reply to Thread
Results 1 to 15 of 15

undesirable change of cells color when expanding/collapsing pivot table

  1. #1
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    undesirable change of cells color when expanding/collapsing pivot table

    good afternoon, everyone!

    I have been trying to solve a slight problem that has been bothering me for a while.

    The problem is as follows: I usually use the white color for all cells on my worksheets (adds a bit of professional look) but when it comes to placing pivot tables on a worksheet I want the pivot table to fit the entire worksheet by being with the same stylistic look (I want all of the cells to be in white color). It looks okay if I preliminary paint all the cells into white and then start to add rows/columns. But the problem starts when I begin to collapse rows or columns. The area by which the pivot table was decreased gets regular formatting and the cell borders appear but I don't want that. So the entire worksheet is white with no borders but the area of the row/column that was just collapsed has a different formatting. I already tried conditional formatting and have set rules that stipulate painting all the cells that are (and are not) empty in white but anyway the problems remains.

    Anyone knows what should be done to solve this?

    PS I also tried formatting cells in pivot table options but the same result

    many thanks in advance

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: undesirable change of cells color when expanding/collapsing pivot table

    Hi vansamsonov,

    Can you upload a sample workbook - may be 2 sheets one is the problem sheet and another one as your desired output sheet ? thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: undesirable change of cells color when expanding/collapsing pivot table

    hello, dilipandey!

    I attached the xls file - the first sheet contains a pivot table formatted as I want it to be

    the second sheet had 3 value columns - and I removed one from the right-hand side in order to show what happens with the column it was located in. If you remove one value column more - you will see the same thing - that column will have the cell borders despite the fact that I formatted the entire sheet with white color (no borders)

  4. #4
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: undesirable change of cells color when expanding/collapsing pivot table

    nope, it did not upload

    for some reason I can't attach the file

    is there any way I could email it to you??

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: undesirable change of cells color when expanding/collapsing pivot table

    nope, it did not upload

    for some reason I can't attach the file
    How are you attaching ?
    If there is restrictions with xls or xlsx then you can try attaching images showing before / after effects. Thanks.


    Regards,
    DILIPandey
    <click on below * if this helps>

  6. #6
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: undesirable change of cells color when expanding/collapsing pivot table

    finally managed to upload, pls see attached!
    Attached Files Attached Files

  7. #7
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: undesirable change of cells color when expanding/collapsing pivot table

    okay.. under ribbon option "view" uncheck the "gridlines" option.



    Regards,
    DILIPandey
    <click on below * if this helps>

  8. #8
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: undesirable change of cells color when expanding/collapsing pivot table

    okay great! thanks a lot!!

    but is it the only way?? i mean , lets say you need only the pivot table area not to have those gridlines what would you do, then??

  9. #9
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: undesirable change of cells color when expanding/collapsing pivot table

    You can have conditional formatting which can be applied using vba (macro) triggered via change events.
    but that may be a lengthy (difficult) approach



    Regards,
    DILIPandey
    <click on below * if this helps>

  10. #10
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: undesirable change of cells color when expanding/collapsing pivot table

    thanks a lot, man !

  11. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: undesirable change of cells color when expanding/collapsing pivot table

    You are welcome ivansamsonov

    Please mark this thread as [SOLVED].. thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>

  12. #12
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: undesirable change of cells color when expanding/collapsing pivot table

    excuse me, how do I do that ??

  13. #13
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: undesirable change of cells color when expanding/collapsing pivot table

    I checked forum rules and found below :-
    9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.

    Regards,
    DILIPandey
    <click on below * if this helps>

  14. #14
    Registered User
    Join Date
    03-27-2013
    Location
    Kiev, Ukraine
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: undesirable change of cells color when expanding/collapsing pivot table

    done, thanks again !

  15. #15
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: undesirable change of cells color when expanding/collapsing pivot table



    cheers


    Regards,
    DILIPandey
    <click on below * if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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