Get rid of the GETPIVOTDATA without disabling it

If you do Pivot Table, I believe you should have experienced the following too.  Excel Tips - GetPivotData 0

GETPIVOTDATA is good.  But sometimes we just want a simple cell reference.  We may disable the GETPIVOTDATA easily by going to Pivot Table Option –> Uncheck the “Generate GetPivotData”:

Excel Tips - GetPivotData 1

But as I said, GETPIVOTDATA is good.  I want to keep it on.  So here is a simple trick I would do.

Instead of selecting a single cell that you want to refer to, select just one more cell.  See the screenshot below:Excel Tips - GetPivotData 2

The GETPIVOTDATA formula will change to a simple range of two cells immediately.  What you need is just a few keystrokes of “Backspace”.

Try it out.Excel Tips - GetPivotData Anminated

Note: If you have already released your mouse click, press Shift+Down Arrow, that has the same effect.

Wait… would it be easier to type =B17 directly? Yes, absolutely… provided that you did not point to the cell and generated the GETPIVOTDATA in the first place.  🙂


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

2 Responses to Get rid of the GETPIVOTDATA without disabling it

  1. Igor says:

    Thanks. Good tip.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.