Auto Format PivotTables to Match Source Data

So summer is for practically over and I’m back to blogging topics that can actually help people. For my first post back, I’ll come out big with one of the best pieces of code I’ve ever written.

 

A 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 the formatting dialog boxes. I loved it and used it for a while. Then I thought:

 

 

That is to say, I want my pivot table to automatically recognize the number formatting in the source and apply it without my help. So I got to work and created what you’re about to see here. You had better put on your sunglasses because the awesomeness is blinding.

 

Start with a raw pivot table similar to the one shown here. Notice how Excel slaps the General format on numbers. Also note the always annoying “Sum of” prefix on every value field.

 

 

Now just place your cursor inside the pivot table and run this code:

  1. Sub AdoptSourceFormatting()
  2. 'Mike Alexander
  3. 'www.datapigtechnologies'
  4. 'Be sure you start with your cursor inside a pivot table.
  5.  
  6. Dim oPivotTable As PivotTable
  7. Dim oPivotFields As PivotField
  8. Dim oSourceRange As Range
  9. Dim strLabel As String
  10. Dim strFormat As String
  11. Dim i As Integer
  12. Dim bSourceIsTable As Boolean
  13.  
  14. On Error GoTo MyErr
  15. 'Identify PivotTable and capture source Range
  16. Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
  17.  
  18. If oPivotTable.PivotCache.SourceType <> xlDatabase Then
  19. MsgBox "Sorry.  This procedure will not work on pivot tables based on external sources, multiple consoldiated ranges, other pivots, or Scenarios."
  20. Exit Sub
  21. End If
  22.  
  23. Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))
  24.  
  25. On Error Resume Next
  26. bSourceIsTable = (oSourceRange.ListObject.Name <> "")
  27. On Error GoTo 0
  28. If bSourceIsTable Then
  29. Set oSourceRange = oSourceRange.Offset(-1).Resize(oSourceRange.Columns.Count + 1)
  30. End If
  31.  
  32. 'Refresh PivotTable to synch with latest data
  33. oPivotTable.PivotCache.Refresh
  34.  
  35. 'Start looping through the columns in source range
  36. For i = 1 To oSourceRange.Columns.Count
  37.  
  38. 'Trap the column name and number format for first row of the column
  39. strLabel = oSourceRange.Cells(1, i).Value
  40. strFormat = oSourceRange.Cells(2, i).NumberFormat
  41.  
  42. 'Now loop through the fields PivotTable data area
  43. For Each oPivotFields In oPivotTable.DataFields
  44.  
  45. 'Check for match on SourceName then appply number format if there is a match
  46. If oPivotFields.SourceName = strLabel Then
  47. oPivotFields.NumberFormat = strFormat
  48.  
  49. 'Bonus: Change the name of field to Source Column Name
  50. oPivotFields.Caption = strLabel & " "
  51. End If
  52.  
  53. Next oPivotFields
  54. Next i
  55.  
  56. Exit Sub
  57.  
  58. 'Error stuff
  59. MyErr:
  60. If Err.Number = 1004 Then
  61. MsgBox "You must place your cursor inside of a pivot table."
  62. Else
  63. MsgBox Err.Number & vbCrLf & Err.Description
  64. End If
  65.  
  66. End Sub

 

In seconds, your pivot table will be reformatted to match the number formatting and labeling found in the source data.

 

 

The code is commented relatively well so I won’t bother explaining every detail. But here’s the gist.

The code simply loops through each column in the data source, capturing the header name and the number format of the first value under each column. Once it has that information, it determines if the associated PivotField is in the Values Area of the PivotTable. If it is, the number format and label is applied to that PivotField.

 

I’ll be expecting a call from the Nobel institute later this week.

42 thoughts on “Auto Format PivotTables to Match Source Data

  1. Khushnood Viccaji

    Hi Mike,
    This is really useful stuff !

    A couple of questions:

    1. Why is the code put into a “Function” ?

    2. If the pivot table is not refreshed, an error occurs, and the error message is misleading.
    Is there a way to check whether the pivot table is refreshed or not ?
    And if not, then do that and go further ?

    Khushnood Viccaji
    Mumbai, India

  2. Khushnood Viccaji

    Also, I prefer the ‘classic’ pivot table layout, along with a few other default settings.
    I have created a .bas module to setup a pivot table with the preferred default settings, based on a pre-defined data range name (which needs to be done first, manually).
    Whenever I need to create a new pivot table, I import the .bas file and run it. Your code from this post will be a very useful extension to that code !

    I can share that module in case anyone’s interested.

    Khushnood

  3. datapig Post author

    Khushnood:

    “Why is the code put into a Function?”

    No reason really. I went ahead and changed the code so it can be used directly in the Macros window.

    “If the pivot table is not refreshed, an error occurs…”

    I’m not sure why this would happen. But just to be safe, I added a quick line that refreshes the pivot table before starting.

  4. Oakhome

    Cool! Shades are on

    now if only someone could tell MS to stop defaulting value fields in pivot tables to “count of”, as opposed to ‘sum of’ ..

  5. moth25

    or even better, tell MS to make the default value field a user definable option…I tend to use ‘average of’ in most of my pivot tables.

  6. Khushnood Viccaji

    Hi Mike,

    “I’m not sure why this would happen.”
    This is because in the pivot table’s options, I had un-checked the option : ‘Refresh data when opening the file’.
    So when your code was run, it was trying to work with an ‘un-refreshed’ pivot table, and throwing an error.

    “But just to be safe, I added a quick line…”
    I did the same at my end 🙂

    Khushnood

  7. Sergio Juan

    Hi Mike.
    I´m Sergio From Spain.

    I need some help from you.
    I´m trying to use your code, but a continiusly have a error, The macro says me that I´m not in pivot table, when I actually I am, I review your code and I have a error on the folowing line:
    Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))

    I used a trik , I create a dinamic range o my sheet to refer the pivot table source then the code works. But it´s not a solution, because I wan to add you code to my personal.xls and use it permanently.

    The result of: Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1)) is always Nothing in the vb local window-

    Sorry, about my english level.

  8. Gerald Strever

    Hi Khushnood
    I would very much appreciate you sharing the .bas module that you referred to 10.49 today.
    Thanks
    Gerald Strever

  9. chrisham

    Khushnood, appreciate if you could post the bas module you referred to earlier. That would be a great tool to have!

  10. Khushnood Viccaji

    Here goes :

    1. Sub CreateDefaultPivotTable()
    2. '
    3. ' Macro to quickly create a PivotTable on a new sheet,
    4. ' based on the temp range name,
    5. ' and customise it in classic PivotTable Layout
    6. '
    7. ' remember to create the Range Name 'TEMPDATA' in the source data table
    8. ' *before* running this macro
    9.  
    10.     ActiveWorkbook.PivotCaches.Create _
    11.     (SourceType:=xlDatabase, SourceData:="TEMPDATA", _
    12.     Version:=xlPivotTableVersion12).CreatePivotTable _
    13.     TableDestination:="", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
    14.     ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    15.     ActiveSheet.Cells(3, 1).Select
    16.  
    17. ' change various pivot table options
    18.    With ActiveSheet.PivotTables("PivotTable1")
    19.     .PageFieldWrapCount = 1 ' display one page field per column
    20.    .HasAutoFormat = False ' disable column autofit on refresh
    21.    .DisplayNullString = True ' display '-' in blank cells
    22.    .InGridDropZones = True ' x
    23.    .AllowMultipleFilters = True ' allow multiple filters per field
    24.    .RowAxisLayout xlTabularRow ' classic pivot table layout
    25.    .PrintTitles = True ' print titles across pages
    26.    .SaveData = False ' do not save source data with the file
    27.    End With
    28.  
    29.     ActiveWindow.DisplayGridlines = False
    30.  
    31. ' run Mike's sub to pickup source cells' formatting in pivot table fields
    32.    Call AdoptSourceFormatting
    33. End Sub
  11. Oakhome

    @ Jeff:

    wonderful – thank you for sharing.. now to not think of all those times I’ve gone through the baluba of doing this the hard way, and rather enjoy the magic

  12. Khushnood Viccaji

    Also one other thing :
    The variable ‘ i ‘ is shown as not defined when you try to run the code (if you have Option Explicit specified).

    You can add : “Dim i as Integer” in the code.

    This should take care of things until Microsoft manages to cross the 32767 columns barrier in a spreadsheet ;-p
    In any case, a table with fields/columns more than a fraction of this number would be better off in a Database application, right ?!

    Khushnood

  13. datapig Post author

    Sergio: Strange error you’re getting. Could it be that yoiu are using an external data source for your pivot table? What do you see when you run this test:

    Sub SourceTest()
    Dim oPivotTable As PivotTable
    Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
    MsgBox oPivotTable.SourceData
    End Sub

  14. Sergio Juan

    Hey Data¡¡ Thanks for answering so fast….but. I still have the same problem.. I used your the SourceTest code you have passed me, and the result its a msg with range of the data source of the pivot table ( that it is in the same workbook), the problem I think it´s not in that part of the code but the folowing line it´s where I got the error.

    Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))

    Here it´s where the code crash. It gives me an error, and then go to Myerr (logically). If I use de F8 to run the code step by step, it´s in this line where the code jumps to Myerr , it sets oSourceRange =”Nothing”. It´s very strange, because if I get out this part of code and I put directly the range of the SourceTable of the pivot table, it´s runs.

    Could I send you a e-mail with my excel??

  15. General Ledger

    I have been complaining for years about PivotTables not automatically using the source formatting. Once again, the solution comes not from Microsoft.

    Bill Gates, while giving half his fortune away, should cough up a few thousand bucks for you.

  16. Frank

    Hi Mike,
    I am Frank from Germany having exactly the same problem as Sergio

    “The macro says me that I´m not in pivot table, when I actually I am, I review your code and I have a error on the folowing line:
    Set oSourceRange = Range(Application.ConvertFormula(oPivotTable.SourceData, xlR1C1, xlA1))”

    However, if I use a name for the SourceData, it works fine. Obviously, the conversion of the SourceData is problematic!? There seems to be no conversion to xlA1 in Excel 2010!?

  17. Rick

    Datapig,

    I am surprised I didn’t see your name on the Nobel last year for this bit of magical code!! It has helped me tremendously.

    I am wondering though if you could help me figure out how to enhance it a bit to also bring over the font colors of the source data values?

  18. Jeff Weir

    On my 2010 system, this code fails if the data source for the pivot is an Excel table (i.e. ListObject) because the oPivotTable.SourceData address returns the table body range (e.g. Table4) and not the entire table including headers (i.e. Table4[#All])

    But you can always add a check for this right after the Set oSourceRange line , like so:

    On Error Resume Next
    bSourceIsTable = (oSourceRange.ListObject.Name "")
    On Error GoTo 0
    If bSourceIsTable Then Set oSourceRange = oSourceRange.Offset(-1).Resize(oSourceRange.Columns.Count + 1)

    Only tested this in Excel 2010.

  19. Raghav

    Hi … Just like one can copy formatting , would be possible to copy cell color or row color in the pivot table.

    Regards

  20. Yeshica

    Hi Mike,

    I’ve just recently stumbled upon this page and it’s been saving me, literally. I just have one problem with Excel that you might be able to help me with.

    So I’ve used your code to apply a custom ‘weight’ format to my table. And some of the data has the same value as the ones below. And the formatting would only work on the first column data, but not the ones below.

    Therefore I tried manually formatting the leftover data however these returns as I refreshed the table. Would you mind helping me solve this if I send my file to you?
    Thanks so much (:

  21. Kurt

    This sounds like a foolish question as I ask it, but I keep getting a message telling me to place my cursor inside the pivot table. What exactly does placing the cursor inside the pivot table entail?

    I am fairly confident that I am doing just that.

  22. MAJORMAJOR

    Hi there,
    I’m a total VBA noob, unable to get this working.
    Here are the steps I’m following:
    Alt+F11
    Insert, Module, paste everything above starting with

    1. ending with

    Click green ‘run’ arrow
    Error message:compile error: invalid outside procedure

    If I remove the , I get: compile error: sub or function not defined

    Can anyone explain the fundamental mistake I’m making?

    Thanks

  23. Jennifer

    Hi, what a novel idea! I really like this code and I was pretty sure it would save my day!

    One issue though, I need the formatting in one column to include two different types of number formatting. Example:

    Title Comp
    Director
    Salary $150,000
    Bonus 20%

    As you see, the values in the Comp column can be either $ or %s (as set up in the original data source). Is there any way to make the pivot table reflect the different format types? Would it help if I sent the file to your email?

    Thanks for any help!

  24. Jeff Weir

    Hi again Mike. Have just linked to this from Chandoo’s blog, and note that your code still errors out if pivots are based on Excel 2010 tables.

    Just in case you missed my comment above, the reason is because the oPivotTable.SourceData address returns the table body range (e.g. Table4) and not the entire table including headers (i.e. Table4[#All])

    But you can always add a check for this right after the Set oSourceRange line , like so:

    On Error Resume Next
    bSourceIsTable = (oSourceRange.ListObject.Name “”)
    On Error GoTo 0
    If bSourceIsTable Then Set oSourceRange = oSourceRange.Offset(-1).Resize(oSourceRange.Columns.Count + 1)

    Only tested this in Excel 2010.

  25. Bill

    Hi

    This is a great bit of code and I have used it successfully for years but have now come across a problem.

    My source data has months as column headings (e.g. Jan-14) and it appears to stop this working. When I run the macro it appears to run but nothing changes, I don’t get any error messages.

    If I change the heading to January 14 as text it runs and formats the table correctly.

    I’m using Excel 2007

    Can you please tell me if there is anything I can do to get this working or will I just need to change my headings to text?

    Thanks

    Bill

  26. Jeff Weir

    Thinking about this bit:

    'Bonus: Change the name of field to Source Column Name
    oPivotFields.Caption = strLabel & " "
    End If

    …you probably should check if there is already such a name, because sometimes users have both a Sum and a Count based on the same source field, and you can’t try to rename them both to strLabel & ” “

  27. diablo2man

    Thanks a lot.

    in case anyone is adding this macro to personal.xlsb instead of regular worksheet, this code change would help:

    Set oPivotTable = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

    to

    Set oPivotTable = ActiveWorkbook.ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)

  28. Excel Geekazoid

    I’m also a VBA noob. Loving the idea of running this code but I’m having the same trouble as sergio. Were you able to find a solution yet?

  29. Excel Geekazoid

    UPDATE: I had two workbooks open. I closed the other one and now I’m not getting the bug.

    HOWEVER, I have some custom formatting in the source data that is not translating to the pivot. : (

    Thanks so much for your help!!!!!!

  30. Mike

    How do I carry over front color? Ex: I have some data in worksheet I use to differentiate collect vs. delivered shipping (use red font for delivered). I would like the red font to carry over into the Pivot table

Leave a Reply

Your email address will not be published. Required fields are marked *