Formatting Pivot Tables
A few times in a man’s life, technology meets genius to create something truly remarkable. This is one of those times.
Applying a style to a cell with the keyboard is a pain. As I’ve mentioned in a previous post, I created a macro and assigned it the hotkey Ctrl+M.
Sub MakeComma()
If TypeName(Selection) = "Range" Then
Selection.Style = "Comma"
End If
End Sub
If TypeName(Selection) = "Range" Then
Selection.Style = "Comma"
End If
End Sub
Simple but effective. I love this macro. Another bane of my existence is formatting pivot fields in a pivot table. Right click, Field Settings, Number, etc., etc. So without further ado, I present to you the greatest macro ever written.
Sub MakeComma()
Dim pf As PivotField
If TypeName(Selection) = "Range" Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0
If pf Is Nothing Then
Selection.Style = "Comma"
Else
pf.NumberFormat = "#,##0.00"
End If
End If
End Sub
Dim pf As PivotField
If TypeName(Selection) = "Range" Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0
If pf Is Nothing Then
Selection.Style = "Comma"
Else
pf.NumberFormat = "#,##0.00"
End If
End If
End Sub
You know you’ve written something magical when you giggle like a school girl every time you run it. You’re welcome.
You are a god walking among men. I bow before your awesome excel knowledge. How do I assign this to a macro key so that is always available?
Are we welcome for the macro or the magic??
Great! Now that this problem has been solved, fill in the blanks below:
Sub SolveWorldHunger()
‘blank
End Sub
Sub FixEarthClimate()
‘blank
End Sub
Sub PlugOilLeak()
‘blank
End Sub
There’s probably a VBA library for these kinds of things… good luck!
I modified the first one to my favorite format: “Comma [0]” because I see a lot of people enter numbers like 12345 and 3423456, which are hard to gauge the magnitude of without straining my eyes.
I’ll have to try the second one out because it too is the “bane of my existence” when formatting PivotTables. Thanks for the code. I’m sure I’ll be laughing here pretty soon, ’cause I don’t giggle.
You have touched on a subject that has haunted me since I first began using Pivot Tables (PT).
Why don’t fields in a PT default to the format of their respective data in the source table?
If my source data is on a Excel sheet, it is very easy for anyone to manually apply formatting (currency, date, etc.). Even a relatively new user can create a macro in seconds to apply formatting using the macro recorder. None of those options are good enough. When you create a PT based on the formatted data, all the numbers are formatted as general. You have to format each field individually, which takes multiple clicks. This really stinks !!!!!!
Until someone at Microsoft wakes up to this issue and does something about it, at least I know have this macro in my box of tools.
Thank you very much,
GL
Hi Dick
That’s excellent
Like Gregory, I also like the comma separator for zero decimal numbers, so to make it variable, I modified your code as follows
Sub MakeComma()
Dim pf As PivotField, dec As Integer
dec = InputBox(”Number of decimals”)
If TypeName(Selection) = “Range” Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0
If pf Is Nothing Then
Selection.Style = “Comma” & ” [" & dec & "]”
Else
pf.NumberFormat = “#,##”
If I Then
pf.NumberFormat = “#,##” & “.” & Left(”0000″, dec)
End If
End If
End If
End Sub
I can’t help thinking ctrl shift+1…
Dick, beyond what you’ve done, I also invariably rename the field because Excel put the supremely annoying “SumOf” in front of the fields, so using your post as inspiration, I wrote this today:
Dim szPivotTableName As String
Dim szPivotFieldName As String
Dim szPivotFieldNewName As String
szPivotFieldNewName = InputBox("Enter the new name for the field", "Pivot Field Rename")
szPivotTableName = Selection.PivotTable.Name
szPivotFieldName = Selection.PivotField.Name
With ActiveSheet.PivotTables(szPivotTableName).PivotFields(szPivotFieldName)
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
.Caption = szPivotFieldNewName
End With
End Sub
I need to spend a few minutes tweaking the code so I can choose between my ,0 format and a percentage format with 1 decimal place as those are the two most common formats I use. I also need some error checking logic. Right now, it is best if the cursor is on the field name of field you want to modify.
If you don’t want to use a shortcut you can always add the macro to the Pivot Table Context Menu (Right Click Menu).
I created an AddIn and included this in the AutoExec code.
With Application.CommandBars("PivotTable Context Menu").Controls
With .Add
.Caption = "Format: Currency"
.OnAction = ThisWorkbook.Name & "!PivotCurrency"
.BeginGroup = True
End With
End With
This allows to select one cell in a pivot table, right click and select the Pivot Curency and it will format the entire column. The error handling is a joke but it works for my purposes.
On Error Resume Next
With Selection
With ActiveSheet.PivotTables(.PivotTable.Name).PivotFields(.PivotItem.Name)
.NumberFormat = "#,##0.0_);(#,##0.0)"
End With
End With
End Sub
Dude! this has gone straight to my custom toolbar where it will occupy a place of prominence
I think you are testing the wrong variable Roger, and there is a superfluous concatenation.
I think it should be
If CBool(dec) Then
pf.NumberFormat = “#,##0.” & Left(”0000″, dec)
End If
Once again, Dick has changed my life!
Great code!
Thanks Dick, I love your magic, I’ve been using my hotkey many times already this week
99 out of a 100 times I need to set my pivot field to the summary type and the same time, so I’ve made one further modification to set the function type. By setting the Function type first this will already trigger a default name starting with CountOf.. to be updated to SumOf.. before it is supplied as default value in the InputBox asking for a possible name change.
Dim pf As PivotField
If TypeName(Selection) = "Range" Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo 0
If pf Is Nothing Then
Selection.NumberFormat = "#,##0_);[Red](#,##0)"
Else
pf.Function = xlSum
pf.NumberFormat = "#,##0_);[Red](#,##0)"
pf.Name = InputBox("Enter the new name for the field " & Chr(13) & "(Please note the field summary type is now SUM!)", "Pivot Field Rename", pf.Name)
End If
End If
End Sub
This is going to save so much time! Thank you!
Pivot tables are a great tool, however, one of my greatest frustrations is seeing them get discarded due to integrity issues. I can appreciate the formatting tools offered but was wondering if anyone else is having problems with their pivot tables being trashed by Excel.
I ended up writing a macro to re-build the pivot table on the fly but would like to think there is a more elegant solution.
I’ve gone through great lengths making sure my service packs are up to date and more … with no success. My spreadsheet is password protected and I can only think that this may be the root of the problem. Any advice would be appreciated.
By the way, thanks for the great code snippets. They work wonders.
[...] few weeks ago Dick Kusleika posted a small but brilliant piece of code that auto formats the active pivot data field to a number format without having to muddle through [...]