Pivot Table Formatting

uscarioca

New Member
Joined
Jul 7, 2004
Messages
16
I am trying to format my row headings to left justified, however each time for instance that a click on the Column drop down and hit OK or Cancel my formatting my row headings change back to Center Justified? I have tried the Perserver Formatting under Table Options as wells as I have Enable the Section and then applied the formatting change, but it does not stay?? What am I missing here? thanks in advance!
Chris
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
It is a waste of time trying to format PTs.
My standard practice is to just use the PT as an interim step between raw data and my nicely formatted reports.

Transfer data by making an extra column to the left of the PT with =CONCATENATE() formula to make unique lookup values and use =VLOOKUP() in the report (hidden lookup values in column A).
 
Upvote 0
Brian...I appreciate your response however I am not sure if I get what you are saying. Yes I understand what the Concatenate and Vlookup functions are....
 
Upvote 0
Not sure if this will help but try it and see. What this will do is merge and center the row headers as you filter the fields.

For the worksheet that holds the pivot table, right click on that sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveSheet.PivotTables(1).MergeLabels = True
Application.EnableEvents = True
End Sub
 
Upvote 0
Concatenate and Vlookup
We need a way to use Vlookup from a formatted report to a pivot table which requires a unique lookup value. As they do not exist naturally we have to manufacture them in both sheets.

Example
We need to use 2 columns to the left of the PT. So let's say the PT leftmost column is C. Unfortunately it contains blanks. So column A needs a formula like (with first PT row 5) :-
=IF(C5="",A4,C5)
We can now make our unique lookup values in column B with formula :-
=CONCATENATE(A5,D5)

They can look like gobbledegook, but so what. They are nicely in place to be included with the PT range as a lookup table.

These keys can now be copied to, say, column A in another sheet, where they can be referred to by =VLOOKUP(). We now have freedom to format the report exactly as required without being constrained by the PT format.

Hope this helps.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top