Pivot table with text orientation at 90degrees

Topic

New Reply

I have a pivot table which has course names across the top and names down the size with date of course completeion as the data. All works fine with that.

The course names were long, so I formatted the row that contains them to have text orientation at 90degrees and wrap text turned on. This allowed me to produce a smaller, narrow column. Again this works fine.

Problem comes when I add courses to the Access database which supplies the data to the pivot table. The cell which the new course name goes into changes to standard text – wide column, and horizontal.

I tried changing the ‘preserve formatting’ in the pivot table options, but that had no effect.

I tried pasting the data into the spreadsheet and linking directly to it rather than linking to it via an Access Query and had the same problem.

Any thoughts?

John

Reply | Quote

Replies

Any thoughts?

John

The problem is not with the Pivot Table. It is with the formatting you selected in Excel.
If each time you receive new data you generate a new Pivot Table and you select the table to either go to a new area of the existing worksheet or to go to a new worksheet you will lose your formatting.

One possible solution is to use the same Pivot Table and change the PivotTable Data Range to be large enough to accommodate the current data as well as future data.

Then when new data arrives copy it to the Pivot Table Data Range.
Go to the existing Pivot Table and hit Refresh. The Table should update and reflect your new data.

If you desire run a new Pivot Table each time try the below Macro
Run it after the Pivot Table is Created.

The below Macro assumes the row to be formatted is Row 4 of the Active Worksheet.
You can modify the below to adjust to your facts.

Sub MyCols()
‘
‘ Macro will Format Cols Headings for Pivot Table
ActiveSheet.Select
Rows(“4:4”).Select
With Selection
.WrapText = True
.Orientation = 90
End With
End Sub

Regards,

Tom Duthie

Reply | Quote

If you want the table to be formatted automatically when it’s updated, you can create code similar to that posted by Tom Duthie in the Worksheet_PivotTableUpdate event in the worksheet module.
See [post=”737026″]Post 737026[/post] for an example of such code (with different formatting!)

Reply | Quote