Loading...

Follow Excel Campus Blog on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


Bottom line: Learn how to change the date formatting for a grouped field in a pivot table.

Skill level: Intermediate

Changing the Days Field Number Formatting Doesn’t Work

When we group a Date field in a pivot table using the Group feature, the number formatting for the Day field is fixed.  It has the following format “Day-Month” or “d-mmm”.

If we try to change the number format of the Day/Date field it does not work.  Nothing changes when we go to Field Settings > Number Format, and change the number format to a custom or date format.

Why?

The number formatting does not work because the pivot item is actually text, NOT a date.

When we group the fields, the group feature creates a Days item for each day of a single year.  It keeps the month name in the Day field names, and this is actually a grouping of day numbers (1-31) for each month.

We can actually see this list of text items in the pivotCacheDefinition.xml file.  To see that you can change the file extension of the Excel file to .zip, and navigate to the PivotCache folder.

Since these are text items that represent the days of the year, we won’t be able to change the number formatting of the cells directly in Excel.  However, there are a few workarounds.

Solution #1 – Don’t Use Date Groups

The first solution is to create fields (columns) in the source data range with the various groups for Year, Quarter, Month, Days, etc.  I explain this in detail in my article on Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data.

Using your own fields from the source data for the different date groups will give you control over the number formatting of the field in the pivot table.

You can also create a Calendar Table with the groupings if you are using Power Pivot.

Automatic Date Field Grouping

If you are using Excel 2016 (Office 365) then the date field is automatically grouped when you add it to the pivot table.

To Ungroup the date field:

  1. Select a cell inside the pivot table in one of the date fields.
  2. Press the Ungroup button on the Analyze tab of the ribbon.

The automatic grouping is a default setting that can be changed.  See my article on  Grouping Dates in a Pivot Table VERSUS Grouping Dates in the Source Data to learn more.

Once the date field is Ungrouped you can change the number formatting of the field.

To change the number formatting on the ungrouped Date field:

  1. Right-click a cell in the date field of the pivot table.
  2. Choose Field Settings…
  3. Click the Number Format button.
  4. Change the Date formatting in the Format Cells window.
  5. Press OK and OK.

Again, this only works on fields that are NOT grouped.  If you group the field again after changing the formatting, the formatting for the items in the Days field will change back to “1-Jan”.

Solution #2 – Change the Pivot Item Names with VBA

If you really want to use the Group Field feature, then we can use a macro to change the pivot item names.  This will make it look like the date formatting has changed, but we are actually changing the text in each pivot item name.

The following macro will loop through all the pivot items of the grouped Days field, and change the number formatting to a custom format. By default I set it to “m/d”, but you can change this to any date format for the month and day. Just remember that the item is NOT going to contain the year, since the item is not an actual date.

Download the File

Download the Excel file that contains the macro.

Pivot Table Date Field Group Number Formatting Macro.xlsm (54.2 KB)

The Change Days Field Formatting Macro
Sub Change_Days_Field_Formatting()
'Change the number formatting of the Days field
'for Grouped pivot table date field.
'Source: https://www.excelcampus.com/pivot-tables/grouped-date-field-formatting/

Dim pt As PivotTable
Dim pi As PivotItem


'IMPORTANT: Change the following to the name of the
'grouped Days field.  This is usually Days or the name
'of your date field.

Const sDaysField As String = "Days"

  'Set reference to the first pivot table on the sheet
  'This can be changed to reference a pivot table name
  'Set pt = ActiveSheet.PivotTables("PivotTable1")
  
  Set pt = ActiveSheet.PivotTables(1)

  'Set the names back to their default source name
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    'Bypasses the first and last items "<1/1/2015"...
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      pi.Name = pi.SourceName
    End If
  Next pi

  'Set the names to a custom number format
  For Each pi In pt.PivotFields(sDaysField).PivotItems
    If Left(pi.Name, 1) <> "<" And Left(pi.Name, 1) <> ">" Then
      'Change the "m/d" format below to a custom number format.
      'Year 2020 is used for leap year.
      pi.Name = Format(DateValue(pi.SourceName & "-2020"), "m/d")
    End If
  Next pi

End Sub
How the Macro Works

The macro first loops the pivot items in the Days field to restore the pivot item name to it’s default source name.  Two different pivot items cannot have the same name.  So this should prevent any errors when changing or trying different formats.

The second loop changes each pivot item to the new format.  It uses the DateValue function to change the pivot item name “1-Jan” to a date.  It then uses the Format function to change the formatting of the date to text.  By default it uses the “m/d” format.  This can be changed to another format with the month and day.  Each item must be unique, so you will want to use both the month and day in the item name.

You could probably separate this macro into two macros, and only run the first reset loop as needed.  The macro takes about 15 seconds to run on my computer because of all the looping.  But it’s not one you will have to run often.

It’s also important to mention that this is running on the pivot items, not the pivot cache.  So the newly named items will only be changed on the pivot table you run the macro on.

Final Verdict

I hope you find that helpful.  I would suggest going with Solution #1 unless you really want to use the Group feature.

Please leave a comment below with any questions or suggestions on how we can improve this.  Thanks so much! 🙂

The post How to Change Date Formatting for Grouped Pivot Table Fields appeared first on Excel Campus.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Bottom line: Learn how to add buttons to your pivot tables and pivot charts to quickly expand/collapse entire fields using a VBA macro.

Skill level: Intermediate

Expanding & Collapsing Fields is Time Consuming

Expanding and collapsing entire fields in a pivot table can be a multi-step process that is a bit time consuming.  This is especially true for the users/consumers of your Excel report files that are not familiar with the process.

To expand or collapse the ENTIRE field, we tend to press the little expand (+) and collapse (-) field buttons for each item in the field.

There are shortcuts for this on the right-click menu and the Options/Analyze tab in the ribbon.

Keyboard shortcuts:

Expand Entire Field: Alt, A, J Or Menu Key, E, E

Collapse Entire Field: Alt, A, H Or  Menu Key, E, C

Note: A cell in the Rows or Columns area must be selected for the keyboard shortcuts to work.

However, there are no dedicated buttons on the pivot table to expand/collapse the entire field.

New Expand Collapse Buttons in Excel 2016 Pivot Charts

In Excel 2016 a new feature was added to pivot charts that allows us to expand or collapse the fields in the Rows area.  You will see little plus and minus button in the bottom right corner of your pivot chart.

However, this feature is ONLY available in Excel 2016 (Office 365).  So, I created a few macros that will work on any version of Excel, in both the Rows and Columns area of a pivot table or pivot chart.

Expand Collapse Entire Pivot Fields Macros

This set of macros will expand or collapse the NEXT field in the rows or columns area.  This allows us to drill down to show the details of the fields in the Rows or Columns Area.

A good example is when the pivot table has fields in the rows area for Year, Quarter, Month, Day.  We might want to compare year totals, then drill down to see totals by quarter or month.

If the pivot table is currently collapsed to years, the “Expand_Entire_RowField” macro will expand ALL of the Year items to display the Quarters for each year.

Running the “Collapse_Entire_RowField” macro will collapse the Year items back.

Assign the Macros to Shapes or Buttons

We can assign these macros to shapes or buttons on the sheet.

The buttons can be continually pressed to drill up or down through the fields.  This makes it easy for users of your reports to quickly expand or collapse entire fields.

The shapes can be pasted inside a pivot chart to mimic the functionality of the expand/collapse buttons in Excel 2016.

The VBA Macro Code

There are four different macros for the expand/collapse on the Rows and Columns areas.  The macros use For Next Loops to do some pretty aggressive looping through the pivot fields and items.

The Expand macro finds the field in the highest position that is collapsed by checking the ShowDetail property of each PivotItem.  If it does NOT find an expanded item, then it expands the entire field.  If all items are expanded, then it proceeds to the field in the next position down.

Sub Expand_Entire_RowField()
'Expand the lowest position field in the Rows area
'that is currently expanded (showing details)

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long

  'Create reference to 1st pivot table on sheet
  'Can be changed to reference a specific sheet or pivot table.
  Set pt = ActiveSheet.PivotTables(1)

  'Count fields in Rows area minus 1 (last field can't be expanded)
  iFieldCount = pt.RowFields.Count - 1
  
  'Loop by position of field
  For iPosition = 1 To iFieldCount
    'Loop fields in Rows area
    For Each pf In pt.RowFields
      'If position matches first loop variable then
      If pf.Position = iPosition Then
        'Loop each pivot item
        For Each pi In pf.PivotItems
          'If pivot item is collapsed then
          If pi.ShowDetail = False Then
            'Expand entire field
            pf.ShowDetail = True
            'Exit the macro
            Exit Sub
          End If
        Next pi
      End If
    Next pf
  'If the Exit Sub line is not hit then the
  'loop will continue to the next field position
  Next iPosition
  
End Sub

The Collapse macro does the opposite. It starts at the lowest field position and works backwards until it finds a pivot item that is NOT collapsed. If it finds an expanded item then it collapses the entire field. Otherwise it moves up to the field in the next position in the Rows area, and repeats the process.

Sub Collapse_Entire_RowField()
'Collapse the lowest position field in the Rows area
'that is currently expanded (showing details)

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iFieldCount As Long
Dim iPosition As Long

  'Create reference to 1st pivot table on sheet
  'Can be changed to reference a specific sheet or pivot table.
  Set pt = ActiveSheet.PivotTables(1)

  'Count fields in Rows area minus 1 (last field can't be expanded)
  iFieldCount = pt.RowFields.Count - 1
  
  'Loop backwards by position of field (step -1)
  For iPosition = iFieldCount To 1 Step -1
    'Loop fields in Rows area
    For Each pf In pt.RowFields
      'If position matches first loop variable then
      If pf.Position = iPosition Then
        'Loop each pivot item
        For Each pi In pf.PivotItems
          'If pivot item is expanded then
          If pi.ShowDetail = True Then
            'Collapse entire field
            pf.ShowDetail = False
            'Exit the macro
            Exit Sub
          End If
        Next pi
      End If
    Next pf
  'If the Exit Sub line is not hit then the
  'loop will continue to the next field position
  Next iPosition

End Sub
Download the File

You can download the file that contains the macros for the Rows and Columns areas.

Expand Collapse Entire Fields Macro.xlsm (68.3 KB)

How Can We Improve It?

The Expand/Collapse Macro buttons should make a nice addition to any report file you send to users that aren’t Excel ninjas.   I’ve seen many users click the small expand/collapse buttons repeatedly for each pivot item, simply because they didn’t know there was a better way.  This macro should help give them a faster and easier way to drill down through your pivot tables or charts.

Please leave a comment below with questions or suggestions on how we can improve this.  Thanks so much! 🙂

The post Expand and Collapse Entire Pivot Table Fields – VBA Macro appeared first on Excel Campus.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview