Thread: How do I get Month Names to show up in my Pivot Table?

After a lot of tweaking I managed to get the Number of the Month to display- For about 40 minutes I hassled with this to stop it from displaying each individual day. Now that I got it by month it is by labeled according to the number month.. 1, 2, 3, etc. How can I get this thing to display the month?! January February, etc.. I'm new to Pivot and am still stumbling over how this works..

Thanks for all the help!

Suggestion...

Create a Named Range:

Name: _PTData
RefersTo: ='Data Entry!$A$1:INDEX('Data Entry'!$L:$L,MATCH(9.99999999999999E+307,'Data Entry'!$B:$B))

Alter your PT Source Range to be: =_PTData

On the PT itself, remove "Month" from Column Field and replace with "Date Opened", right click on any given date and select Group -> select both Year and Month, this grouping should result in your desired output (based on my interpretation)

However, it still doesn't appear to be carrying the conditional formatting down in the pivotable whenever I add new data.. Was that formula supposed to help that or was it just something to better than just manually selecting the range with the data in it?

Re: conditional formatting... things get a little trickier here given your PT's can move, expand/contract etc... (at least theoeretically), as to whether or not there's a watertight method for doing this I don't know, I'm no PT expert myself I'm afraid... the below approach is to use the VBA Pivot Table Update Event such that as the PT is refreshed the formatting is reapplied such that even rows within the DataBody Range are formatted, obviously if you have historic rows that no longer form part of the PT (ie has contracted) you may end up with legacy formatting issues (ie rows formatted that should no longer be formatted...)

Code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rngData As Range, lngRow As Long
If UCase(Target.Name) = "PIVOTTABLE1" Then
    Set rngData = Target.DataBodyRange
    rngData.Interior.ColorIndex = xlNone
    For lngRow = 1 To rngData.Rows.Count - 1 Step 1
        If lngRow Mod 2 = 0 Then rngData.Rows(lngRow).Interior.ColorIndex = 15
    Next lngRow
    Set rngData = Nothing
End If
End Sub

(the above is set to apply only to pivottable1 - remove this test if you want the same formatting applied to any/all pivot table that is updated on the sheet)

In XL2007 things become a lot easier as the PT formatting options are significantly enhanced ... there may be a way to do likewise in the earlier versions but in truth I don't know them off hand (the styles as I recall are quite limited) - I will look around to see if I can find some good links on the subject (most likely Contextures!).

For every letter in the Work Center there is a name associated. e.g. "K" would be subcontractors.. Can I incorporate the Names of what each letter is into the pivot table?

To keep things simple, based on your latest layout on sheet 'Data Entry' (which is a lot better incidentally!) I would advise you do something along the lines of the below:

1 - insert a new sheet - named say "LOOKUPS"
in Column A insert the various Work Centre Codes, in B the associated values (ie subcontractor)

2 - go back to 'Data Entry' sheet, add a further calculation column
M1: Work Center Desc.
M2 =IF($G2="","",VLOOKUP($G2,LOOKUPS!$A:$B,2,0))
copy down for all rows

3 - revise your RefersTo Range for Defined Name: _PTData such that reference to $L:$L becomes $M:$M

4 - go to your Pivot Table, Refresh - then right click and Select the Wizard...

click Layout:

a) drag the new Field (Work Center Desc.) and place below Work Center in the Row Field
b) double click on each of the 2 ROW Fields (Work Centre, Work Centre Desc.) and set SubTotal to None

then click OK and Finish

Originally Posted by mrgillus

...I am definitely not a fan of these pivot tables..

Shame, I personally believe Pivot Tables should be the very first thing that anyone who uses XL on an even a weekly basis should learn to use... they enable the user to generate high-end analysis very quickly without requiring any extensive knowledge of formulae etc... and generally speaking they are pretty efficient (ie much more so than replicating the same analysis using formulas) ... they also encourage people to store their data correctly... ie show the value to be gained by adopting good data storage practices.