Pivot Chart Formatting Changes When Filtered

This is a guest post written by my colleague Jon Acampora.

The Problem

When you apply a filter or slicer to a PivotChart the custom formatting can change with each change in the filter.  This can be very frustrating and require you to apply the custom formatting to each possible view or slice of the data.

The screencast below shows this problem.  I customized the colors of the lines to green and grey for the Year 2011, but the line colors change back to the default colors when I filter the data for 2012.  This happens when the data is filtered by a slicer or manually filtered it in the PivotTable.

Pivot Chart Formatting Changes by Filter Slicer Excel

This post will explain why this happens and present an alternative solution that will save you time when creating PivotCharts with customized formatting.  VBA or macros will NOT be needed for this to work, so it should be easy for anyone to implement.

Why Does This Happen?

It’s all about the cache…

Any changes to the formatting of a data series in a PivotChart are stored in a cache inside the Excel file.  In the example below I made a few changes to the formatting of the lines in the chart from their default settings.  The colors of the lines and the shapes of the markers were changed when the PivotTable was filtered for the Year 2011.  As you can see in the screencast, when the PivotTable is filtered for the Year 2012 the line colors and markers change back to the default settings.

Excel Pivot Chart Source Range Unchanged on Filter

Even though the source range of the PivotChart remains the same, the formatting of the chart changes.  This is an odd behavior until you understand that the formatting properties are saved and remembered by Excel.

Excel actually stores this data in a cache with all the other chart properties.  This means that it remembers the exact formatting I applied for the Year 2011 only, and that is why the colors and formatting change back to my customization when the PivotTable is filtered for 2011.

To see this you have to dive under the hood of the Excel file.  I included the XML code for the PivotChart in the sample file (download below).  If you search through the code you can see a reference to “2011 – Coffee” and “2011 – Tea”.  Below each of those lines are rows of code that contain the custom formatting settings I applied.

Excel PivotChart Cache XML

Since there is no reference to 2012 or 2013 in the chart’s cache, the default formatting for the chart is applied.  I think it’s cool to be able to see the inner workings of the file and try to understand what is happening with the application.  You can see the components that make up any Excel 2007+ file by changing the file extension to “.zip”, then opening the folder.

Why does this matter?

This behavior is annoying for the most part.  If you click on a slicer or filter, you usually want to see the same custom formatting you applied for the other slices.

But understanding the behavior allows you to either use it to your advantage, or avoid it at design time.  I will explain a solution below that allows you to avoid the behavior, but still use the PivotTable filtering and slicers.

It should also be noted that this behavior does not always happen.  It depends on the setup of the PivotTable and the order of the row and column fields.  If you have a very simple pivot table that only has one field in the column area and one field in the row area, then you might not experience this change in formatting at all.  I am still trying to determine the exact parameters of the PivotTable that cause this to happen.  Please let us know if you have any insights on this.

Pros and Cons

The advantage of this unique formatting behavior is that it allows you to apply customized formatting for each slice of your data.  This could be useful if you want a series to stand out from the rest when the user is clicking through the slices.  You could use it as an attention grabber to highlight some important trends.

The disadvantage is that a lot of the time you want all slices to display the same custom formatting.  If you have a lot of slices then it becomes very tedious work to go through each slice and reapply the customized formatting.  And it will also require maintenance in future periods.  For example, when I get next year’s data for 2014 I will have to create custom formatting for that slice of the data.

Simple Workarounds

One way to prevent the formatting from changing is to create a regular chart that references the PivotTable data.  With either of the following solutions we want to avoid the PivotChart all together, and instead create a regular chart.  Again, the regular charts do NOT suffer from the formatting issues when filters are applied.

Solution #1

This can be accomplished by applying the technique in Jon’s article Making Regular Charts from PivotTables.  With that technique you basically create a regular chart and then carefully add the data series to the chart by referencing areas within the PivotTable.  You just have to be careful with the areas you select or reference because Excel has a tendency to want to convert the regular chart to a PivotChart.

Solution #2

The other option is to create a new area on the worksheet that contains a replica of the PivotTable.  This area just contains formulas that reference the PivotTable.  You can either use the direct cell references like (=C9) or the GETPIVOTDATA() function to point to the PivotTable.  You might want to use the GETPIVOTDATA function if you only want to display a subset of your PivotTable data in the chart.

The advantage of this technique is that once you have the formula based PivotTable setup, you can reference the entire area of the source data range to quickly create a chart that includes all your series.  It might save you time over having to add the series individually as you do in solution #1.

The sample file (download below) contains an example of the technique that I will explain here.

The first step is to recreate the PivotTable data by creating formulas that reference the pivot.  This can be done in cell adjacent to your PivotTable or on a separate worksheet.  Just remember to leave enough blank rows/columns between your PivotTable and formula based table in case your PivotTable expands when filters are applied/removed.

Excel PivotChart Formulas Linked to PivotTable

Step two is to create a regular chart using the new formula driven table as the source of the chart.  When the PivotTable is filtered or sliced, the formulas will automatically be updated and display the new numbers from the PivotTable.  The chart will also be updated and display the new data.

Excel Create Regular Chart to PivotTable Linked Formulas

That’s it!  You should now have a chart that retains it’s formatting when a slicer or filter is applied to a PivotTable.  An example of this behavior can be seen in the screencast below.

PivotChart vs Regular Chart Formatting Changes Slicer Excel

This solution works well for simple cases where the size of the data area stays the same every time the chart is updated.  If the number of rows/columns in your PivotTable is increasing/decreasing every time you update the data, then this solution might take more maintenance or tweaking to work for you.  But the general principle will help get you started and thinking about more advanced solutions if needed.

Jon has a great article on using VBA to create dynamic charts that explains a more advance approach to this issue.

Conclusion

Adding slicers to your PivotCharts and PivotTables is a great way to make your presentation interactive.  PivotCharts allow you to link your chart to a data source so they can be refreshed dynamically with little maintenance.   However, PivotCharts display some odd behavior when filtering charts with custom formatting.  Understanding this behavior and planning for it at design time will save you time and frustration.

The example file can be downloaded below.  Please leave a comment with any questions or suggestions.  Thanks!

Download

Download PivotChart Formatting Changes On Filter Slicer.xlsx PivotChart Formatting Changes On Filter Slicer.xlsx (34.7 KiB)

About the Author

Jon AcamporaJon Acampora writes the blog at ExcelCampus.com where he is focused on helping users improve their Excel and VBA skills.  His contributions include in-depth articles, video tutorials, and free add-ins to help save time and improve productivity. Jon is a newly-minted Microsoft Excel Most Valuable Professional (MVP).

 

Peltier Tech Chart Utility

Clean Up Date Items in An Excel Pivot Table

In Grouping by Date in a Pivot Table I showed how to summarize daily data in a pivot table by grouping into monthly values. I’ll review this technique, then show how to clean up the dates when you don’t use the default starting and ending dates in the Grouping dialog.

Here is a pivot table with daily values. I worked this out in Excel 2003, because that’s what was open at the time, but the technique is much the same in all versions. I want to condense this into monthly values, and show only data from 2010 and 2011.

Pivot Table of Daily Values

I navigate to the Group and Show Detail > Group command, and choose the appropriate parameters in the Grouping dialog.

Pivot Field Grouping Dialog

I change the range of dates in the pivot table by unchecking the two Auto boxes at the top and entering the start and finish dates I want. Then I select the time units I want the data grouped by. Pick Months and Years, or you’ll only get 12 monthly values summed over all the years within selected the date range.

The pivot table is condensed into the desired time periods. The only problem is that Excel includes two additional pivot items in each date-related pivot field. The pivot fields Years and Date have pivot items <1/1/10 and >12/31/11 to account for data from outside our selected date range.

Pivot Table grouped by Months and Years

We can click on the field header dropdowns and see the pivot items in each. The Years and Date pivot item lists are shown below.

Pivot Item Lists for Years and Date Fields

The pivot items have checkboxes, so we can manually uncheck the extraneous items, every time the pivot table is refreshed. Bo-o-o-oring.

I’ve actually been doing this manually for years in some of my accounting workbooks. Like the plumber’s faucet that always drips, the programmer’s workbook gets updated by hand. But a reader emailed me and asked how to get rid of those extra date items. I thought for about 30 seconds, and coded for about 3 minutes, and came up with this routine that cleans up any fields in the Row and Column areas of all pivot tables on the active sheet that have items beginning with “<” or “>”.

Sub Remove_GT_LT_PivotItems()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim pi As PivotItem

  For Each pt In ActiveSheet.PivotTables

    For Each pf In pt.RowFields
      For Each pi In pf.PivotItems
        If Left$(pi.Caption, 1) = "<" Or Left$(pi.Caption, 1) = ">" Then
          pi.Visible = False
        End If
      Next
    Next

    For Each pf In pt.ColumnFields
      For Each pi In pf.PivotItems
        If Left$(pi.Caption, 1) = "<" Or Left$(pi.Caption, 1) = ">" Then
          pi.Visible = False
        End If
      Next
    Next

  Next

End Sub

Here is the finished pivot table.

Pivot Table with Cleaned Up Date Fields

 

Peltier Tech Chart Utility

Pivot Table Conditional Formatting with VBA

A reader encountered problems applying conditional formatting to a pivot table. I tried it myself, using the same kind of formulas I would have applied in a regular worksheet range, and had no problem. The reader responded that he was having problems in Excel 2007, and I was using 2003. Apparently in 2003 the conditional formatting is preserved when the table is refreshed, but in 2007, the CF in the data range is wiped out.

Well, this is just the type of thing Bill gates invented Visual Basic for. I’ll apply an approach related to that in my VBA Conditional Chart Formatting series: VBA Conditional Formatting of Charts by Series Name, VBA Conditional Formatting of Charts by Category Label, and VBA Conditional Formatting of Charts by Value, with a little help from Referencing Pivot Table Ranges in VBA. I’ll use arbitrary data and an arbitrary condition for the example.

Here is a data source for a simple pivot table. The entire range of values in column D consists of random integers between 1 and 9, and there are three categories by which they can be sorted.

Pivot Table Data Source

The first pivot table, PivotTable1, has the category fields all placed in the rows area. I manually typed the name “Pivot Table 1” in the cell above the pivot table.

Pivot Table 1 Unformatted

Tip: To name a pivot table, right click on the table, choose Table Options, and edit the name in the first line of the dialog.

The second pivot table, PivotTable2, has one category in the columns area, so the values are separated into two columns.

Pivot Table 2 Unformatted

I used the following VBA procedure to apply bold text and yellow fill formatting to rows in PivotTable1 if the value in the Total column is 7 or greater. We are testing the entire DataBodyRange, which is one column wide.

Sub FormatPT1()
  Dim c As Range
  With ActiveSheet.PivotTables("PivotTable1")
    
    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With
    
    ' apply formatting to each row if condition is met
    For Each c In .DataBodyRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next
    
  End With
End Sub

I used the next procedure to apply the same formatting to PivotTable2 rows if the value in column a under Category 3 is 7 or greater. The DataBodyRange is two columns wide, for pivot items “a” and “b”. To test against pivot item “a”, we test the pivot item’s DataRange.

Sub FormatPT2()
  Dim c As Range
  With ActiveSheet.PivotTables("Pivottable2")
    
    ' reset default formatting
    With .TableRange1
      .Font.Bold = False
      .Interior.ColorIndex = 0
    End With
    
    ' apply formatting to each row if condition is met
    For Each c In .PivotFields("Category 3").PivotItems("a").DataRange.Cells
      If c.Value >= 7 Then
        With .TableRange1.Rows(c.Row - .TableRange1.Row + 1)
          .Font.Bold = True
          .Interior.ColorIndex = 6
        End With
      End If
    Next
    
  End With
End Sub

The two procedures above belong in a regular code module.

Here is PivotTable1 with the conditional formatting applied.

Pivot Table 1 Formatted

Here is PivotTable2 with the same formatting applied.

Pivot Table 2 Formatted

Note that refreshing the pivot tables changes values but does not automatically reformat the tables. You have to manually rerun the VBA routines, or capture the PivotTableUpdate event:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
  Select Case Target.Name
    Case "PivotTable1"
      FormatPT1
    Case "PivotTable2"
      FormatPT2
  End Select
End Sub

This procedure goes in the code module for the worksheet that contains the pivot tables. To access this module easily, right click on the sheet tab, and choose View Code. If you use this approach, you can put the first two procedures into the worksheet’s code module, instead of a regular module.

 

Peltier Tech Chart Utility

Preliminary Data Exploration with Excel Pivot Tables

In Preliminary Data Exploration in Excel I showed how to start the process of analyzing a table of data. The data I started with was a flat table, one row per observation, but it wasn’t normalized. That is, each input parameter had its own field. A more flexible arrangement is to replace the several fields for each factor with two fields: one for factor name and one for factor value. This means each observation needs as many records (rows) as it has factors.

This is what the top section of the data looks like when normalized in this way. I’ve converted the range to a list for all the usual reasons.

list of data
Marginal Means Plots

Instead of constructing a table of formulas to analyze the data, I can instead create a simple pivot table. The data area contains Average of Result, while Factor and Level are stacked in the rows area, with Level inside Factor. The empty rows come from checking the Empty Rows Between Items for the Factor field.

simple pivot table

I could make a pivot chart, but I dislike pivot charts. The plot area cannot be resized, and the pivot fields and buttons clutter them up.

simple pivot chart

You can hide the fields and buttons, but you still have limited formatting capability. What’s worse, the blank spaces in the data range are ignored by the pivot chart, so points for one factor are connected to points from the next factor.

cleaned up pivot chart

Instead I’ll make a regular chart.

simple regular chart

This is almost like the first chart from the previous article, except that the factor names aren’t centered under the factor values. This is fixed by changing the layout a bit, by showing data in the Factor field in outline form, not table form, and retaining the space after each item.

enhanced pivot table

Now the labels are centered. The chart based on a grid of formulas has less space between groups of labels, but we’ll accept this in exchange for how easy pivot tables make our work.

cleaned up chart

The second set of formulas in the previous example allowed for separate series for each factor. To get this with a pivot table, we need a duplicate Factor field. When added to the list containing the data, Excel renames it Factor2.

enhanced data table

Now we place Factor2 in the columns area, and this splits the table into six columns, one for each factor.

multiple column pivot table

These six columns become six separately formatted series in the chart.

multiple series chart

Interaction Plots

In order to work interactions into the pivot table, you need to reintroduce the original columns for the factors and their values. Since we’ve already changed the table so that one observation requires six rows, we need to add six times the original data. If someone knows more about pivots than I do. please explain to me how to do this without using scads of data.

massive data table

Anyway, here is a pivot table, one of the six needed to display all of the interactions. You can only neatly show one separate factor in the columns area. It’ easy enough to change the pivot table, of course: drag one factor off the table and drag another on.

first of six interaction pivot tables

Here’s pivot table two of six. Pivot tables three through six are not shown here.

second of six interaction pivot tables

An interaction plot can be built from each of these pivot tables. These are essentially the same as those in the earlier analysis, and the same conclusions can be drawn from them.

interaction chart for mixer
interaction chart for amount
interaction chart for mutt
interaction chart for pctg
interaction chart for form
interaction chart for add

Even though some data preparation was required, and for some of the analysis a lot of data had to be duplicated, but creating an analysis using pivot tables can be easier, faster, and much more interactive than one using formulas.

The Data

If anyone would like to use this data and try to add to this preliminary analysis, the data is available in the following two CSV files: Table1.csv and Table2.csv.

For more about pivot tables, check out these pages on my web site:

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.