Non-VBA way to show Excel PivotTable Data Source as cell value

Question

Is there a non-VBA way to show the PivotTable Data Source in a cell in Excel?

I know we can manually view the Data source by clicking on

PivotTable Tools Analyze > Change Data Source > Change Data source to display source

but this has to be done for each pivot table.

I have inherited lots of workbooks with lots of pivot tables across lots of tabs, referencing lots of tabs. I know some are referencing the wrong source data - e.g. a table labelled as 2023 is referencing 2022 data. Rather than clicking in each pivot table to see what its source data is, can I do something to show the source data next to the table, so I can just scan down and spot ones looking at the wrong place?

I am hoping there's something like:

=GETPIVOTDATA("Source",{cell ref somewhere in pivot table})

so I can leave the cell ref as dynamic and paste it in next to each pivot table.

(https://i.sstatic.net/TMYPrD9J.png)

I tried searching Excel functions and couldn't come up with anything.

I tried googling the answer and have only found one option, and it uses VBA:

display Excel PivotTable Data Source as cellvalue

I can't use it as I'm not familiar enough with VBA to understand how to apply that to my spreadsheet. It also mentions that you need to reference the pivot table number - the ones in my spreadsheet weren't created in any "nice" order, so I'd still have to click in them all to see what their number was, defeating the point of avoiding clicking in each one and checking Change Data Source.

Thanks for any ideas.

There is no built-in formula for that. Amending that VBA function so that you can simply refer to a cell in the pivot table (as with GETPIVOTDATA) would be easy enough though. — Rory
– Rory, Commented Jan 22 at 12:07

Rory · Accepted Answer · 2025-01-22 12:12:58Z

3

Example per my comment of how to use a cell reference rather than a pivot table name or index:

Function GetPivotTableSource(pcell As Range) As String
    Dim a1Source As String
    Dim bracket As Long
    Dim pt As PivotTable
    On Error Resume Next
    Set pt = pcell.Cells(1).PivotTable
    On Error GoTo 0
    If pt Is Nothing Then
        GetPivotTableSource = "Not a pivot table cell"
    Else
        a1Source = Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1)
        bracket = InStr(1, a1Source, "]")
        GetPivotTableSource = "=" & Mid(a1Source, bracket + 1)
    End If
End Function

answered Jan 22 at 12:12

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Louise Jan 23 at 5:50

Thanks for the reply. So do I just copy the above exactly as-is into the VB editor? And then once it's in I can put the formula =PivotTableSource(cell ref somewhere in pivot table) anywhere in the workbook and it'll show the data range of that table?

Rory Jan 23 at 10:57

Yes - it needs to go into a normal module, not a worksheet one. Then you use =GetPivotTableSource(A3) for example.

Louise Jan 27 at 12:51

Sorry for the delay, just got time to try it today. It's brilliant, thank you! I had to google how to add a "normal" module, but other than that just bunged in the code and the formula works a treat. For any luddites like me reading (and I'm not saying this is the most efficient way), I went Developer tab > Visual Basic > Insert > Module > enter above code into the window that pops up & Save And if the Developer tab isn't showing, turn it on here: File > Options > Customize Ribbon > under "Customize the Ribbon" select the Main Tabs dropdown and check Developer

Collectives™ on Stack Overflow

Non-VBA way to show Excel PivotTable Data Source as cell value

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related