Jumping to a PivotTable’s Source Data

I use Ctrl+[ all the time to go to the precedents of the ActiveCell. The SourceData for a PivotTable isn’t a precedent, but wouldn’t it be nice if there was a shortcut to go there? Yes, it would.

Until now, if I wanted to see the SourceData for a PivotTable, I would use Alt, J, T, I, D to activate the Ribbon control for changing the source data (PivotTable Tools – Options – Change Data Source – Change Data Source). That would open the Change PivotTable Data Source dialog. The focus on that dialog is a RefEdit control so Excel navigates to the data. I don’t actually want to change it, though, so I hit ESC and it takes me back to the PivotTable. But I make a note of where it is and I use Ctrl+G or simply navigate to where it is.

I decide to repurpose Ctrl+[ to do the deed.

First, I recorded a macro to see what happens when I press Ctrl+[. That became the first line after the error handler. Rather than doing a bunch of checking to see whether the ActiveCell was in a PivotTable, whether the PivotTable source was a Range, and a bunch of other stuff that I can’t even predict, I just attempt to Goto the SourceData. If it works, then it was possible. If there’s some reason I can’t, it just fails silently.

3 thoughts on “Jumping to a PivotTable’s Source Data

  1. Just note this doesn’t work if your pivot table is coming from Excel’s data model. I suppose it would fail silently in that case.

  2. As edh commented, the macro does not work for Pivot tables coming from Excel’s data model. However, the data source is still listed under the “Change Data Source” dialog box. Is there a macro that could pull the sheet info from the change data source dialog box and goto it?

Leave a Reply

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