Closed Thread
Results 1 to 21 of 21

Pivot Table Column Alignment

  1. #1
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Pivot Table Column Alignment

    I feel stupid asking this, but for some reason I am having trouble keeping alignment of columns in a Pivot Table....

    I have a Column of text in a pivot table and I am just trying to center the darn thing... but no matter what I have tried, when I refresh the table it goes back to left-aligned....

    I have Preserve Formatting set on... in the Table Options.

    What am I overlooking?
    Last edited by NBVC; 04-14-2009 at 12:12 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    Did you also uncheck Auto formatting?
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    Yes Andy, it is unchecked, yet still it returns to left aligned.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    what exactly do you mean by refresh the table?

    Even with my suggestion removing the row/column field and then replacing it does destory the formatting.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    I am Right-clicking the table and choosing Refresh Data

    Should I be loosing my formatting doing that?

    In an adjacent field with a date... I am able to change the date format and that setting it does preserve upon refresh.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    I can not repo your problem.
    You doing this in 2003 or 2007?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    2003

    Can you tell me exactly what you would do to center a field of a Pivot table and keep it... maybe I am missing a step.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    Here is a small sample of what I have...

    Try formatting column C in the pivot table to horizontally centered alignment.
    Attached Files Attached Files

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    I just selected the part of the table I wanted to align and then used the standard alignment buttons on the formatting toolbar.

    If I then right-click and refresh data the formatting remains as was.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    Can you please give my attachment above a look...

    That is what I was doing.... but no cigar... I also tried through Format|Cells...

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    I missed your attachment doing the previous post.

    I can now see what you mean. Strangley for me C9 and C12 remain centered the others become left aligned.

    Let me play and see if I can find a reason.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    For me not even C9 and C12 remained aligned... they all moved back to left......

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    I don't understand why but here is what I discovered.

    Format the column once the table is build and some the the rows revert back to non centered alignment when the table is refreshed.

    If you remove all the inner fields up to New Order. Now format the field to be centered. Add back the fields
    "Promised Date", "Days To Comp.", "Days Over", "Curr"

    and now the formatting is retained during a refresh.

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    For me they went back to left aligned....

    However, I did discover another "trick"...

    I went to PivotTable|Select|Entire Table and then again Pivot|Table|Select|Label and centered all labels...

    When I refreshed they remained Centered... but it only then allowed be to left-justify the first column (and keep it) the other columns wouldn't allow me to go back to left-justified....

    ..indeed odd... same thing happens in 2007... is something wrong with the data itself? I can't see why?

  15. #15
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    I rebuilt part of the table and the field is maintaining alignment.

    Can you see if the files behaves for you.
    Attached Files Attached Files

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    Thanks Andy, everything remains where it should be when I refresh... however if I change anything it goes back to the original setting... there should be a way to format the cells after you create the table and not only as you are building it... shouldn't there be? There's got to be something I/we are missing specific to my table, since you are able to do it on a table you create on your own from scratch, right?

    Now I have to rebuild my original table as you did and see if it will work... but when I rebuilt the smaller one as per your instructions before, it did not work for me...

  17. #17
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Pivot Table Column Alignment

    Final observation before I go home

    If you select the range C5:C16 and center some of the cells revert to left align on refresh.

    But if you move your mouse to the left of cell C9 you should see the cursor change to a black right arrow. If you click you will see all rows with a Y in will be selected. Now if you center column C is centered and the alignment remains after a refresh.

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    Andy,

    I also found this in Microsoft support website

    http://support.microsoft.com/default...;en-us;Q215904

    I know I played around with moving/adding/deleting fields and I also "hid" subtotals, etc...

    ...so maybe that is why your rebuild works and mine does not...

    I guess I will have to rebuild the whole sheet from scratch.. as I really don't like their workaround using a macro.

  19. #19
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Pivot Table Column Alignment

    Quote Originally Posted by Andy Pope View Post
    Final observation before I go home

    If you select the range C5:C16 and center some of the cells revert to left align on refresh.

    But if you move your mouse to the left of cell C9 you should see the cursor change to a black right arrow. If you click you will see all rows with a Y in will be selected. Now if you center column C is centered and the alignment remains after a refresh.
    Excellent Andy,

    That did the trick... I added more Y's in the main database and refreshed the table and the "new" Y's remained centred too!

    So that black right arrow seems to select all "like" items in the field to the right... so it is coincidence that I only have Y's... If I do the same in other fields, it only selects the cell that have the same values, so I would have a harder time...I guess... so not the ultimate solution, but in my case I only needed that one column centered (I think)... we'll see what the requester here says.

    Anyways, Great! Thanks much for the help, Andy!

    I am glad it wasn't such a stupid question after all.

  20. #20
    Registered User
    Join Date
    05-01-2013
    Location
    Telford, shropshire
    MS-Off Ver
    Excel 2010
    Posts
    1

    Smile Re: Pivot Table Column Alignment

    Hi All

    I have been having this very same issue and today discovered that the problem is solved by making sure you have the "Merge and centre cells with labels" unchecked. This is found in Pivot Table Options under Layout & Format.

    Hope this helps.

  21. #21
    Registered User
    Join Date
    09-09-2013
    Location
    london
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Pivot Table Column Alignment

    1. select the cell you want to align

    2. Analyze

    3. Select

    4. Enable selection

    5. Align to centre

    and that's it

Closed Thread

Thread Information

Users Browsing this Thread

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

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