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?

Did you also uncheck Auto formatting?

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

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.

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.

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

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.

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

Try formatting column C in the pivot table to horizontally centered 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.

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

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

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.

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

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.

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?

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

Can you see if the files behaves for you.

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...

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.

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.

Originally Posted by Andy Pope

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.