PivotTable Formatting

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
291
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Background:
I have an issue with formatting on a PivotTable.
The row labels are long so the text orientation is set at 60 degrees.
Preserve formatting is selected in options.
The spreadsheet is a template which is used to produce data for a number of processes.
The issue:
The behaviour of the above formatting in the template is irregular when used repeatedly.
i.e. sometimes only the first two columns will have the formatting applied, sometimes more.
When I update the template with new information and Refresh I get a message that the PivotTable already contains information even though the PivotTable itself is blank. I saved the template without the source data in an attempt to start with a clean PivotTable.
But whatever I have tried so far the formatting is still not applied to all columns after a refresh.

Any help would be appreciated.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Col Delane

Active Member
Joined
Jan 14, 2014
Messages
303
Formats:
When formatting the pivot table are you selecting the cells in the pivot table (like selecting any other range in a spreadsheet) and applying formatting, or selecting the pivot table "field". The former will be erased when the PT is refreshed as it is not part of the PT, whereas the latter will be retained.

Watch this video (from time point 8.30) from ExcelTV with Excel MVP Ken Puls demonstrating formatting a PT:
https://excel.tv/members/building-bi-with-pivot-tables-free-video-series9384748793792348798379832/

Noise:
I'm not sure about the "PivotTable already contains information even though the PivotTable itself is blank" issue, but the following may help.

PTs are a special kind of animal (a powerful black box that most of us know how to use but don't really know what's going on in the box! :eek:)
When the source data changes (subject matter rather than just more of the same), the old values can sometimes be "remembered" by the PT, resulting in lots of "orphans"
Check out this video tutorial from Debra Dalgleesh's excellent Contextures website (which has loads of good stuff about PTs :biggrin:)
https://www.contextures.com/xlPivot04.html
 

sparky2205

Active Member
Joined
Feb 6, 2013
Messages
291
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Col,
thanks for your help and the links.
It was what you suggested.
The cells in the pivot table had the formatting applied rather than the relevant pivot table field.
So it's working now.

Cheers.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,312
Members
409,862
Latest member
lbisacca
Top