Any self-respecting coach always has a supply of “wow-chips” – simple but effective tricks, a kind of killing-features to quickly charm a complex audience. In pivot tables, one of these tricks is, of course, double-clicking the left mouse button on any number in the value area:
If this is done, then you will be taken out to a new sheet, where Excel will unload the details for this cell – all the “underground”, explaining how this value turned out, what it was made of:
Officially, this procedure is called drill-down, informally it is usually called “fall through”.
The key nuance is that the table obtained as a result of such a failure by double-clicking is a copy of the original data, not themselves. The resulting table is absolutely autonomous and is in no way connected with either the source data or the summary. Sometimes it is in our favor – we can use it for our own purposes, change it, etc.
But sometimes another desire arises: is it possible to see not a copy of the original data, but the data itself? That is, to filter those same rows in the source table that are involved in the calculation of this cell? They, for example, could then be changed, thereby correcting the result in the pivot table.
This is impossible with standard tools, but for macros, the limits of what is possible in Excel are much wider 🙂
Open the Visual Basic Editor:
- In Excel 2003 and older, you need to select from the menu Service – Macro – Visual Basic Editor (Tools — Macro — Visual Basic Editor)
- In new versions of Excel 2007-2013, go to the tab developer (Developer) and press the button Visual Basic. If you do not see such a tab, then enable it in the settings File – Options – Ribbon Setup (File — Options — Customize Ribbon)
In the editor window, insert a new module through the menu Insert – Module and copy the text of these two macros there:
Sub FilterPivot() Dim pt As PivotTable Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Set pt = ActiveCell.PivotTable Set rSource = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)) rSource.EntireRow.Hidden = False nCols = rSource.Columns.Count Selection.ShowDetail = True Set rDrill = ActiveSheet.UsedRange Set shDrill = ActiveSheet DrillLastRow = shDrill.Range("A1").End(xlDown).Row For i = nCols To 1 Step -1 formulatxt = formulatxt & "RC[-" & i & "]&" Next i formulatxt = Left(formulatxt, Len(formulatxt) - 1) shDrill.Cells(2, nCols + 1).Resize(DrillLastRow - 1, 1).FormulaR1C1 = "=" & formulatxt For j = 2 To rSource.Rows.Count contxt = "" For i = 1 To nCols contxt = contxt & rSource.Cells(j, i).Value Next i If WorksheetFunction.CountIf(shDrill.Cells(2, nCols + 1).Resize(DrillLastRow - 1, 1), contxt) = 0 Then rSource.Cells(j, 1).EntireRow.Hidden = True End If Next j shDrill.Delete rSource.Parent.Activate Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Sub ShowAllData() ActiveSheet.Rows.Hidden = False End Sub Now, if we select any one cell with data in the pivot table and run our first FilterPivot macro using the keyboard shortcut Alt + F8 or through the menu Service – Macro – Macros (Tools — Macro — Macros), then we will go to the sheet with the source data for the summary, where filters will be automatically applied that select only those rows that were involved in the calculation of the current cell:
Now you can, for example, change them to achieve the desired result in the PivotTable report. Just do not forget to update the summary after making changes: right-click – Update & Save (Refresh).
The second macro, ShowAllData, is needed to return the original view of the original table – it makes all rows on the current sheet visible. For greater convenience, you can hang these two macros on keyboard shortcuts that are convenient for you, using the button Parameters (Options) in the window Macros, which is displayed on Alt + F8.
- What are pivot tables and how to build them
- Set up calculations in PivotTables
- What’s New in PivotTables in Microsoft Excel 2013